You are getting the exact answer to an approximate problem. As the
reference that Frank provided explains, almost all software (not just
Excel) does math in binary. None of your decimal fractions can be
exactly represented in binary; since your inputs are approximate, it
should be no surprise that the output is also approximate.
The IEEE double precision binary approximation to 646.63 is (converted
back to decimal) 646.6300000000001091393642127513885498046875. Based on
the IEEE approximations to the values in the sum, the actual value in
S127 is 646.6299999999999954525264911353588104248046875. Do the math,
the actual difference between these numbers is
-1.136868377216160297393798828125E-13, which Excel correctly reports in
up to 15 decimal places.
You can fairly accurately predict the potential magnitude of
inaccuracies resulting from finite precision and decimal/binary
conversions from Excel's specifications that state that the accuracy of
calculations is 15 decimal digits. Thus your problem is in effect
99.7400000000000??
+253.210000000000???
+295.470000000000???
-1.79000000000000?
--------------------
646.630000000000???
and
646.630000000000???
-646.630000000000???
--------------------
0.000000000000???
which is entirely consistent with the actual result of
-0.000000000000113686837721616
These are standard issues in floating computer floating point
calculations, that have been around for over half a century (long before
Excel). The standard approach is to use
=IF(ABS(U27-S127)<epsilon,trueAction,falseAction)
Equivalently, you can round the difference as Frank suggested.
You didn't ask, but are probably wondering why
=U127-S127
returns zero, but your IF function doesn't. It is Microsoft's attempt
to avoid these questions. If the last operation is to calculate the
difference between numbers that are equal to 15 decimal digits, then
Excel will arbitrarily zero the result. Otherwise (as in your If
function, where the difference is embedded in another function call)
Excel returns the exact value of math operations, since to do otherwise
would introduce inaccuracies beyond the unavoidable finite precision
approximations to numbers.
Jerry
