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

  • Thread starter Thread starter ian123
  • Start date Start date
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
 
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 *
 
Ian

perhaps this will do what you want:

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

Regards

Trevor
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top