Suppress zero value from chart

[chrismbmc]

Building 12 month running chart. I want to be able to add values as the year goes by and have the chart automatically update -Need to suppress 0 values that represent future periods from chart. I know blank cells can be dropped. Can you assign a blank cell via a formula?

Thanks for the input.

 

[Mark W.]

> Can you assign a blank cell via a formula?

No, but you can assign #N/A.

 

[Jim North]

You could also change the formatting... though I'm not sure it will affect your chart.

Select the cells to be formatted.
Press CTRL+1.
Click the NUMBER tab.
Under Category select CUSTOM.
To the left under Type:, key in #.
Click on OK.

 

[Mark W.]

Charted values are the internally stored values (which can include 0). Formatting never changes the internally stored value in a cell. A 0 is still a 0 regardless of its format.

 

[XL-Dennis]

chrismbmc,

Just follow up Mark´s answer with an example:

=IF(SUM(A4:A20)=0,NA(),SUM(A4:A20))

 

[PaddyD]

& just another addition - excel only charts visable cells, so you can exclude zeros (or indeed any other value) from a chart by applying an appropriate filter to the source data.

Paddy

 

[chrismbmc]

NA() works like a champ.

Thanks