Bug in Excel 2007

[Bill_Landrum]

Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!

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

 

[JoelKatz]

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?

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

 

[Marcus Schöneborn]

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)

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.

 

[Wondering]

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.

 

[adam.vero]

Since we have seen a lot of theories,
here is some condensed description.

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/

 

[Erich Neuwirth]

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

 

[Hans Terkelsen]

Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!

Molham, how on earth did you come across this bug???
It needs human eyes to spot it, I think.
Congratulations, Hans.

 

[pr]

Are you saying excel doesn't use the FP processor? How then did the previous
intel floating point bug appear in excel?

Michael

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]

 

[carlos r. hernandez]

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

 

[randy.carson]

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

 

[rmabid]

Guess this bug was introduced recently.

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

-Sharath

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

 

[Wondering]

You responded to the subject: Bug in Excel 2007. Which version do you think
the bug is in?

 

[Dana DeLouis]

...It doesn't affect any downline calculations.

How often do your spreadsheets total exactly 65,535?

According to the Microsoft article, it's also 65536. There may be more.
Also, as others have pointed out, it does affect calculations down line.
Round is the most obvious one.

According to Microsoft, equations like the following will produce 100001
instead of 65536.

=ROUND(PRODUCT(3, 315881, 6915684493)/(2^11*5^11),2)

 

[stereoroid]

One freaky thing I've noted is that the rendering error seems to
"propagate". Try this:

A1: =850*77.1 'or another noted combination
A2: =A1*2
A3: =A2/2
A4: =A3/2
A5: =A4*2
A6: =A5*2
A7: =A6/2
A8: =A7/2

The results:
100000
131070
100000
32767.5
100000
131070
100000
32767.5
100000

i.e. the problem in rendering A1 affects the other cells. Change how
A1 is calculated [ say =2^16-1 ] and it goes away.

Now where did I put that copy of Lotus 1-2-3 v2.01?

brian t - dublin

 

[Wondering]

2^16-1 results in exactly 65535.0 whereas 850*77.1 results in approximately
65534.99999999995.
Your calculations are just resulting in the values that are not being
displayed properly that MS has acknowledged:
http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx
There is no propagation problem.

 

[joao.amadora]

First sorry for my bad english.

I don't know if you had noticed that the number it gives error is the
same number it of lines that excel 2003 have (65536).

João Teixeira
Best regards for all

 

[vsoler]

Are you saying excel doesn't use the FP processor? How then did the previous
intel floating point bug appear in excel?

Michael

It looks to me that we are not confronted with a calculating bug BUT
with a formatting one !!!
This explains why =A1-1 =A1*2 and MAX formula behave correctly.
For =A1 and A1+1, simply the formatting routine is not well written.

Saludos

 

[Bob I]

Pure Coincidence

 

[Michael C]

First sorry for my bad english.

I don't know if you had noticed that the number it gives error is the
same number it of lines that excel 2003 have (65536).

You're only about the 50th person to point this out.


João Teixeira
Best regards for all

 

[adam.vero]

It looks to me that we are not confronted with a calculatingbugBUT
with a formatting one !!!
This explains why =A1-1 =A1*2 and MAX formula behave correctly.
For =A1 and A1+1, simply the formatting routine is not well written.

Saludos

While it is mainly a formatting issue, there are several functions
which can cause the error to become fixed or 'locked in'. These
include simple things like ROUND, ABS or TEXT as well as actions like
Paste > Values and exporting to CSV.
You can read more about this here, as well as see plenty of examples
of number pairs which generate the error - certainly enough to make it
clear that these could easily come up in someone's spreadsheet:
http://veroblog.wordpress.com/2007/...hows-wrong-answers-to-simple-multiplications/