How to ignore zero values when plotting a graph

  • Thread starter Thread starter Guest
  • Start date Start date
G

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