zero in formula problem

[Akpo]

I have a simple calculation (L3-L4-L7) that works fine
except when L3 & L& are the same and L4 is 0. The problem
is that the cell will display 0 instead of the - that is
in all other zero cells and the if formula that points to
this cell does not recognize the 0 as zero. I have tried
changing the formula and formats and even restarting my
computer but nothing seems to work. Interestingly enough,
if I type 0 in the cell, the problem is solved. Please
help.

 

[Dan E]

Akpo,
While I couldn't reproduce your problem this might work, though it's not the
greatest solution, put
=IF(L3-L4-L7<>0,L3-L4-L7,"-")
in the cell, then if your cell is zero it will put "-" in your cell like it
already should?

Dan E

 

[J.E. McGimpsey]

The accounting format will only give a "-" if the value is exactly
0. If your cels are calculated, the values in the cells may be
different than what is displayed. For instance, if your cell is
formatted to 2 decimal places and the calculation results in 1.395,
the cell will display 1.40, but the value used in your sum will be
1.395.

In addition, not all fractional values can be exactly represented in
binary (the way 1/3 cannot be exactly represented in decimal:
0.3333...), so occasionally there are very small rounding errors
that result in a tiny, but non-zero result.

In either case, you can use ROUND() to return 0 for values
sufficiently close. e.g.:

=ROUND(L3-L4-L7,2)