Showing 2 series on the same chart

[Mike]

I have data which looks a bit like this

ColA ColB ColC
Date Price Ave1
27/03/07 306.5
28/03/07 303
29/03/07 304
30/03/07 303.75
02/04/07 310
03/04/07 311.25
04/04/07 311.5
05/04/07 311
10/04/07 313.75
11/04/07 314
12/04/07 308.5
13/04/07 310.25
16/04/07 314
17/04/07 314.5
18/04/07 310.5
19/04/07 312.75
20/04/07 314.5
23/04/07 314
24/04/07 312.5
26/04/07 320 311.0125
27/04/07 314.5 311.4125
30/04/07 316.25 312.075
01/05/07 316.25 312.6875
02/05/07 317.25 313.3625


So the "average" is a moving average of the previous 20 values and wish
to plot these correctly using dynamic ranges. I found an example on the
net.

The sheet name is called "Basic Range". The following named ranges are
defined
XValues=
OFFSET('Basic Range'!YValues,0,-1)
YValues=
OFFSET('Basic Range'!$B$2,0,0,COUNTA('Basic Range'!$B:$B)-1,1)

And I have added my own
Ave1=
=OFFSET('Basic Range'!$C$2,0,0,COUNTA('Basic Range'!$C:$C)-1,1)

I understand what the YValues expression is doing, automatically
creating a data range, based on the number of populated cells

So Series1 has
='Basic Range'!XValues for the X Axis and
='Basic Range'!YValues for the Y Axis

and Series2 has
='Basic Range'!XValues for the X Axis and
='Basic Range'!Ave1 for the Y Axis

However I am struggling to work out how to make series 2 start in the
correct place on the X plane - as it just seems to appear in the middle
of the chart and not flush right as it should be. As a workaround I
could fill the empty cells with 0's but then you see the line shooting
up from 0 to .311.01 which I don't really want


I have uploaded the spreadsheet here if you'd like to see it
http://preview.tinyurl.com/2wzq5r
The original version was created in Excel 2003, but I have saved it into
2000 format, as this is irrelevant.

The above code has no VBA, but I don't mind if the solution does require
VBA as it's part of a bigger project that does use VBA

Many thanks for those that take the time to look

 

[Jon Peltier]

Make your life easier. The dynamic range for YValues is good, so is that for
XValues, derived smoothly from YValues. Use the same to give you a
reasonable Ave1, which has to start on the same point as YValues in order to
plot correctly on the line chart:

Ave1=
OFFSET('Basic Range'!YValues,0,1)

Now plot both YValues and Ave1 using XValues for X.

- Jon

 

[Mike]

In message <[email protected]>
at 21:13:51 on Thu, 30 Aug 2007, Jon Peltier

Make your life easier. The dynamic range for YValues is good, so is that for
XValues, derived smoothly from YValues. Use the same to give you a
reasonable Ave1, which has to start on the same point as YValues in order to
plot correctly on the line chart:

Ave1=
OFFSET('Basic Range'!YValues,0,1)

Now plot both YValues and Ave1 using XValues for X.

- Jon

Wow that is superb and very clever

Many thanks for your help