Dynamic Ranges and XY Charts

[QGroup]

Folks, I'm having trouble with using dynamic ranges and plotting an XY chart or scatterplot. Excel 2003.

Dynamic Ranges as follows:

Primary = =OFFSET(AllData!$C$1,0,0,COUNTA(AllData!$C:$C),1)
Duplicate = =OFFSET(AllData!$D$1,0,0,COUNTA(AllData!$D:$D),1)

Chart Series as follows:

=SERIES("Dup.vs.Prim",'Duplicate Analysis.xls'!Primary,'Duplicate Analysis.xls'!Duplicate,1)

Chart Type is set to XY (Scatter)

Instead of getting all the data plotted in column C plotted against column D I get something that looks more like a line graph. When I check the chart data by selecting a datapoint it looks like there is an extra value in the series and the data is being plotted against that value instead of against each other. The extra value is essentially a record number and that's why it is looking like a line graph rather than an XY chart.

Anyone have any thoughts?

 

[QGroup]

Ha!

Got it I think.

Looks like my offset formula in the dynamic range was not working correctly. I tried this instead

=OFFSET(AllData!$C$1,1,0,COUNTA(AllData!$C:$C),1) and

=OFFSET(AllData!$D$1,1,0,COUNTA(AllData!$D:$D),1)

Note the second argument in the formula is 1 not 0. I had a data label in the first row and that seemed to be the problem. I forgot that scatter plots don't like to have any alpha data in their ranges!