# Dynamic Range for Chart scatter plot

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

2. ## Re: Dynamic Range for Chart scatter plot

Hi dpk1,

Do you need the results from J3:k4 to be shown in B3:C6 when a1 = 6 ?

Regards,
DILIPandey
<click on below * if this helps>

3. ## Re: Dynamic Range for Chart scatter plot

No worries....See the attached file where I used below formula to move the offset accordingly

Formula:
`Please Login or Register  to view this content.`

Entered as array in B3:C6 with key combination : ctrl shift enter

Dynamic range.xlsxDynamic range.xlsx

Regards,
DILIPandey
<click on below * if this helps>

4. ## Re: Dynamic Range for Chart scatter plot

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.

5. ## Re: Dynamic Range for Chart scatter plot

HI dpk1,

I would say first you understand the working of offset function in a separate workbook and then only you should continue with this problem.

Another way to use offset function in "defined names" as then you can actually see what range you are considering while plotting.

Regards,
DILIPandey
<click on below * if this helps>

6. ## Re: Dynamic Range for Chart scatter plot

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.

7. ## Re: Dynamic Range for Chart scatter plot

Hi dpk1,

What I understood here is that when cell A1 is 0, chart data range (B3:C23) will consider D3:E23.
when cell A1 is 2, chart data range (B3:C23) will consider f3:g23.
when cell A1 is 4, chart data range (B3:C23) will consider h3:i23.
when cell A1 is 6, chart data range (B3:C23) will consider j3:k23.
when cell A1 is 8, chart data range (B3:C23) will consider l3:m23.

and I already achieved this and show to you in my post #3... now are we on same page ?

Regards,
DILIPandey
<click on below * if this helps>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.06386 seconds
• Memory Usage 9,041KB
• Queries Executed 15 (?)
Template Usage (32):
• (1)footer
• (1)forumrules
• (1)gobutton
• (7)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (3)postbit_attachment
• (7)postbit_legacy
• (7)postbit_onlinestatus
• (7)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (47):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete
• forumrules
• navbits
• navbits_complete