Bug in Excel 2007

[metalgore32]

... the correct result is 100000

^^ Redacted, it's been a long day ignore me

 

[cjeganmohan]

Surprisigly when i try these calculations it seems to work for me
correct!!

 

[Legendary Pirate]

Many of our accountants use Excel.... lol

 

[billythefisherman]

I'd be EXTREMELY suprised if not one person or even only a few on the excel
team had experience with assembler. Remember all these programmers would be
using C which has all the power of assembler with the ease of use of
assembler.

Michael

Sorry but I find it hard to believe the Excel code team are a bunch of
monkeys which it seems your implying - I'd imagine they're amongst the
finest programmers around. This I'm afraid is a QA failure, it should
never have got out the door with what is obviously a simple mistake
that even the best can make. In any case you certainly don't need an
understanding of assembler to have a guess at what this is related
to...

 

[Matthias Klaey]

This is actually a very old bug in windows. If you type this into MS
Calculator it gives you the same result. I am not exactly sure why
this is but it has existed since Windows 95/98.

Hmmm . no I am sorry, my MS Calculator gives the correct answer (Win
XP Pro SP2 fully patched)
greetings
Matthias Kläy

 

[webprof]

Hi,

try this, it is amazing:

A1: 65535

Fill B1:B31500 (or as many rows you like) range with numbers 1, 2,
3, ... 31500

C1: =$a$1/b1
D1: =b1*c1

Highlight and drag c1:d1 down till line 31500 (or whatever numbers you
filled in column B).

There are lots of 100.000s in column D.

J.

 

[dougaj4]

If any formula that yields the incorrect 100000 value is formatted as
a date it shows 4 June 2079, which is the correct date for day number
65535!

 

[Erich Neuwirth]

I have read the thread.
I just wanted to add evidence that at the core of the problem is a
rendering issue. This is NOT in contradiction that the error sometimes
propagates. If it were a problem of the computational engine only,
it would almost always propagate, which it does not.
Some more indication:
I used my RExcel addin which allows to transfer values from Excel to R.
Transferring the cell containing =850*77.1 result int the correct value
of 65535 in R.

850*77.1+1, by the way, displays as 100001, but
850*77.1+2 displays as 65537

Another funny thing
INT(850*77.1) produces 65535
ROUND(850*77.1) produces 100000, and this 100000 is the "real" value,
it gets transferred to R as 10000 and it propagates to other formulas.

While checking this I also found another strange thing.
MOD(850*77.1,1) produces -7.27596E-12
which it should not because the sign of the result of MOD should always
be the same as the sign of the divisor (the second argument).
Repeatedly subtracting 1 from this number and computing MOD(...,1) for
all these numbers produces -7.27596E-12 until the value produced byt the
subtraction becomes 10000, then MOD produces 1, which it should not
because the result of MOD(...,1) should always be less then one.
INT of these ones, by the way, is 0. Setting the precision for the cell
containing the 1 to 14 places shows that the value is 0.99999999999272
and that explains the 1 as a rounded value. Of course the question here
is why MOD(850*77.1-55534,1) is slightly smaller than 0 (which it should
not be) and MOD(850*77.1-55535,1) is slightly smaller than 1, which at
least plays according to the rules of MOD.


Subtracting 1 repeatedly even further starting at -10000 produces a MOD
value of -7.27596E-12 again.

So the whole issue is quite complicated.

The whole problem of course is as bad as it can get.
A spreadsheet which seemingly even cannot multiply
correctly. Nevertheless, it is probably useful to track down
where the problem might be rooted.

Some further thoughts:
ROUND produces an error which propagates. So if the display mechanism
uses ROUND internally and ROUND is buggy, this might be an explanation
of the behavior we observe.

 

[grant]

The following error is apparent in Excel 97, 02, and 07.

A1=120.05
B1=120.04
C1=A1-B1 (returns 0.01)
D1=IF(C1=0.01,"Yes","No") (returns "No")

The numerical result is actually
0.009999999999990910.

The error occurs with 120.10-120.01, and so on.

So if you bought £million of bonds, for example, at 120.05 and needed
to determine whether any were down by 0.01 or 0.02, etc, you would be
happy (but wrong, and poorer).

Grant.

 

[relaxrelaxx]

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

One of the Mathematica kernel programmers has a response to this on
the Wolfram Blog:
http://blog.wolfram.com/2007/09/arithmetic_is_hardto_get_right.html

 

[Michael C]

Sorry but I find it hard to believe the Excel code team are a bunch of
monkeys which it seems your implying

I suggest you read my post again because this is certainly NOT what I am
implying. In fact I was refuting this.

