How to ignore zero values when plotting a graph

[Guest]

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.

 

[Jon Peltier]

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/
_______

 

[Guest]

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/software/na_discontinuity/

Thanks.

John Mansfield

 

[Tushar Mehta]

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/software/na_discontinuity/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