Strange error - inaccurate calculations

[Micha³ Chaniewski]

ok, try this:

A1: 125
B1: =A1*0,021 (it is 2,625 - no more, no less)
C1: =ROUND(B1;2) (it is 2,63 - no more, no less)
D1: =C1-B1 (it should be 0,005 - no more, no less - but it is
0,00499999999999989)

WHY?!!!

I'm not sure if it is ROUND function in international version of Excel, I'm
using Polish version of Excel and it is translated, but you for sure know
what function I mean.

 

[Harald Staff]

Hi

It's probably because computers are binary, not decimal, and some decimal
numbers doesn't have exact binary representations. See
http://www.cpearson.com/excel/rounding.htm
on this.

 

[Micha³ Chaniewski]

OK, you're right.
The same is for simple =2,63-2,625
I was able to duplicate this 'error' with a simple C program
So, 'it is a feature - not a bug'

thanks

Michal Chaniewski

 

[Jerry W. Lewis]

The math is exact, but the input numbers are not.

Excel follows the IEEE standard for double precision arithmetic. Part
of that standard is binary representation of numbers. None of the
numbers in B11 have exact finite binary representations (just as 1/3
has not exact decimal representation), so your "no more, no less" is not
true of the computer representation of those numbers).

The decimal resolution of IEEE double precision is roughly decimal 15
digits (see Help for "Excel specifications and limits", sub-topic
"Calculation specifications"). As a result, in computers your
calculation in D1 becomes

2.63000000000000???
2.62500000000000???
---------------------
0.00500000000000???
which Excel reports as
0.00499999999999989
clearly within the approximation range of the inputs. Excel has given
you the result of this calculation under the IEEE double precision
standard. Any program that gives you 0.005 is either not showing you
full precision, rounding the final result, or doing the math
differently. The most common approaches for different math are
- 10 byte extended doubles (e.g. Windows calculator, which is still
not exact, but gives more figures)
- symbolic arithmetic (e.g. Maple or Mathematica)
- binary coded decimal (e.g. XCalc combines BCD with additional
precision, http://www.crbond.com/applications.htm). For comparable
precision BCD is slower than binary and just exchanges one set of exact
problems for a different set of exact problems.

Integers (through 2^53-1) are represented exactly in IEEE double
precision, but most decimal fractions are not. Standard programming
practice for half a century has been to test for near equality, as in
=IF(ABS(D1-0.005)<epsilon, equal, not equal)
instead of
=IF(D1=0.005, equal, not equal)
Equivalently, you could round D1 to minimize the accumulation of
discrepancies due to input approximations.

Jerry