+ Reply to Thread
Results 1 to 5 of 5

How to ignore zero values when plotting a graph

  1. #1
    Phil Lavis
    Guest

    How to ignore zero values when plotting a graph

    Using Excel 2003. I have a data range for a graph. The values in the cells
    are the results of a simple If function - If(m28>0,n28,0). The results are
    taken from a larger data input exercise. But, the graph line (a simple
    graph!) plots the FALSE value (0) when I would like there to really be no
    value & hence no plotted point if the result is FALSE.

  2. #2
    Jon Peltier
    Guest

    Re: How to ignore zero values when plotting a graph

    Phil -

    Change this:

    If(m28>0,n28,0)

    to this:

    If(m28>0,n28,NA())

    This results in the ugly #N/A error in the cell, but it makes the chart
    ignore the point. Debra Dalgleish shows how to hide the ugliness with
    conditional formatting:

    http://contextures.com/xlCondFormat03.html#Errors

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    Phil Lavis wrote:

    > Using Excel 2003. I have a data range for a graph. The values in the cells
    > are the results of a simple If function - If(m28>0,n28,0). The results are
    > taken from a larger data input exercise. But, the graph line (a simple
    > graph!) plots the FALSE value (0) when I would like there to really be no
    > value & hence no plotted point if the result is FALSE.


  3. #3
    John Mansfield
    Guest

    Re: How to ignore zero values when plotting a graph

    Hi Jon,

    Using a standard line chart, I seem to be getting an interpolated value with
    NA() as opposed to a gap in the line. Could I be missing a setting somewhere?

    For example, my original data is on the left side below and charted data is
    on the right. Assuming a blank column between the two sets with the data
    starting in cell A1, I've added this formula to cell E1 and copied down the
    column:

    =IF(B1>0,B1,NA())

    a 6 a 6
    b 4 b 4
    c 0 c #N/A
    d 5 d 5
    e 0 e #N/A
    f 3 f 3

    Excel seems to interpolate the line in column E rather than leaving a gap.

    Tushar Mehta has this information on his site - does this still apply?

    http://www.tushar-mehta.com/excel/so...discontinuity/

    Thanks.

    John Mansfield


    "Jon Peltier" wrote:

    > Phil -
    >
    > Change this:
    >
    > If(m28>0,n28,0)
    >
    > to this:
    >
    > If(m28>0,n28,NA())
    >
    > This results in the ugly #N/A error in the cell, but it makes the chart
    > ignore the point. Debra Dalgleish shows how to hide the ugliness with
    > conditional formatting:
    >
    > http://contextures.com/xlCondFormat03.html#Errors
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    > Phil Lavis wrote:
    >
    > > Using Excel 2003. I have a data range for a graph. The values in the cells
    > > are the results of a simple If function - If(m28>0,n28,0). The results are
    > > taken from a larger data input exercise. But, the graph line (a simple
    > > graph!) plots the FALSE value (0) when I would like there to really be no
    > > value & hence no plotted point if the result is FALSE.

    >


  4. #4
    Tushar Mehta
    Guest

    Re: How to ignore zero values when plotting a graph

    No, you are not missing anything. XL will only interpolate over NA()s.
    It won't create gaps. To get gaps, you have to use a programmatic
    solution such as
    Chart gap for N/A
    http://www.tushar-mehta.com/excel/so...ity/index.html

    However, because of a bug introduced with XL2002 (2000?) and not yet
    fixed means even the code doesn't work with a line chart, though it
    continues to work just fine with a XY Scatter chart.

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <B67FA397-07B4-4B35-B3D2-0811647B8C38@microsoft.com>,
    JohnMansfield@discussions.microsoft.com says...
    > Hi Jon,
    >
    > Using a standard line chart, I seem to be getting an interpolated value with
    > NA() as opposed to a gap in the line. Could I be missing a setting somewhere?
    >
    > For example, my original data is on the left side below and charted data is
    > on the right. Assuming a blank column between the two sets with the data
    > starting in cell A1, I've added this formula to cell E1 and copied down the
    > column:
    >
    > =IF(B1>0,B1,NA())
    >
    > a 6 a 6
    > b 4 b 4
    > c 0 c #N/A
    > d 5 d 5
    > e 0 e #N/A
    > f 3 f 3
    >
    > Excel seems to interpolate the line in column E rather than leaving a gap.
    >
    > Tushar Mehta has this information on his site - does this still apply?
    >
    > http://www.tushar-mehta.com/excel/so...discontinuity/
    >
    > Thanks.
    >
    > John Mansfield
    >
    >
    > "Jon Peltier" wrote:
    >
    > > Phil -
    > >
    > > Change this:
    > >
    > > If(m28>0,n28,0)
    > >
    > > to this:
    > >
    > > If(m28>0,n28,NA())
    > >
    > > This results in the ugly #N/A error in the cell, but it makes the chart
    > > ignore the point. Debra Dalgleish shows how to hide the ugliness with
    > > conditional formatting:
    > >
    > > http://contextures.com/xlCondFormat03.html#Errors
    > >
    > > - Jon
    > > -------
    > > Jon Peltier, Microsoft Excel MVP
    > > Peltier Technical Services
    > > Tutorials and Custom Solutions
    > > http://PeltierTech.com/
    > > _______
    > >
    > > Phil Lavis wrote:
    > >
    > > > Using Excel 2003. I have a data range for a graph. The values in the cells
    > > > are the results of a simple If function - If(m28>0,n28,0). The results are
    > > > taken from a larger data input exercise. But, the graph line (a simple
    > > > graph!) plots the FALSE value (0) when I would like there to really be no
    > > > value & hence no plotted point if the result is FALSE.

    > >

    >


  5. #5
    Registered User
    Join Date
    07-28-2014
    Location
    Neaples, Italy
    MS-Off Ver
    2007 (windows 7)
    Posts
    1

    Re: How to ignore zero values when plotting a graph

    I tried many times to implement a chart with discontinuity using "TM Chart Gap for #N/A" by Tushar Mehta... but it does'nt work; it always interpolates.
    Maybe because in italian #N/A becomes #N/D??
    thanks anyway.
    mimmo_ti

+ 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1