How to stop 0 values appearing on a column graph

[kalim]

Hi I am using a countif formula to count a certain creteria and then plot the results on a column graph.

The problem I have is that, if the count is 0 the column graph displays a 0 value.

How can I make excel not display a 0 value if that cell's value is 0?
I have tried an if statement with na() i.e

=IF(COUNTIF(A1:A20,answer=0),NA(),COUNTIF(A1:A20,answer))
but is does not work for this type of graph. It just displays an #N/A

Any suggestions?
Thanks.

 

[andylawson5]

Would using double quotes instead of NA() work?
i.e.

=IF(COUNTIF(A1:A20,answer=0),"",COUNTIF(A1:A20,answer))

 

[kalim]

Thanks for the reply.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have already tried that and the graph displays a 0, as the cell is not truly blank.<o></o>
<o></o>
Any other suggestions?<o></o>

 

[andylawson5]

Why do you not want zero to appear on the chart, and what would you rather see?

This is what I currently get:

 

[kalim]

Thanks for the reply.

Add data labels (which I need) to those columns and you will get zero.

I am comparing two data values and it looks bad on the graph if there are alot of zeros there. As mentioned the graph is linked to a countif formula they will change as the workbook is altered.

What I need, is when the countif fomulas return a 0, the graph displays no value (blank) and not a value of 0.

Thanks.

 

[andylawson5]

You can format the data values on the chart so that the 0 values do not appear. i.e. use a custom number format

0;;;

And it should look like this:

 

[kalim]

Thanks that worked perfectly.