Skip the Zeros in a chart

[Doug]

How can I have the chart skip Zero values?
I am using the formula below to leave the cell blank for values that are
zero, but it still shows up in the chart messing up the line.
Is there a chart setting for this or do I need to change the formula?
=IF(N454=0,"",N454*4+150000)

 

[Peter T]

In Excel 2003 and older, select the chart, Tools, Options, Chart, Plot empty
cells as: Not plotted (leave gaps), or maybe you might prefer Interpolated.

In Excel 2007 search "Not plotted" in help

Regards,
Peter T

 

[Doug]

I have 2007. I followed the instructions, but it didn't work. If I clear the
empty formula values, the graph looks fine. I just tried having it return a
zero and it is still not working =IF(N454=0,"0",N454*4+150000)

 

[Jon Peltier]

Not Plotted and Interpolated are only applied to actual blank cells, not
cells which have formulas that return "".

The best you can do is change "" in the formula to NA(). This looks like
#N/A in a cell, which looks ugly, but in a line or XY chart is not
plotted. Instead, the connecting line is interpolated across the space
where the point would be plotted.

- Jon

 

[Peter T]

Thanks for posting that, I didn't read the question properly.

Regards,
Peter T

Not Plotted and Interpolated are only applied to actual blank cells, not
cells which have formulas that return "".

The best you can do is change "" in the formula to NA(). This looks like
#N/A in a cell, which looks ugly, but in a line or XY chart is not
plotted. Instead, the connecting line is interpolated across the space
where the point would be plotted.

- Jon