Bug in Excel 2007

  • Thread starter Thread starter Molham Serry
  • Start date Start date
Here is another hint that it is probably a rendering problem.
exp(ln(anynumber)) returns anynumber (for anynumber > 0)
exp(ln(850*77.1))
returns 65535

If A1 contains =850*77.1,
and B1 contains exp(ln(A1))
then A1 displays 100000
and B1 displays 65535
 
That bug is also present in Excel 3.0 and Excel 4.0. It is NOT present
in Star Office or Open Office. <vbg>
Click to expand...

So, when will Star Office and Open Office be updated to maintain
compatibility with Excel 2007 <grin>?
 
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
Click to expand...

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...
 
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.
Click to expand...


Hmmm . no I am sorry, my MS Calculator gives the correct answer (Win
XP Pro SP2 fully patched)
greetings
Matthias Kläy
 
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.
 
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!
 
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.
 
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.
 
billythefisherman said:
Sorry but I find it hard to believe the Excel code team are a bunch of
monkeys which it seems your implying
Click to expand...

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.
Click to expand...

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

Michael
 
Cactus77 said:
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......????
Click to expand...

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
 
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...
Click to expand...

No, see my other response.
 
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.
 
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
 
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
Click to expand...

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.. >_<
 
Back
Top