Thread: 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.

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.

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

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