- I'd imagine they're amongst the
finest programmers around.

There'd be a range of skills I imagine from genius to very good.

Michael

 

[DMLanger]

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

gates should have stayed in college...

 

[Michael C]

hhmm.... wat suprises me is that the maximum quantity of rows in Excel
2003 is 65.536.
Is it just a case of coincidence or......????

It's no coincidence but it's not related. 65535 (or 65536 if you start at 1)
is the largest number that will fit into 16 bits. The number of rows in
excel will be 65536 so they can store the row number in 16 bits and save
some storage space. All sorts of bugs will occur at 65535, for example some
programs crash if a listbox contains more than 65536 items.

Michael

 

[Michael C]

Any correlation to the fact that there are only 65536 lines rows
allowed in older versions of Excel? Maybe the bug is in the row
sequencing logic...

No, see my other response.

 

[Erich Neuwirth]

If you add a column E with
E1: =MOD(D1,1)

column E will display only 3 different values,
7.3E-12
-7.28E-12
0

and the wrong 10000 will occur exactly when this value is
-7.28E-12
which, as I said in another posting, should not occur anyhow
since MOD(x,1) always should return nonnegative values.
It turns out that this is 2^(-37), so again we hit a power of two.
It occurs in all rows with a divisor of the form
13*2^n
(but also in other ones) and it seems to be true that
if it occurs in row x it also occurs in all rows x*2^n.


In all these columns ROUND(D_) will produce 10000, and this will be the
"real" value as you can see inf you reference the value in another formula.

 

[norinaka]

Not being much of an excel user, forgive me if this is completely not
related..

However, do you think that there is any relationship between this
topic and the following:

A1: enter 0.85
A2: enter 0.8
A3: type =A1*A2
A4: type =A3*100
A5: type =int(A4)

Why is the result 4? I would think that it would be 5..?

Thanks,
Jeff

 

[norinaka]

Not being much of an excel user, forgive me if this is completely not
related..

However, do you think that there is any relationship between this
topic and the following:

A1: enter 0.85
A2: enter 0.8
A3: type =A1*A2
A4: type =A3*100
A5: type =int(A4)

Why is the result 4? I would think that it would be 5..?

Thanks,
Jeff

EDIT: Sorry, I was just being stupid.. I took a closer look at a
description of the INT function and it does say that it will round
DOWN. So when I add more decimal spaces A4 now displays 4.999999.. so
I guess it works as it's supposed to. Sorry for fudgeup.. >_<

 

[Michael C]

EDIT: Sorry, I was just being stupid.. I took a closer look at a
description of the INT function and it does say that it will round
DOWN. So when I add more decimal spaces A4 now displays 4.999999.. so
I guess it works as it's supposed to. Sorry for fudgeup.. >_<

I presume you meant to put A1 - A2 in the third cell

What you're experiencing there isn't necessarily a bug, it's more a
limitation of floating point arithmatic and is a VERY common issue with
computers in general. Because of the way numbers are stored they are not
always stored exactly. Do a google search on floating point errors if you're
interested, there isn't much point in my rewriting everything. I should add
the IMO it is a bug and they probably should have used a format that doesn't
have this problem.

 

[Harlan Grove]

There'd be a range of skills I imagine from genius to very good.

Yes, but given the likely age distribution and what that would imply
about the likely CS courses available while they were still students,
their assembler skills and bit manipulation experience likely runs
from modest to nonexistent.

Being an excellent C++ programmer is NOT ipso facto proof of expertise
in machine language programming, especially not with

 

[Michael C]

Yes, but given the likely age distribution and what that would imply
about the likely CS courses available while they were still students,
their assembler skills and bit manipulation experience likely runs
from modest to nonexistent.

I totally disagree with that. While I'm sure that there are some programmers
on the Office team that know little about assembler I'm certain there would
be more than enough who have excellent knowledge. I work in high level
languages such as C# and VB6 and 50% of the people I work with know
assembler reasonably well (i've written my own 32 bit integer maths routines
for an 8 bit processor in assembler from scratch). Considering the assembler
knowledge here I would be absolutely amazed if that knowledge was lacking in
this particular MS project. This is one of the largest companies on earth
and this is one of their major products. Do you really think a multi-billion
dollar project is going to be lacking in expertise?

Being an excellent C++ programmer is NOT ipso facto proof of expertise
in machine language programming, especially not with

I notice you put the words "bit manipulation" in your first statement but
missed it from this one. Naturally while C++ does not mean the programmer
will have any knowledge of assembler it does generally require the
programmer to have good knowledge of bit manipulation.