Dynamic Ranges and XY Charts

QGroup

New Member
Joined
Jul 9, 2008
Messages
3
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?:confused:
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

QGroup

New Member
Joined
Jul 9, 2008
Messages
3
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,194
Messages
5,594,778
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top