+ Reply to Thread
Results 1 to 7 of 7

Dynamic Range for Chart scatter plot

  1. #1
    Registered User
    Join Date
    12-14-2012
    Location
    uk
    MS-Off Ver
    Excel 2007
    Posts
    14

    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....
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Dynamic Range for Chart scatter plot

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

    Formula: copy to clipboard
    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. #4
    Registered User
    Join Date
    12-14-2012
    Location
    uk
    MS-Off Ver
    Excel 2007
    Posts
    14

    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.
    Attached Files Attached Files

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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. #6
    Registered User
    Join Date
    12-14-2012
    Location
    uk
    MS-Off Ver
    Excel 2007
    Posts
    14

    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.
    Attached Files Attached Files

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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 (?)
More Information
Template Usage (32):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (7)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
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
  • ./showthread.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_facebook.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_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • 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
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1