Consider a hypothetical 4-decimal-place computer: 1/3 + 1/3 would then be
calculated as 0.3333 + 0.3333 = 0.6666. Mathematically, the answer should be
2/3, but numerically the answer is correctly not equal to 0.6667 because of
the impact of finite precision.
In the same way for this calculation, A3 and A4 are correctly not equal to
each other. None of the numbers involved have exact binary representations,
and so the approximations to the original numbers result in the following
calculation:
0.6999999999999999555910790149937383830547332763671875
+0.09999999999999997779553950749686919152736663818359375
-------------------------------------------------------
0.79999999999999993338661852249060757458209991455078125
which is not equal to
0.8000000000000000444089209850062616169452667236328125
=(A4-A3) returns 1.11022302462516E-16 which is the correct 15-digit (Excel's
documented display limit) display of the correct difference of
1.1102230246251565404236316680908203125E-16 (which you can verify if you
care to do the math).
AFAIK, Excel's basic arithmetic is done in hardware, not software, and so
would give the same result produced by any other software package that does
not attempt to re-invent the wheel. Valid criticisms of Excel deal with
algorithm choice for calculations beyond basic arithmetic.
The inconsistent comparisons that you got are the result of MS muddying the
waters by vainly trying to "take care of that" as you requested. Since Excel
97, final subtractions of numbers that are equal to 15 decimal digits are
arbitrarily zeroed under the assumption that the very small difference is
unwanted residue of finite precision approximations. If such a subtraction
is not the final operation, then the actual difference is retained, since it
would reduce accuracy if the assumption were wrong. As a result, =(A4-A3)
and =A4-A3 do not return the same value, because surrounding parentheses mean
that the subtraction is not the final operation, and therefore the fuzz
factor is not applied. Similarly, =A4=A3 returns TRUE because the comparison
is the final operation, but =A4-A3=0 compares an unfuzzed (not final
subtraction) to 0 and correctly returns FALSE.
I consider this "optimization"
http://support.microsoft.com/kb/78113
to be a vain attempt, because no attempt to "take care of that" can succeed
in general. Whatever the level of fuzzing, it will be too much for some
calculations and not enough for others. Where it is too much, it will reduce
the accuracy of calculations. Instead, users need to be aware of the
limitations of finite precision arithmetic and do comparisons intelligently.
Instead of asking if A3=A4, ask if ABS(A3-A4)<epsilon, where epsilon is a
number that is appropriately small for the calculation (this has been taught
in programming courses for over half a century). An equivalent approach
would be to either round both numbers or the result to an appropriate level.
Given that all of the posted numbers have no more than one decimal place,
with only adding/subtracting, then rounding to one decimal place would be
perfectly reasonable.
Jerry
