Newbie Custom Chart Question

[investinthebest]

I'm guessing this is somewhat easy, but I can't seem to figure it out so I was hoping someone on these boards could help. I'm trying to create a custom chart in Excel that compares percentage gains/losses to a predetermined value. I have included some data below. Basically, I want a line graph with a percentage gain/loss on the y-axis and dates on the x-axis. In the center of the y-axis I would like a basis line going straight across (which would basically represent 0%) and would be the first data point from the data below (12.51). Then as the number goes up and/or down - the next point would show the percentage difference - in this case, the basis is 12.51 - and the next day it is 12.34 - then the point on the chart would be at -1.36%; the next point would be 13.16, so it would be (13.16 - 12.51)/12.51 = 5.20%; etc. I would like the chart to go up to 60% above and -60% below the basis line. Any help for this newbie would be greatly appreciated. And can this chart be saved, so if I have a bunch of different tables to chart I can just click a button instead of having to customize each time?

18-Jan-01 12.51
19-Jan-01 12.34
22-Jan-01 13.16
23-Jan-01 13.69
24-Jan-01 13.49
25-Jan-01 13.01
26-Jan-01 12.99
29-Jan-01 13.2
30-Jan-01 13.57
31-Jan-01 13.13
1-Feb-01 13.37
2-Feb-01 13.18
5-Feb-01 13.22
6-Feb-01 13.47
7-Feb-01 13.42
8-Feb-01 13.91
9-Feb-01 14.07
12-Feb-01 14.26
13-Feb-01 14.25
14-Feb-01 14.26
15-Feb-01 14.12
16-Feb-01 13.77
20-Feb-01 13.44
21-Feb-01 13.22

 

[Andrew Poulsom]

Insert a column between your 2 columns of data. Assuming your original data was in A1:B24, in (the new) B1 enter:

=(C1-C$1)/C$1

and format as Percent with 2 decimal places. Copy that formula down as far as needed. Then plot the new A1:B24 as an xy Scatter chart.

 

[investinthebest]

Andrew - thanks a million - it worked. I might have more detailed questions later, but this should definitely get me started. I really appreciate it.