Glaring Excel calc bug.

  • Thread starter Thread starter baobob
  • Start date Start date
B

baobob

Enter:

=(1.01-1-0.01)

To quote Paul Lynde, "I don't know what you got, but I got a sports
shirt."

***
 
This is a natural consequence of finite precision arithmetic. Excel is
behaving exactly according to the IEEE standard, as most computers and
software in the world do.

If you had a calculator that only carried 4 decimal figures of accuracy,
then you would only expect
(1+x)-1-x
to be zero if x could be represented in 3 decimal figures. For instance, if
x=1/3, then you would have
1.333-1-0.3333
which should not produce zero.

The only potential surprise here is that 0.01 cannot be exactly represented.
But computers work in binary where most terminating decimal fractions are
non-terminating binary fractions. IEEE double precision uses 53 bits to
approximate numbers, so the decimal equivalents of the binary approximations
to 1.01 and 0.01 are
1.0100000000000000088817841970012523233890533447265625
0.01000000000000000020816681711721685132943093776702880859375
so that your calculation correctly results in
8.67361737988403547205962240695953369140625E-18
which Excel reports to its documented display limit of 15 digits as
8.67361737988404E-18

You get 0 from =1.01-1-0.01 (removing the parentheses) because Excel is
trying to be "helpful" and assumes that a final difference between 2 numbers
that are equal to 15 digits is not meaningful. With the parentheses, the
parentheses rather than the subtraction is the final operation, so the fuzz
factor does not kick in to avoid introducing possibly unwanted inaccuracies
into the math.

Jerry
 
Enter:
=(1.01-1-0.01)
Click to expand...

For information on the floating point representation problem you are
experiencing, take a look at these...

INFO: Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1

