IF function fail when adding negative #?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone explain why the IF function in column E below
is failing on the second set of numbers.
The IF looks like this
=IF(A1+D1=0,"OK",A1+D1)

108 41.79 149.79 -108.00 OK
108.01 41.79 149.8 -108.01 -1.42109E-14

The results vary based upon the combinations of numbers.
 
Hello:

Problem is as old as computing and must come up in a spreadsheet news
group once a week. Try the link below for a discussion see:

http://cpearson.com/excel/rounding.htm

Or do a search in Google groups for
Excel calculates Incorrect Values! Why???
To see a discussion of the issue.

To solve your problem try the following:

=IF(ABS(A1+D1)<0.00001,"ok",A1+D1)

This tests for a very small difference between the values of A1 and D1. The
size of .00001 depends on the level of accuracy you want. The value of
-1.42109E-14 is really about -0.0000000000000142109, very close to zero
but not quite. So the test you used fails since it is not zero. In general
tests of the kind you use will fail with floating point numbers. So you
need to test for approximately zero.

Pieter Vandenberg

: Can anyone explain why the IF function in column E below
: is failing on the second set of numbers.
: The IF looks like this
: =IF(A1+D1=0,"OK",A1+D1)

: 108 41.79 149.79 -108.00 OK
: 108.01 41.79 149.8 -108.01 -1.42109E-14

: The results vary based upon the combinations of numbers.
: --
: John R
 
IF is properly reacting according to whether
=(A1+D1)
is zero or not. Pieter Vandenberg has steered you in the right direction as
far as why results that you expected to be zero aren't (most floating
terminating decimal fractions are non-terminating binary fractions that can
only be approximated) and what to do about it (either test for results being
suitably small, or round the result before testing).

Jerry
 
Back
Top