(B2-A2)=0; How to get a blank and NOT '0'

  • Thread starter Thread starter Rum
  • Start date Start date
R

Rum

Hi,

When I do (B2-A2) I get a '0'.

How can I have a blank cell as a result even if the answer is '0'?

Thanks
Regards
Rum
 
Rum said:
When I do (B2-A2) I get a '0'.
How can I have a blank cell as a result even if the answer is '0'?
Click to expand...

If you want the value of the cell to be "blank" (actually the null string)
instead of zero, then:

=if(B2-A2 = 0, "", B2-A2)

Alternatively, simply use =B2-A2 and create the custom format
"General;-General;" without quotes. You can replace "General" with whatever
numeric format you wish, e.g. "0.00;-0.00;" for the equivalient of Number
with 2 decimal places, but blank for zero.

Using a custom format, the value in the cell is still zero; it just appears
blank. Using the IF() expression, the value in the cell is the string ""
instead of zero. That can cause complications in formulas that reference
the cell value.

PS: Beware that sometimes B2-A2 __appears__ zero, but it actually is not.
Neither approach avoids displaying 0 in that case. Are you worried about
that?
 
On Jul 29 said:
Alternatively, simply use =B2-A2 and create the custom format
"General;-General;" without quotes. �You can replace "General" with whatever
numeric format you wish, e.g. "0.00;-0.00;" for the equivalient of Number
with 2 decimal places, but blank for zero.
Click to expand...
<snip>

Is that so ? Maybe in later versions of Excel but my Excel 97 needs
the third specification to show zero. ie . . .

0.00;-0.00;""

The first element is the format for positive values, the second is for
negative values and the third is for zero values.

Strings in the format specifier are useful for special displays.
eg . . .

"Pay �"0.00;"Refund �"0.00;"Nothing to pay"

.. . . instead of using a function formula to generate alternative cell
display strings.

Alan Lloyd
 
Back
Top