Bug in Excel 2007

  • Thread starter Thread starter Molham Serry
  • Start date Start date
Excel uses the FPU for INTERMEDIATE calculations, but the final value
of any cell formula gets passed through some, er, MSFT value-added
processing before it becomes the cell's value. This is how
nonparenthesized expressions are implicitly rounded to 15 decimal
digits. Also, and quite unfortunately, some built-in function calls
perform additional and usually unnecessary postprocessing of FPU
results, e.g., MOD(2^30-1,3), which returns #NUM! because MOD can't
handle arguments that produce quotients comfortably within 32-bit
integer or 64-bit double precision bounds even though this is well
within the FPU's capabilities.

I don't think this is the case for 850*77.1. I suspect some bright
spark on the Excel development team decided to rewrite their machine
representation-to-human representation internal number formatting
function, and they screwed it up somehow.

What really concerns me is what Dana DeLouis pointed out: with A1
containing =850*77.1, =A1+1 returns 100001 while =A1-1 returns 65534.
That's VERY, VERY BAD because it implies the value in A1 isn't just
fed directly to the FPU stack. It's subjected to, er, value-added
preprocessing which seems to differ depending on what the precise
operation is. What I'm most worried about, given Dana's other
examples, is whether someone thought they were doing Excel users a
favor by treating increment (+1), decrement (-1), idempotent
operations (+0, *1, ^1), binary left shift (*2) and binary right shift
(/2) as special cases in the formula parser. MSFT *DID* rewrite parts
of the formula parser in XL2007, which is how they lifted the 7 nested
function call limit etc. I'd bet they rewrote too much of it.
Click to expand...

If you take a look at the numbers in Octal, you can see what's going
on with adding and subtracting 1.
Even with 64 bit machines, everythings still based on octal.
 
A1: 850*77.1
A2: A1+1
A3: A2+1

A3 displays 65537

I think this is a counterexample to you theory.
 
Thanks. :>~ The article mentions..

"...6 between 65535.99999999995 and 65536"

I can't get an ending 5, but with an ending 6

(65535.99999999996) I get

=PRODUCT(3, 1685479, 324022627)/25000000000

=100001 (for 65536)
 
Complete and utter BS. As pointed out, the Excel representation holds
absolutely nothing in common with either the computers native FP
result, nor with the IEEE standard for floating point.
Click to expand...

The bug is from the FPU, excel just converts its own algorithm to
transfer it to text wich strips the data from a double.

And the 65565 bug is probally caused by an optimisation that causes
excel to use the smallest data type possible to store data (thus
saving a LOT of memory space), but this time they must have messed it
up.
 
Since we have seen a lot of theories,
here is some condensed description.

65535-2^-35, 65535-2^-36, 65535-2^-37,
65536-2^-35, 65536-2^-36, 655356-2^-37

are numbers exhibiting this problematic behavior.
So if a floating point calculation has such a result,
(which is not visible because Excel does not display enough decimal
digits) then you get 10000 or 10001 displayed.

What also is interesting that in these cases
MOD(...,1) gives -2^(-35)... displayed in decimal as
-0.00000000002910383046 (with 20 decimal digits)
MOD(...,1) never should return negative values.
ROUND(...,1) in these cases also produce the wrong result (10000 or
10001), the this is persistent. Any further calculation using the result
of applying the ROUND function will be wrong.

So if the display engine used ROUND and/or MOD to create the displayed
value, that would be consistent with all the errors reported so far.

MS in the Excel blog at
http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx
states that the error occurs with exactly 12 floating point values, 6
near 65535 and 6 near 65536.
Earlier in this mail I listed 6 of them, but I could not find the other
6 yet.
 
Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!
Click to expand...

I think that I have found a second related bug that is in both Excel
2003 and Excel 2007. Namely, if you key in the following:
=DEC2HEX(2^16), it correctly gives 10000 (hex)
=DEC2HEX(2^16-1), it correctly gives FFFF (hex)
=DEC2HEX(65535), it correctly gives FFFF (hex)
=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)

Bill Landrum
 
If it isn't a rendering problem why does the date show correctly for
day number 65535, even when it displays 100000 when formatted as a
number?
Click to expand...

Because the date is stored in two different places in two different
formats. Tests disagree on which area to use when the number is
exactly equal to 65,535. This is probably because some places test for
less than or equal to 65,535 and some test for less than 65,535.

DS
 
»[email protected]« said:
I think that I have found a second related bug that is in both Excel
2003 and Excel 2007. Namely, if you key in the following:
=DEC2HEX(2^16), it correctly gives 10000 (hex)
=DEC2HEX(2^16-1), it correctly gives FFFF (hex)
=DEC2HEX(65535), it correctly gives FFFF (hex)
=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)
Click to expand...

That's no bug, as:

