Showing 2 series on the same chart
 

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
--
Mike News

Showing 2 series on the same chart
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mike" S wrote in message
...
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
--
Mike News

Showing 2 series on the same chart
 

In message
at 21:13:51 on Thu, 30 Aug 2007, Jon Peltier
wrote
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
--
Mike News