Almost all of them are probably inexact, but not so much that you would
notice or could detect. It is the accumulation of all the negligible
approximations that is no longer entirely negligible.
As John Woodgate pointed out, computers do binary arithmetic, not
decimal arithmetic. The decimal fractions .00, .25, .50, and .75 can be
exactly represented in binary. No other 2-place decimal fractions can
be exactly represented in binary, and so must be approximated. For
instance, the binary approximation to 0.20 is
3602879701896397/18014398509481984
which in decimal is
0.200000000000000011102230246251565404236316680908203125
You cannot directly detect this approximation, because it differs from
0.2 in the 17 figure, whereas Excel will display no more than 15
meaningful figures.
Indirectly, you can sometimes detect the binary approximations via
subtraction. For instance, the binary approximation to 0.3 is
5404319552844595/18014398509481984
which in decimal is
0.299999999999999988897769753748434595763683319091796875
Thus the formula =(0.2+0.2+0.2-0.3-0.3) will return 1.11022302462516E-16
instead of zero. Here (as in your case) Excel is giving the exactly
correct answer to a math problem that only approximates the problem that
you intended. This is an issue with almost all computers and software,
since almost all computers and software do binary math.
In your case, you can do one of two things:
- Avoid decimal fractions entirely. Multiply all your numbers by 100,
to get integers (which can be exactly represented in binary) then you
can add 28,000 of them with no fear of accumulating approximation residues.
- Round the final result to 2 decimal places, since you know that
anything beyond that is residue from the binary approximations.
Jerry
