Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Dynamic Range for Scatter Graph

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Range for Scatter Graph

    Hi,

    I am trying to develop a dynamic range for a scatter graph from an index that selects a row of data from the raw data below for comparison. My formula at the moment looks like what you can see below; however I can't quite get it to work. Is there something obvious I am missing?

    =SERIES('Total Report'!$C$6,'Total Report'!=OFFSET($D$5,0,0,MAX(IF(ISNUMBER(5:5),ROW(5:5)-3,0)),1)),'Total Report'!=OFFSET($D$6,0,0,MAX(IF(ISNUMBER(5:5)-3,0)),1)),1)

    Regards,

    Hereward

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,490
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    are you comparing 1 row with the whole data - what are you trying to achieve ?

  3. #3
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    Your SERIES formula can only contain references to ranges or hard-coded values.

    You can define Names that include the OFFSET formulas, and reference these names in the SERIES formula. See Dynamic Charts for a tutorial.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  4. #4
    New Member
    Join Date
    Jul 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    Hi, All thank you for the advice.

    I was comparing one row against another row and plotting it onto a scatter graph. I managed to have the scatter graph working by using the following:

    =IF(ISBLANK(B32),#N/A,INDEX(B32:B137,$B$5))

    and then the scatter plot is completed for with the following:

    =SERIES('Total Report'!$C$7,'Total Report'!$D$6:$XFD$6,'Total Report'!$D$7:$XFD$7,1)

    This has allowed me to create a scatter graph that only plots cells with numbers in. However the trouble I have now is that I am trying to create a line graph for the same pieces of data. I want to compare three different rows of data and I can indefinitely extend 2 of the data sets however the first one, which I am assuming the line chart creates the "count" still counts #N/A as a value and therefore I end up with all the data right up against the Y-axis.
    Is there a reason this method works for the scatter graph and not the graph measuring the value by count?

    Thank you for any feedback

  5. #5
    New Member
    Join Date
    Jul 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    If it makes it easier to understand, the new graph is not plotting the points as "0" it is automatically adjusting the axis to be the full count all the way up to "XFD". Is there anyway to ensure the x-axis range is determined by the the total number of cells not containing "#N/A".

    =SERIES('Total Report'!$C$6,,'Total Report'!$D$6:$JM$6,1)

    =SERIES('Total Report'!$C$7,,'Total Report'!$D$7:$XFD$7,2)

    =SERIES('Total Report'!$C$5,,'Total Report'!$D$5:$XFD$5,3)

    As you can see 2,3 I can plot all the way to "XFD" however if I do the same to 1 the x-axis changes the count all the way to XFD. To clarify the amount of data will change on daily or even hourly basis therefore I cannot set a range as there will inevitably be blanks or data excluded.

    I hope I have explained myself well enough.

  6. #6
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    What data are you using for your X values? If you leave that blank in the series formula, Excel will use the counting numbers (1, 2, 3, ...), and it isn't smart enough to only assign counting numbers to points that have numbers for Y values.

    Actually, I don't really understand what your data is doing. Could you show a screen shot of the data, and what you're trying to plot?
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  7. #7
    New Member
    Join Date
    Jul 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    [IMG][/IMG]

    Hi Jon,

    Here is a screen shot. What I am trying to do is related to the graph in the middle. C5,C6 and C7 onwards is the data. At the moment there are about 272 entries and the rest of the row is filled with #N/A in accordance to the previously mentioned criteria. The Scatter graph will plot all numbers and only numbers depending the amount of values in the data rows ignoring N/A. However the middle where I extend the first series to the end of the sheet will count every cell including N/A is there anyway to generate the count based only on cells with values?

  8. #8
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    Okay, this is a little more clear.

    Are the #N/A values located anywhere in the row, or are the numbers all first and #N/A after you run out of numbers?

    Are the same rows always plotted?

    I think you're making this harder on yourself than you have to, and I also think this is one of those cases where VBA is not needed.

    Are your X and Y values shown at least in part in the screenshot? What is the address of the cell containing the value 0.69? (You forgot to capture the row and column headings.)
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  9. #9
    New Member
    Join Date
    Jul 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    Are the #N/A values located anywhere in the row, or are the numbers all first and #N/A after you run out of numbers?

    The latter, all numbers first, followed by N/A.

    Are the same rows always plotted?

    The same rows are always plotted.

    Are your X and Y values shown at least in part in the screenshot? What is the address of the cell containing the value 0.69? (You forgot to capture the row and column headings.)

    The value 0.69 is D6, but this is indexed from B37 which can be see in the bottom of the screen shot, which is derived from this (which is in D6):
    =IF(ISBLANK(B32),#N/A,INDEX(B32:B137,$A$5))


    I hope that makes it clear, and thank you for you help.

  10. #10
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Range for Scatter Graph

    All right. The blue, red, and green data in the chart seem to correspond to rows 7, 6, and 8. Is this correct?

    And, what should the chart use for X values?
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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