It is not an error, and it is not unique to Excel.
Almost all computer software works in binary. In binary, 0.67 (67%) and
0.33 (33%) (along with most other terminating decimal fractions) are
non-terminating binary fractions that can only be approximated in binary
(just as 1/3 is a non-terminating decimal fraction that can only be
approximated in decimal). When you do math with approximate inputs, it
should be no surprise when you get approximate results.
The decimal values of the binary approximations to 0.67 and 0.33 are
0.67000000000000003996802888650563545525074005126953125
0.330000000000000015543122344752191565930843353271484375
You don't see these full values because as documented (in Help), Excel
displays no more than 15 significant digits for a number. If you do the
math, you will see that the correct result of subtracting these values from 1
is
-5.5511151231257827021181583404541015625E-17
as Excel reports (again to 15 digits).
The "optimization" mentioned in E-Coder's reference is that if the final
operation is to difference two numbers that are equal to 15 digits, then
Excel will arbitrarily zero the result regardless of the actual difference.
IMHO this causes more questions than it answers. In particular, this
"optimization" means that you will NOT get the same answers from
=1-0.67-0.33
and
=1-0.67-0.33-0
The realities of finite precision arithmetic, compounded by decimal/binary
conversions have been well known for over half a century (long before Excel
was a gleam in Bill's eye). A better solution than this "optimization" is to
develop an informed user base who can then intelligently apply the standard
approaches for dealing with these issues. Aside from introducing seeming
inconsistencies into Excel's arithmetic, the "optimization" is often either
not enough or too much, depending on what a particular user trying to do.
That user though, is capable of determining what level of fuzzing is
appropriate to the particular application, and to apply that level by either
rounding results before comparisons, or comparing
ABS(difference) to a small positive value instead of asking if the
difference is exactly zero.
In your case, since you are adding/subtracting numbers with no more than 2
decimal places, you can round results to 2 decimal places with no violence to
the intended calculation.
Jerry