number value stored in FPU value of that
850 0x408a900000000000 850
77.1 0x4053466666666666 77.09999999999999431566
850*77.1 0x40efffdfffffffff 65534.99999999999272404239
65535 0x40efffe000000000 65535

As you see, 77.1 is stored as a periodic binary representation. You can
find the very same problem on your cheap 8-digit or 10-digit calculator
by entering 1 / 3 * 3 =, that is, (1/3) * 3: it will show 0.999999999
instead of 1. Just like it's impossible to represent 1/3 correctly in
the decimal system, 77.1 can't be represented correctly in binary. See
the series of 6666666666 in the number... they are just like the
33333333333 in 1/3 in decimal. When multiplying that so that it SHOULD
cancel that stuff out, a roundoff error causes it to end up as ffffffff
instead of 00000000.

And DEC2HEX apparently always rounds down, so
DEC2HEX(65534.99999999999999) = DEC2HEX(65534). One could argue that the
function should better round to the nearest number, but that decision is
up to the author of such a function.
 
The DEC2HEX function converts integers to hexadecimal. So it truncates the
number to an integer before conversion. So 850 * 77.1 results in 65534
after truncation which is hexadecimal FFFE.
 
Since we have seen a lot of theories,
here is some condensed description.
Click to expand...

A nice synopsis, thanks Erich.

Just to note that although ROUND and INT seem to perpetuate the
erroneous value (I guess INT may just be ROUND(x,0) internally),
ROUNDUP and ROUNDDOWN do not seem to, they use the correct underlying
value and both "round" to 65,535 (or 65,536 as approriate).

Also watch out for the setting "set precision as displayed" (although
I don't know many people that use this in the real world).

see more here:
http://veroblog.wordpress.com/2007/...lation-bug-displays-apparently-wrong-numbers/
 
Since 65535-2^(-35) is not an integer
ROUNDUP(...,0) and ROUNDDOWN(...,0)
should not give the same value.
ROUNDDOWN(65535-2^(-35),0) should be 65534
 
Molham Serry said:
Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!
Click to expand...

Molham, how on earth did you come across this bug???
It needs human eyes to spot it, I think.
Congratulations, Hans.
 
Are you saying excel doesn't use the FP processor? How then did the previous
intel floating point bug appear in excel?

Michael
Click to expand...

I have found around 12000 such number pairs were this bug occurs. Here
is a link to my blog where I have posted a few thousand.
http://pranjan.blogspot.com/2007/09/i-found-12000-recurrence-of-excel-2007.html
[blogspot.com] Here is the ruby code to get a list of these
numbers(however seems my pattern is not completely correct as only
12000 of the 72000 of these number pairs are actually reproducing the
bug) http://pranjan.blogspot.com/2007/09/ruby-code-for-unearthing-vista.html
[blogspot.com]
 
the problem occurs with the series:850/2n and 77.1*2n; 850/3 77.1*3;
850/9 77.1*9. but 850/6 77.1*6 gives the right result, it flaws with
850/625 and so on

850 425 212.5 106.25 53.125 26.5625 13.28125 6.640625 3.3203125
multiply 77.1 154.2 308.4 616.8 1233.6 2467.2 4934.4 9868.8 19737.6
product 100000 100000 100000 100000 100000 100000 100000 100000
100000
minus 1 1 1 1 1 1 1 1 1
65534 65534 65534 65534 65534 65534 65534 65534 65534


5 25 125 625 3125 15625 78125 390625
850 170 34 6.8 1.36 0.272 0.0544 0.01088 0.002176
77.1 385.5 1927.5 9637.5 48187.5 240937.5 1204687.5 6023437.5
30117187.5
100000 65535 65535 65535 100000 100000 100000 100000 100000
1 1 1 1 1 1 1 1 1
65534 65534 65534 65534 65534 65534 65534 65534 65534
 
It's actually just a display error. It doesn't affect any downline
calculations. If a cell with this result is referenced in any other
formula, it is treated exactly as a value of 65,535 even though it
shows 100,000.

Yes, this can cause great confusion for the viewer. Yes, a Paste
Special|Values can create computational errors. But what are the odds?
How often do your spreadsheets actually total exactly 65,535? Do you
think a cursory glance would alert you that all of your random
multiplications magically came up to exactly 100,000 and you might
want to investigate? When you open the same spreadsheet in 2003, the
error disappears, so it doesn't really change the value.

It is a minor easter egg easily fixed and serves only as temporary
fodder for the Mac heads.

Just my $.02
 
Guess this bug was introduced recently.

I use Excel 2002 (10.6823.6626) at work and it shows the answers
correctly as 65535.

-Sharath
Click to expand...

Hello,

I just heard about this bug, which release or version of Excel this
concerns?? I have Excel 2000, and I do not seem to have this bug.
-BB
 
Back
Top