Hiding calculated zeroes

[TomH]

Hi all.

Is there a way of having zero values that come from calculations (SUM of blank cells, etc.) to be formatted that the cell will appear blank but ... if a zero is entered directly into a cell, the zero shows? I get tired of having to use "=IF(SUM(A1:A10)=0,"",ELSE) or the like. This always causes an error down the line if I try to use this result in a dependent formula.

Thanks for any help.

 

[CJ_London]

No need to use the if statement, use custom format

depends on your number structure but something like

#,###;[red](#,###);;

You can have four format states

Positive, Negative, zero, text, separated by ;

here is a link

http://www.ozgrid.com/Excel/CustomFormats.htm

 

[TomH]

Thanks, CJ, but that results in the same problem I'm having now, where no zero values show at all. What I am hoping to achieve is that zero values from calculations do not show in the cell, but that directly input zeroes do appear.

For example, if cell C3 contains the formula =A3-B3 and the result is zero, i do not want the cell to show anything. However, if I enter zero (0) into the cell directly, I want that to appear in the cell.

Thanks for your help.

 

[CJ_London]

0;[red]0;;@

will work if you enter '0 rather than just 0 - because you a enter the zero as text - basically any text will show

An alternative is

0;[red]0;;"0"

which will display a zero if you enter any text e.g. a single quotation or any letter