# Dynamic Ranges and XY Charts

#### QGroup

##### New Member
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?

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### QGroup

##### New Member
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!

Replies
3
Views
317
Replies
6
Views
172
Replies
4
Views
570
Replies
3
Views
214
Replies
9
Views
670

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,905
Messages
5,834,320
Members
430,277
Latest member
rippernash

### 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.

### Which adblocker are you using?

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

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