Dynamic Range for Chart scatter plot

Hi All,

I hope someone can help as I just cant figure this out.

I wanted to learn howto plot a series of latitude/longtitudes points to a background map on a chart using the Excel scatter plot. Being a novice I thought I'd make a start by just drawing 4 points of a square and try to move these points around using dynamic ranges.

On the attached spreadsheet I use cell A1 to increment in 2's and this changes the range that the chart gets it's data (B3-C6) from.

When A1 = 0 it draws the square
when A1 = 2 it moves the square
When A1 = 4 it moves the square again but the top left corner is drawn at (0.5,1) to prove it can
But when A1 = 6 or 8 it doesn't plot the points in the correct places and I cant understand why.

It seems everytime I use less then 4 points to plot it screws up the plotting? I was only messing with this to get a better understanding of scatter plots and because eventually I hope to have like 2000+ points to plot marking geographical locations on a backdrop map and animate this over a series of frames. But it seems like my experimenting just opened up a can of worms that I cant get past.

I guess my question is why doesn't it plot less than 4 points accurately and if that's just something silly I missed or did wrong (FINGERS CROSSED). Can someone suggest a method that will allow me to vary the number of rows dynamically.

Eg eventually somedays there will be like 1000 points to plot and other times more or less.

I'm guessing my formula wont do the trick

=IF(OFFSET(B3,0,2+$A$1,1,1)="","",OFFSET(B3,0,2+$A$1,1,1))

Would appreciate any help as I've been pulling my hair out here....

Hi Dilipandey,

Thank you for your response, unfortunately it's not quite what I was looking for although your formula is much cleaner.

My explanation was quite poor. I was hoping a formula could be used in cells B3:B6 that took into account the number of rows and therefore the number of points to plot. Probably it should have a count() to count the number of rows for a given dataset eg when A1 = 6 or 8 in the example there are only 2 rows and 2 values to plot. When A=4 there are 5 rows and 5 values to plot.

My second problem is why do the datasets when A1=6 or 8 not show correctly plotted on the chart?

In the attached I changed the data for A1=6 (j3:k5) and there should 2 points plotted at 5,1 and 10,1 but the chart shows these points wrongly plotted at 1,1 and 2,1 respectively?

When A1=8 (L3:M5) it plots 3 points but there are only 2 points in the dataset. Im guessing its plotting the blank values that it should just be leaving blank which is why I think the formula needs to dynamically reflect the number of rows in the dataset.

Hi Dilipandey,

I kinda fixed my problem, not perfectly but I understand now why the plots were all astray. It appears that the scatter chart does not like empty strings "" as co-ordinate values. So where my formulas contained empty strings

eg

=IF(OFFSET(B23,0,2+$A$1,1,1)="","",OFFSET(B23,0,2+$A$1,1,1))

I substituted the "" for a cell value $B$3

=IF(OFFSET(B23,0,2+$A$1,1,1)="",$B$3,OFFSET(B23,0,2+$A$1,1,1))

Now if I run the formulas in columns B & C down to the number of co-ordinates (or rows) then it will work.

As per example with an India map.

I would rather have had a formula that could determine the number of rows like a count() but I just cant figure one out to keep it dynamic and based on the number of rows in the dataset.