Problem on an IF formula

  • Thread starter Thread starter Gerard Sanchez
  • Start date Start date
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?
 
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),"")
 
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),"")
 
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
 
It all worked!@

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

Many Thanks! :-)
 
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...
 
Back
Top