Problem on an IF formula

G

Gerard Sanchez

Hi,

IF(B853-(B854+B855)<>0,B853-(B854+B855),"")

here it with the cell values instead of cell address:

IF ( 83.80 - ( 40.45 + 43.35 ) <> 0, 83.80 - (40.45 + 43.35), "" )

--The problem is that on the condition :

IF ( 83.80 - ( 40.45 + 43.35 ) <> 0, --> Excel is giving me a TRUE result.
Shouldn't this condition produce a False result??

Excel then proceeds to do the arithmetic with a value of -1.4219E -14

The values of these 3 cells have been checked many times, and they are all
typed exactly to 2 decimal places.

Any thoughts?
 
T

T. Valko

This is "common" behavior when converting binary arithmetic to decimal
results. Internally, Excel performs binary arithmetic then displays the
result in decimal format so that we humans can understand what it is we're
looking at.

The solution is to use rounding:

=IF(B853-ROUND(B854+B855,1)<>0,B853-ROUND(B854+B855,1),"")
 
G

Gerard Sanchez

Hi it worked! But how would I relate this to:

IF(B855<>0,B853-(B854+B855),"")

Can you it work with this formula as well.



try this

=IF(TRUNC(B853,2)-TRUNC(B854+B855,2)<>0,B853-(B854+B855),"")
 
S

Shane Devenshire

Hi,

There is nothing wrong with your formula, it is just the way computers work.
change the formula to

=IF(ROUND(B3-B4-B5,2)<>0,B3-B4-B5,"")

Computers work in binary, we work in decimals which results in
approximations by Excel and any computer.

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html
 
G

Gerard Sanchez

It all worked!@

Thank you very Much Guys both for the taking the time to correct the formula
and the additional explanation!

Many Thanks! :)
 
G

Gerard Sanchez

Never mind. Thank you for your help.


Gerard Sanchez said:
Hi it worked! But how would I relate this to:

IF(B855<>0,B853-(B854+B855),"")

Can you it work with this formula as well.



try this

=IF(TRUNC(B853,2)-TRUNC(B854+B855,2)<>0,B853-(B854+B855),"")
Click to expand...
 
Top