View Single Post
  #5   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

For =25-25.08+0.08 I get 1.706968E-15. If you are getting 1.706975E-15
then some of your inputs are not exactly as described (probably the
result of other calculations). The following explains the 1.706968E-15
result, from it you should see the principle that would extend to your
specific problem.

0.08 (like most decimal fractions) has no exact binary representation,
just as 1/3 has no exact decimal representation. When you do math with
approximate inputs, the answer is naturally only approximate.

A direct analogy would be if you had a decimal computer that carried
only 4 decimal digits. In that case, 25-(25+1/3)+(1/3) would be
calculated as
25
-25.33
-----
-0.33
+0.3333
-------
0.0033
instead of zero, for obvious and correct reasons.

Per the IEEE standard for double precision binary representation (used
by Excel and almost all other general purpose computing hardware and
software), the binary approximation to 25.08, converted back to decimal, is
25.0799999999999982946974341757595539093017578125
The binary approximation to to 0.8, converted back to decimal, is
0.080000000000000001665334536937734810635447502136 23046875
Do the math; the correct answer is
1.70696790036117818090133368968963623046875E-15
which Excel correctly reports to 15 figures (Excel's documented limit --
cf. Help for "Excel specifications and limits" subtopic "Calculation
specifications")

It is not necessary to do all the decimal/binary conversions, just
follow the implications of the documented 15 digit accuracy. Your
problem then becomes:
25
-25.0800000000000????????
-------------------------
-0.0800000000000????????
+0.0800000000000000?????
------------------------
0.0000000000000????????
which is entirely consistent with Excel's result of
0.000000000000001706968

For addition and subtraction, the simplest approach to removing the
impact of binary approximation to input numbers is to round the result
to the most number of decimal places used (2 in this case).

Alternately, you could do integer arithmetic (no approximation involved
in converting integers to binary) =(2500-2508+08)/100 will return the
expected zero.

Jerry

Tarek wrote:

Why does excel say that 25-25.08 +0.08= 1.706975E-15 instead of Zero? Are
there situations that excel typically makes calculation errors I should be
aware of?