If value = 0, cell is blank... how?

I

ian123

Does anyone know a cell format that will result in the cell being
completely blank if the value is zero?

I have been using an if statement <<< if(a1>0,a1-a2,"") >>> but
this keeps resulting in 0 appearing in the destination cell?

Any help would be much appreciated
 
A

A.W.J. Ales

Ian,

No you can't get (without VBA) an blank in the destinationcell.
It will always contain your formula, resulting in either a value or
something like ""

You could however write a subroutine, reacting on filling of A1 (or A2), to
"empty" your destionationcell.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
T

Trevor Shuttleworth

Ian

perhaps this will do what you want:

=if(a1-a2<>0,a1-a2,"")

Regards

Trevor
 
N

Norman Harker

Hi Ian!

You can use conditional formatting and select a font color the same as
the background. It will appear empty but it isn't. It makes it more
difficult if the cell's protection status is locked and hidden and
worksheet protect is in place.

But formulas and functions in cells cannot do more than return values
to cells. Formatting affects how they appear and not what is held.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I

ian123

Cheers guys, i'll be using conditional formating to make the 0's white
and thus appear hidden - a very useful trick! Thanks a lot
 
N

Norman Harker

Hi Ian!

Another approach. For an entire sheet you can use:

Tools > Options > View
Remove check from Zero Values.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top