Inaccurate Calculations in Excel

[Chris Prawn]

Try this one in Excel 2002 or 2003. Other versions might
show the same result but I've not tried.
In one cell, say A1, type 2.8.
In another cell, perhaps B1, type the formula "=13.8/5"
work it out in your head as well (come on, it's not that
hard, the answer is 2.76)
In a third cell (say A2) find the difference between the
two cells "=A1-B1" should do it.
The answer should be 0.04 as any fool knows.
Now increase the decimal places of cell A2 to 17 places.
My computer now tells me the answer 0.0399999999999996 -
does yours?

Now, this might not matter when working out how much beer
money you've got left to spend at the end of the month,
but I wouldn't trust Excel to land an explorer on Mars.
Hmmmm..

 

[Stefan Hägglund [MSFT]]

Hi Chris!

This behaviour occurs because the Institute of Electrical and Electronics
Engineers (IEEE) 754 floating-point standard, requires that numbers be
stored in binary format.
You can read more at http://support.microsoft.com/?id=214118


Stefan Hägglund
Microsoft

 

[BrianB]

It is fairly well known that Excel contains calculation errors of thi
type. Those who really need a high level of accuracy do not use it

 

[Jerry W. Lewis]

There is nothing wrong with Excel's arithmetic, which almost certainly
is done by the CPU rather than in software.

Almost all computers and computer software do binary rather than decimal
math. Neither 2.8 nor 13.8 have exact binary representations (much as
1/3 has no exact decimal representation). What the OP reports is an
exact answer to an approximate problem, which affects any floating point
calculations done in binary.

Excel, like almost every other software package use IEEE double
precision, which defines how many binary digits to use in approximating
numbers. Given IEEE double precision approximations to 2.8 and 13.8,
the exact answer to this approximate problem is
0.039999999999999591437926937942393124103546142578125
which excel correctly reports to 15 figures (Excel's documented maximum
display precision) as
0.0399999999999996

IEEE double precision resolves 15 decimal digits in its approximation to
decimal numbers, so the best way to think about such problems is
2.80000000000000??
-2.76000000000000??
---------------------
0.04000000000000??
Thus the IEEE double precision answer to this problem of
0.0399999999999996
is actually much better than it might have been.

There are known inaccuracies with Excel's calculation of GAMMALN,
continuous probability distributions, and prior to 2003 in statistical
second moment calculations such as VAR, SLOPE, STEYX, RSQ and LINEST,
but these inaccuracies are due to the algorithms selected for the
particular calculations, not a problem with Excel's arithmetic.

Jerry

 

[Bernard V Liengme]

Hi Chris,
Do you have any 'feeling' for the difference between 0.04 and 0.0399999999999996 ?
I know of no scientific or engineering measurement that could be as accurate as 4 parts in 10 to 15
Let's call the average distance to Mars 35 million miles and let's work out the 'inaccuracy' of 4 parts in 10^15.
It is less than 9/1000 of an inch - not even the thickness of my age-thinned hair!

By the way: as others have pointed out, any computer used to direct a trip to Mars will have the same "error"

Bernard

 

[Guest]

My spreadsheet contained a =if(b1=b2,true,false) which
kept telling me "false" even though, on the face of it
cells b1 and b2 both contained 0.04.

Because of this, my spreadsheet made me unaware of an
impending financial deficit, which in turn made me spend
more on beer last Friday than I would have done.

Sorry but your wrong. Tosser.

-----Original Message-----
Hi Chris,
Do you have any 'feeling' for the difference between 0.04 and 0.0399999999999996 ?
I know of no scientific or engineering measurement that

could be as accurate as 4 parts in 10 to 15

Let's call the average distance to Mars 35 million miles

and let's work out the 'inaccuracy' of 4 parts in 10^15.

It is less than 9/1000 of an inch - not even the

thickness of my age-thinned hair!

By the way: as others have pointed out, any computer used

to direct a trip to Mars will have the same "error"

Bernard



"Chris Prawn" <[email protected]> wrote

in message news:[email protected]...

 

[Jerry W. Lewis]

It is not safe to test for strict equality of floating point numbers in
any programming language. Standard technique for over half a century
(long before MS was a gleam in Bill's eye) has been to test whether
ABS(b1-b2) < epsilon
where epsilon is suitably small.

Jerry

 

[AlfD]

Bernard

I bet your hair is thicker - by a factor of 10 even?

Al