Editing formula results so zeros appear blank

[Holly]

Hi All,

Just wondering if anyone has found a way for a formula result which
calculates to zero to show as a blank cell.

 

[Dave Peterson]

=if(yourformula=0,"",yourformula)
is pretty common

 

[Max]

2 thoughts:

a. Switch off zeros display in the sheet via clicking:
Tools > Options > View tab > Uncheck Zero values > OK

b. Use something like this: =IF(yourformula=0,"",yourformula)

(a) gives a neat uncluttered look, easy to apply & retains the underlying
zero values
(b) converts the formula returns of zeros to null strings: "", which appear
"blank"

 

[Pete_UK]

One trick is to allow the formula to return a zero, but "hide" it by
means of conditional formatting on that cell - if the cell content is
zero then print with a white foreground (which on a white background
will not show).

Hope this helps.

Pete

 

[Holly]

Thankyou
--
Cheers,
Holly

2 thoughts:

a. Switch off zeros display in the sheet via clicking:
Tools > Options > View tab > Uncheck Zero values > OK

b. Use something like this: =IF(yourformula=0,"",yourformula)

(a) gives a neat uncluttered look, easy to apply & retains the underlying
zero values
(b) converts the formula returns of zeros to null strings: "", which appear
"blank"

 

[Max]

welcome, Holly.

 

[Brian]

I have a similar problem as this but want to return an actual blank cell not
an empty string (""). Any ideas?

One trick is to allow the formula to return a zero, but "hide" it by
means of conditional formatting on that cell - if the cell content is
zero then print with a white foreground (which on a white background
will not show).

Hope this helps.

Pete

 

[Bernard Liengme]

If a cell has a formula all it can do is display a value from that formula
There is no way the formula can delete itself.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

I have a similar problem as this but want to return an actual blank cell
not
an empty string (""). Any ideas?