Why does Excel XP say that 25-25.08+0.08 = 1.706975E-15 instead o.

[Tarek]

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?

 

[Ken Wright]

http://cpearson.com/excel/rounding.htm

 

[Tim C]

Here is the short(er) version:

Computers can't do math in base 10. They convert everything to binary, do
the calculations, and then convert the results back to decimal for display.

The main problem is that a number with small number of decimal places in
base 10 may have a large or even infinite number of decimal places in
binary, and vice versa. So precise, rational numbers can become rounded off
during one or both conversions. For example, .2 in decimal becomes
..00110011001100110011 repeating infinitely in binary. Excel will chop it
off to a reasonable length at conversion.

Excel uses many tricks to minimize the impact this rounding has, so most of
the time you won't see any discrepencies.

Even when they are there, these small discrepencies are usually not a
problem. No one cares if your sales tax calculation is one trillionth of a
penny off, because you are going to round it to the nearest penny away.

Where you may see a problem is in formulas that rely on comparisons. Such
as IF(A1=0... or A1>0 or a VLOOKUP or MATCH function. If you are having
problems with functions like these, you will need to modify some of your
formulas to compensate, such as =ROUND(25-25.08+.08,2)

Tim C

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/pennyoff.html

and

http://cpearson.com/excel/rounding.htm

 

[Jerry W. Lewis]

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.08000000000000000166533453693773481063544750213623046875
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