The basic problem is not with Excel per se, but with binary computation
in general. Most decimal fractions (including 2.99999999999999) cannot
be exactly represented in binary, with the result that it must be
approximated. When you do math with approximate inputs, then you also
get approximate answers.
Excel (and almost all general purpose numeric software on almost all
computer platforms) follows the IEEE standard for double precision
arithmetic, which defines the level of approximation. Help for "Excel
specifications and limits" subtopic "Calculation specifications"
describes this level of approximation as roughly 15 decimal digits.
Thus you can think of your problem as
3
-2.99999999999999????...
---------------------
0.00000000000001????...
which is entirely consistent with the answer you received of
0.000000000000010214...
Integers (with no more than 15 digits) can be exactly represented, so
you could restructure your problem as
300000000000000
-299999999999999
----------------
1
then divide the answer by 100000000000000 to get 1E-14
You know based on your original inputs that anything beyond the 14th
decimal place is junk due to binary approximation, so another (probably
the simplest) way to get what you want is to round the original result
to 14 decimal places. Excel can't automatically do that for you because
all it knows are the binary approximations, not what infinite precision
numbers you intended.
In rare situations, Excel can guess at what you may have intended, which
explains the second phenomenon you asked about. Note however, that this
is a guess on Excel's part, and so at times it may guess wrongly. That
is why it gives you the ability to precisely control what it does.
When the value in B1 is equal to -A1 (to 15 decimal digits), then MS
decided (ineffectively) to try to avoid these questions by setting the
result of =A1+B1 equal to zero, instead of returning the actual
difference between the two binary numbers involved. However, that fuzz
factor only gets applied in situations like =A1+B1, where the
subtraction of the nearly equal numbers is the last operation performed.
In =(A1+B1) the parentheses are the last operation, so you get the
actual result of A1+B1 instead of the fuzzy answer of zero.
The following two paragraphs are more technical, and may be skipped if
the preceding answer is adequate to your needs.
The binary representation of 2.99999999999999 is
1.01111111111111111111111111111111111111111111111010010...B1
where the notation is interpreted analogously to decimal scientific
notation as
1*2^1 + 0*2^0 + 1*2^-1 + 1*2^-2 +...
The actual calculation limitation is not roughly "15 decimal digits" but
exactly 53 binary digits, so is nonterminating binary fraction gets
truncated to
1.0111111111111111111111111111111111111111111111101001B1
which in base 10 is equivalent to
6755399441055721/2251799813685248
or
2.999999999999989785948173448559828102588653564453125
which is well within the advertised
2.99999999999999????...
If you subtract that value from 3, then you get exactly
1.0214051826551440171897411346435546875E-14
which Excel correctly returned.
You know that 1/3 + 1/3 + 1/3 = 1 so that 1 - 1/3 - 1/3 - 1/3 = 0.
However 1/3 does not have an exact terminating decimal fraction (or
binary fraction for that matter). So whether you write 0.33 or
0.3333333333, ... no matter how many 3's you tack on, it is not enough
to exactly represent 1/3. Therefore when you do 1 - 1/3 - 1/3 -1/3 in
finite precision (whether decimal or binary) you will not get exactly
zero. Thus the real issue here is finite precision vs. infinite
precision, not decimal vs. binary. Binary doesn't create the problem,
it just extends the problem to numbers like 2.99999999999999 that have
an exact finite precision decimal representation, but no exact finite
precision binary representation. Thus Excel correctly reports
=((1-1/3-1/3)-(1/3))
to be nonzero. However, it recognizes that the final subtraction
involves numbers that are equal to 15 decimal places, so it will guess
that you want to see zero as the answer to
=(1-1/3-1/3)-(1/3)
Jerry
