Chart with offset log scale

Question

I've been asked to use Excel to create a chart looking something like this

It is a chart of cumulative returns on different investments. The value axis has a log scale based on the index value of the investment, but the tickmark numbers have been offset with the initial value set to zero, so that the cumulative percentage return is displayed. The chart has to allow for the possibility that returns might be negative occasionally as is shown in the example above.

I have no problem creating something along these lines using a linear vertical axis, but cannot see how I can get an offset log scale of this type. Apparently the original chart was created using something called Gnuplot. Is there any way of doing the same in Excel?

Answer 1

First of all sorry for the delay in response.

I was unable to login to this Microsoft Community site for last 2-3 days.

Now to respond to your query,

If what I have understood is exactly the same what you are looking for,

one of the workaround could be:

1. Add a constant (say for example 5 - you can take any other small value of your choice so that all your negative values become positive) to each value of 'Fund 1' as well as to each value of 'Fund 2'

2.  Plot your line chart using these new values

3. Remove the Y-Axis (or make it white coloured - so that it is not visible)
4. Add data values to your line chart - and refer to your original values for these.

Hope this Helps.

Answer 2

here's the original image again

Answer 3

I am using IE 11.0.9 and I am unable to see any image in this question.

I cross-checked other questions from the same forum - there I am able to see the images very clearly.

Can you give a one more try of sharing yr image pls ?

Secondly, can you also try to draw yr desired chart and then share a image of that too.

This will help us to exactly understand your requirement.

Answer 4

Where is the image ?

Pls also share your sample data (for creating the chart).

This image is displayed perfectly well on the page on my browser, below the first paragraph. Here is some example data you can use to create a chart.

	Fund 1	Fund 2
Nov-03	0	0
Dec-03	-0.02	0.01
Jan-04	-0.02	0.02
Feb-04	-0.01	0.03
Mar-04	0	0.04
Apr-04	0.005	0.05
May-04	0.012	0.06
Jun-04	0.019	0.07
Jul-04	0.026	0.08
Aug-04	0.033	0.09
Sep-04	0.04	0.1
Oct-04	0.047	0.11
Nov-04	0.054	0.12
Dec-04	0.061	0.13
Jan-05	0.068	0.14
Feb-05	0.075	0.15
Mar-05	0.082	0.16
Apr-05	0.089	0.17
May-05	0.096	0.18
Jun-05	0.103	0.19
Jul-05	0.11	0.2
Aug-05	0.117	0.21
Sep-05	0.124	0.22
Oct-05	0.131	0.23
Nov-05	0.138	0.24
Dec-05	0.145	0.25
Jan-06	0.152	0.26

If you use that data to create a line chart, and then try to set the y-axis to a log scale, it complains (quite rightly) about negative numbers.

Answer 5

Where is the image ?

Pls also share your sample data (for creating the chart).