Skip the Zeros in a chart
 

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)
--
Thank you!

Skip the Zeros in a chart
 

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" wrote in message
...
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)
--
Thank you!

Skip the Zeros in a chart
 

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



"Peter T" wrote:

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" wrote in message
...
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)
--
Thank you!


.

Skip the Zeros in a chart
 

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
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Peter T wrote:
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" wrote in message
...
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)
--
Thank you!

Skip the Zeros in a chart
 

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

Regards,
Peter T

"Jon Peltier" wrote in message
...
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
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Peter T wrote:
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" wrote in message
...
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)
--
Thank you!