Selective Display of Data on a Chart
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Selective Display of Data on a Chart

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Summary: Is there a way to make a chart not display "0" values?

    Details: I have a chart that display all the data in a column. I want it to stop displaying at the row that has a zero in it. So, if my Y-values are in column D, and everything under D300 is 0, I want the chart only to display values upto the 300th row. I am willing to do this within "normal" excel or with visual basic.

    Any suggestions? Please let me know if the question is unclear.

    -DanExcel

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi DanExcel,

    There are myriad ways of accomplishing this, but the easiest is probably just to modify your formulas or whatever produces the data to leave the cells empty rather than writing zeroes in them. If a formula returns the zero you could modify it thus:

    =IF(your formula=0,"",your formula)

    to yield an empty cell rather than a zero. Of course, the chart options must be set to not plot empty cells (Tools -> Options -> Charts tab).
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Better make that...

    =IF(D2,D2,#N/A)

    Use this formula to produce your Y values from the values in column D.

    Damon, a cell containing "" isn't empty.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Damon and Mark,

    I appreciate both of your replies. I had actually already tried Damon's suggestion, and found that it would still plot the values as "0". Using #N/A works better because it does not plot the values at all.

    It does, however, still expand the graph scale so that it will fit all of the accomoate all of the cells used in the clumn, as if they had data. If you know somehow to make it actually scale to the data that is there, that would be helpful.

    Thank you for your input. Let me know if I can clear anything up.

    -DanExcel

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you know somehow to make it actually scale to the data that is there, that would be helpful.

    Thank you for your input. Let me know if I can clear anything up.

    -DanExcel
    This can be accomplished using dynamic ranges. Are you familiar with 'em?

    Take a look at http://www.mrexcel.com/board/viewtop...ic=267&forum=2.

    [ This Message was edited by: Mark W. on 2002-04-03 10:05 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am not familiar with them, and I didnt find anything in excel help for "dynamic range" (although I may not know what to look for). Can you direct me to a reference?

    Thank you

    -DanE

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's one...

    http://www.cpearson.com/excel/excelF.htm#DynamicRanges

    You can also Search this board for a myriad of examples.

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah - I didnt realize that the OFFSET function was what you meant by dynamic ranges. I understand this function (I think), however, when I try to use it in the chart dialog box for the X or Y values of a series, it gives an "Invalid Function" error. Can this dynamic range be used in defining a series in the chart dialog box, or does it need to be in VB?

    Also, fyi, there is a similar post on dynamic ranges entitled "Charting Ranges" (started by Dazed_). The example formula the post is:

    =OFFSET('sheetname'!$B$5,0,0,COUNTA('sheetname'!$B:$B)-1,1)

    I'm not sure if it was intended for use in the chart dialog box, tho.

    Thank you,

    -DanE

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 13:39, DanExcel wrote:
    Ah - I didnt realize that the OFFSET function was what you meant by dynamic ranges. I understand this function (I think), however, when I try to use it in the chart dialog box for the X or Y values of a series, it gives an "Invalid Function" error. Can this dynamic range be used in defining a series in the chart dialog box, or does it need to be in VB?

    Also, fyi, there is a similar post on dynamic ranges entitled "Charting Ranges" (started by Dazed_). The example formula the post is:

    =OFFSET('sheetname'!$B$5,0,0,COUNTA('sheetname'!$B:$B)-1,1)

    I'm not sure if it was intended for use in the chart dialog box, tho.

    Thank you,

    -DanE
    You'd create local (sheet) defined names that refer to these offsets and use the defined names in your chart's SERIES functions...

    =SERIES(,Sheet1!X_Range,Sheet1!Y_Range,1)

    If you data is organized like...

    {"X","Y"
    ;1,10
    ;2,20
    ;3,30}

    Sheet1!Y_Range could simply refer to...

    =OFFSET(Sheet1!X_Range,,1)

    If the above sample data is in Sheet1!A1:B4 then Sheet1!X_Range would refer to...

    =OFFSET($A$1,,,MATCH(9.99999999999999+E307,$A:$A))

    Now, when you add {4,40} to row 5 the chart containing the SERIES function shown above would automatically incorporate the data point, (4,40).

    [ This Message was edited by: Mark W. on 2002-04-03 14:57 ]

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you again for your assistance -- I have been lead to another roadblock, however... The COUNTA() function is counting the #N/A cells... So the graph still displays a much larger range than necessary. Is #N/A entered literally as it is? Or is there a special way to enter it?

    Thank you,

    -DanE

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
  •  

 

 
DMCA.com