(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/defaul...pport/kb/articles/Q42/9/80.ASP&NoWebContent=1

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html

Rick
 
I've often wondered, aren't computers fast enough these days that they could do decimal
math, instead of having to do binary conversions? Even if the decimal math had do be done
in software? Better yet, of course, would be for the CPU do be able to do it natively, as
IBM systems were doing decades ago. It seems to me we mess around with having to handle the
errors for a far greater time than additional time the computer might need, even for a large
job. Just a thought.
 
Hi Earl,

That is number 1 on my wish list for Excel. Either in software or supported by hardware, maybe as option you can choose (for the
whole of Excel? For individual cells? Some design issues here).
I guess most Excel users are in the financial field, where floating point calculation causes more problems than it solves.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I've often wondered, aren't computers fast enough these days that they could do decimal
| math, instead of having to do binary conversions? Even if the decimal math had do be done
| in software? Better yet, of course, would be for the CPU do be able to do it natively, as
| IBM systems were doing decades ago. It seems to me we mess around with having to handle the
| errors for a far greater time than additional time the computer might need, even for a large
| job. Just a thought.
| --
| Earl Kiosterud
| www.smokeylake.com
|
| -----------------------------------------------------------------------
| | > This is a natural consequence of finite precision arithmetic. Excel is
| > behaving exactly according to the IEEE standard, as most computers and
| > software in the world do.
| >
| > If you had a calculator that only carried 4 decimal figures of accuracy,
| > then you would only expect
| > (1+x)-1-x
| > to be zero if x could be represented in 3 decimal figures. For instance, if
| > x=1/3, then you would have
| > 1.333-1-0.3333
| > which should not produce zero.
| >
| > The only potential surprise here is that 0.01 cannot be exactly represented.
| > But computers work in binary where most terminating decimal fractions are
| > non-terminating binary fractions. IEEE double precision uses 53 bits to
| > approximate numbers, so the decimal equivalents of the binary approximations
| > to 1.01 and 0.01 are
| > 1.0100000000000000088817841970012523233890533447265625
| > 0.01000000000000000020816681711721685132943093776702880859375
| > so that your calculation correctly results in
| > 8.67361737988403547205962240695953369140625E-18
| > which Excel reports to its documented display limit of 15 digits as
| > 8.67361737988404E-18
| >
| > You get 0 from =1.01-1-0.01 (removing the parentheses) because Excel is
| > trying to be "helpful" and assumes that a final difference between 2 numbers
| > that are equal to 15 digits is not meaningful. With the parentheses, the
| > parentheses rather than the subtraction is the final operation, so the fuzz
| > factor does not kick in to avoid introducing possibly unwanted inaccuracies
| > into the math.
| >
| > Jerry
| >
| > "(e-mail address removed)" wrote:
| >
| >> Enter:
| >>
| >> =(1.01-1-0.01)
| >>
| >> To quote Paul Lynde, "I don't know what you got, but I got a sports
| >> shirt."
| >>
| >> ***
|
|
 
Digital computers have always been inherrently binary. You can impose
decimal representations on to binary patterns (BCD), but the cost is slower
speed and and inefficient use of memory
http://en.wikipedia.org/wiki/Binary_coded_decimal

Using BCD does not eliminate the issues of finite precision arthmetic, as
the example in my original post illustrates. Nor is binary any less accurate
(in fact, it minimizes the relative inaccuracy). You just have to remember
that approximation issues may creep into problems where you otherwise would
not expect them.

Jerry
 
Jerry,

I agree that neither decimal nor binary can represent values precisely. I was just
referring to the conversion issues. If we have decimal values to start with, and want
decimal results, why not do the math in decimal? We can certainly afford the memory and
speed hits with todays hardware.
 
Earl Kiosterud said:
I agree that neither decimal nor binary can represent values
precisely. I was just referring to the conversion issues. If
we have decimal values to start with, and want decimal results,
why not do the math in decimal? We can certainly afford the
memory and speed hits with todays hardware.
Click to expand...
....

So switching to precision as displayed is too difficult for you?

As for arbitrary precision arithmetic, there are several widely and
freely available libraries that provide it, but Microsoft couldn't use
'em because they're licensed under terms Microsoft can't stomach. And
given their track record, I know I wouldn't trust Microsoft to roll
their own and get it right.
 
Ah, an explanation at last as to why my Japanese calculator gives
0.999999 for 2/2, I thought it was the battery going flat.
 
As for arbitrary precision arithmetic, there are several widely and
freely available libraries that provide it, but Microsoft couldn't use
'em because they're licensed under terms Microsoft can't stomach. And
given their track record, I know I wouldn't trust Microsoft to roll
their own and get it right.
Click to expand...

And don't forget the Intel floating point bug, and the replacement
faulty processors program.
 
I've often wondered, aren't computers fast enough these days that they could do decimal
math, instead of having to do binary conversions? Even if the decimal math had do be done
in software? Better yet, of course, would be for the CPU do be able to do it natively, as
IBM systems were doing decades ago. It seems to me we mess around with having to handle the
errors for a far greater time than additional time the computer might need, even for a large
job. Just a thought.
Click to expand...

The Rexx programming language does all calculations in decimal to
arbitrary precision (limited only by available memory).
 
Just two cents to add to the others... That last binary bit does seem a
little strange...:

=(1.01-1-0.01)
8.67361737988404E-18


=10*POWER(2,-60)
8.67361737988404E-18


Another for the library...
Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113


Interesting reading on the section:
"Cases Where We Do Not Adhere to IEEE 754"
 
Sun, 1 Jul 2007 15:15:31 -0400 from Dana DeLouis
Just two cents to add to the others... That last binary bit does seem a
little strange...:

=(1.01-1-0.01)
8.67361737988404E-18
Click to expand...

I get the same result as you. But without the redundant parentheses,
I get precisely 0. (Excel 2003 SP1)
 
Stan Brown said:
Dana DeLouis

I get the same result as you. But without the redundant parentheses,
I get precisely 0. (Excel 2003 SP1)
Click to expand...
....

The Excel developers are allergic to the programming concept of
orthogonality (roughly meaning logical consistency), so Excel applies fudge
factors to floating point arithmetic operations at 'top level', but not to
'intermediate' arithmetic operations. Thus, Excel implicitly rounds the
result of =1.01-1-0.01 but leaves the result of =(1.01-1-0.01) unrounded.
 
Dana DeLouis said:
Another for the library...
Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113


Interesting reading on the section:
"Cases Where We Do Not Adhere to IEEE 754"
Click to expand...
....

In which they state the following misinformation: "with the exception that
IEEE has a concept of -0, and Excel does not". But the Excel formula
=QUOTIENT(0,-1) returns -0, =QUOTIENT(0,-1)=0 returns FALSE (which I believe
is at odds with the IEEE spec), =SIGN(QUOTIENT(0,-1)) returns -1, but
=QUOTIENT(0,-1)*2 returns 0 (this last one is consistent with the IEEE
spec).

It would be more accurate to say that Excel catches MOST -0s and converts
them to 0, but there are a few instances in which the Excel developers were
careless and/or lazy and allowed some functions to return IEEE -0s.
 
=QUOTIENT(0,-1)=0 returns FALSE

Hi. Thanks for this info. I remember this discussion on this "bug"...err
"feature" a while ago.
In Excel 2007, I now get:
=QUOTIENT(0,-1)=0
TRUE

Is this a change/fix for Excel 2007?
If so, why didn't they fix MOD() while they were at it?
 
Dana DeLouis said:
=QUOTIENT(0,-1)=0 returns FALSE

Hi. Thanks for this info. I remember this discussion on this "bug"...err
"feature" a while ago.
In Excel 2007, I now get:
=QUOTIENT(0,-1)=0
TRUE

Is this a change/fix for Excel 2007?
Click to expand...

Probably because now it's built-in, and they had to recode it from the ATP
add-in to whatever language in which they write Excel's built-in functions.
If so, why didn't they fix MOD() while they were at it?
Click to expand...

My own theory is that MOD goes all the way back to Excel version 1, there
was no hardware FPU on the Mac 512K, so MSFT had to write all math
operations in software, and they used Bill Gates's BASICA MOD code, and no
one has had the guts to remove his code since. Maybe MSFT's next marketing
slogan could be 'Where were we 30 years ago?'
 
...

The Excel developers are allergic to the programming concept of
orthogonality (roughly meaning logical consistency),
Click to expand...

Very rough. Orthogonal means perdendicular or independent.
 
I have only seen -0 occur has been on return from macros to Excel, which is
consitent with elimination in 2007 of -0 from QUOTIENT(0,-1) despite AFAIK no
algorithm changes.

Jerry
 
Back
Top