+ Reply to Thread
Results 1 to 9 of 9

Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function!)

  1. 02-13-2015, 04:58 PM #1
    gsuty17
    gsuty17 is offline
    Registered User
    Join Date
    02-13-2015
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    4

    Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function!)

    Hello,

    I am putting together a spreadsheet which will have an ever growing number of data entries that will all be catagorized on one of a finite number of data sets. I would like to do the equivalent of a CHARTIF function in order to select the data for the scatter plot, if such a function existed.

    For example, in the simple spreadsheet attached, in order to select the X-Axis data entries for the Ford data series, I would type the following into the 'select data' field:
    =CHARTIF(A2:A1000,E3,C2:C1000)


    Thanks for any help in the right direction!
    Grant
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-14-2015, 04:03 AM #2
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function

    With some array formulas and named ranges that is possible to achieve BUT the problem is that it it's not flexible. Adding another brand would be a mess. Adding another category of data would be a mess. I assume you want to do either of this?
    For clarity, just add some data to the workbook you posted and post it again to visualize the expanding of the data.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  3. 02-14-2015, 04:45 AM #3
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function

    Hi Grant and welcome to the forum,

    This looks like you want a Pivot Chart with Slicer to me. Slicer tool came out in 2010 which you claim to be using. See example.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  4. 02-17-2015, 01:15 PM #4
    gsuty17
    gsuty17 is offline
    Registered User
    Join Date
    02-13-2015
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    4

    Re: Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function

    Thanks for the replies. I've added some more entries so you can get more of a feel of what I'm after.

    Marvin, I've tried a pivot table, but was stuck when I came to the realization that I could not make a scatter plot using the pivot table.

    I realize this tool isn't available, but the best description for what I want is literally a CHARTIF function. In the data selection field of the scatter plot, if I could select what data I wanted based on Range, Criteria, and [sum_range], I'd be home free.

    Instead of summing "x" cells based on the values corresponding label to a "y" cell, I'd simply like to include "x" data in my scatter chart based on the corresponding "y" cell.

    Clear as mud?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 02-17-2015, 01:23 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function

    How about ...

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    1
    Mfr
    MPH
    Dist
    		 Ford Chevy Dodge GMC Toyota Ford Chevy Dodge GMC Toyota
    2
    		 Ford
    20
    60
    20
    #N/A
    #N/A
    #N/A
    #N/A
    60
    #N/A
    #N/A
    #N/A
    #N/A
    		 E2: =IF($A2=E$1, $B2, NA())
    3
    		 Ford
    30
    75
    30
    #N/A
    #N/A
    #N/A
    #N/A
    75
    #N/A
    #N/A
    #N/A
    #N/A
    		 K2: =IF($A2=K$1, $C2, NA())
    4
    		 Dodge
    20
    65
    #N/A
    #N/A
    20
    #N/A
    #N/A
    #N/A
    #N/A
    65
    #N/A
    #N/A
    5
    		 GMC
    20
    62
    #N/A
    #N/A
    #N/A
    20
    #N/A
    #N/A
    #N/A
    #N/A
    62
    #N/A
    6
    		 Toyota
    30
    68
    #N/A
    #N/A
    #N/A
    #N/A
    30
    #N/A
    #N/A
    #N/A
    #N/A
    68
    7
    		 Ford
    50
    100
    50
    #N/A
    #N/A
    #N/A
    #N/A
    100
    #N/A
    #N/A
    #N/A
    #N/A
    8
    		 Dodge
    50
    115
    #N/A
    #N/A
    50
    #N/A
    #N/A
    #N/A
    #N/A
    115
    #N/A
    #N/A
    9
    		 Ford
    22
    55
    22
    #N/A
    #N/A
    #N/A
    #N/A
    55
    #N/A
    #N/A
    #N/A
    #N/A
    10
    		 Dodge
    23
    63
    #N/A
    #N/A
    23
    #N/A
    #N/A
    #N/A
    #N/A
    63
    #N/A
    #N/A
    11
    		 Ford
    21
    64
    21
    #N/A
    #N/A
    #N/A
    #N/A
    64
    #N/A
    #N/A
    #N/A
    #N/A
    12
    		 Dodge
    19
    62
    #N/A
    #N/A
    19
    #N/A
    #N/A
    #N/A
    #N/A
    62
    #N/A
    #N/A
    13
    		 Ford
    18
    58
    18
    #N/A
    #N/A
    #N/A
    #N/A
    58
    #N/A
    #N/A
    #N/A
    #N/A
    14
    		 GMC
    40
    80
    #N/A
    #N/A
    #N/A
    40
    #N/A
    #N/A
    #N/A
    #N/A
    80
    #N/A
    15
    		 GMC
    45
    85
    #N/A
    #N/A
    #N/A
    45
    #N/A
    #N/A
    #N/A
    #N/A
    85
    #N/A
    16
    		 Toyota
    35
    70
    #N/A
    #N/A
    #N/A
    #N/A
    35
    #N/A
    #N/A
    #N/A
    #N/A
    70
    17
    		 Toyota
    50
    80
    #N/A
    #N/A
    #N/A
    #N/A
    50
    #N/A
    #N/A
    #N/A
    #N/A
    80
    18
    		 Toyota
    70
    100
    #N/A
    #N/A
    #N/A
    #N/A
    70
    #N/A
    #N/A
    #N/A
    #N/A
    100
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  6. 02-17-2015, 01:48 PM #6
    gsuty17
    gsuty17 is offline
    Registered User
    Join Date
    02-13-2015
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    4

    Re: Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function

    SHG,

    That will almost do it. Now I'm just being picky, bu there are two problems with this method. My actual spreadsheet is already very large, so I would need to create a separate sheet with duplicate data, then generate your if/then grid to base my scatter off of. Which I have no idea how to do, if it can be done.

    The other issue I have is, the #n/a's appear as 0's on my chart and ruin my best fit line... nevermind. I can have it just insert a blank. So my next question, is, how or can you create a mirror sheet to work off of?

    Thanks!
    Reply With QuoteRegister To Reply
  7. 02-17-2015, 02:05 PM #7
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function

    1. Why not just put it on the same sheet? You can hide the columns if you like.

    2. NA() doesn't plot and doesn't affect a trendline.
    Reply With QuoteRegister To Reply
  8. 02-17-2015, 03:17 PM #8
    gsuty17
    gsuty17 is offline
    Registered User
    Join Date
    02-13-2015
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    4

    Re: Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function

    SHG,

    Thanks! The NA()'s worked that time. Somehow I got them to show up as zeroes the first go around.

    Thanks!
    Reply With QuoteRegister To Reply
  9. 02-17-2015, 04:45 PM #9
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Dynamic Updates for Randomly Dispersed Scatter Plot Entries (I want a CHARTIF function

    You're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Add Data Labels to Dynamic Scatter Plot
    By johnctholen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2014, 10:08 AM
  2. [SOLVED] Dynamic Scatter Plot
    By LadyS in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 11-17-2014, 06:02 PM
  3. [SOLVED] XY scatter plot and 'table' for dynamic x and dynamic names from which the data is pulled
    By Geoff. in forum Excel General
    Replies: 10
    Last Post: 10-09-2014, 06:20 PM
  4. Dynamic Range for Chart scatter plot
    By dpk1 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 04-30-2013, 03:29 AM
  5. [SOLVED] Dynamic Scatter Plot
    By blaxal in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-22-2012, 08:12 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1