Bug in Excel 2007

J

JoelKatz

Here is another hint that it is probably a rendering problem.
Click to expand...

We have conclusive proof it is not a rendering problem. Look at the
example of "A1 + 1" and "A1 - 1". One gives the correct result and one
does not. If it was a rendering issue, they would both give correct
results.

My bet is they have two internal ways of storing the value, one for
values less than or (sometimes) equal to 65,535 and one for values
greater than or (sometimes) equal to 65,535.

That is, they have no consistent rule for which representation to set/
use when the value is equal to 65,535.

DS
 
X

xindomusic

We have conclusive proof it is not a rendering problem. Look at the
example of "A1 + 1" and "A1 - 1". One gives the correct result and one
does not. If it was a rendering issue, they would both give correct
results.

My bet is they have two internal ways of storing the value, one for
values less than or (sometimes) equal to 65,535 and one for values
greater than or (sometimes) equal to 65,535.

That is, they have no consistent rule for which representation to set/
use when the value is equal to 65,535.

DS
Click to expand...

rewriting the formual mathematically seems to get different result.
The problem may be due ot MSF is trying to swich 10 based floating
number to binary more "efficiently".

A1=(=850*77)
A2=(=850*0.1)
A3=(=A1+A2)=65535 This gets the correct number

A1=849*77.1+77=65534.900000
A2=0.1
A3=(=A1+A2)=100000 This is incorrect.

MS must be offseting the number at certain point get better results.
 
D

dougaj4

We have conclusive proof it is not a rendering problem. Look at the
example of "A1 + 1" and "A1 - 1". One gives the correct result and one
does not. If it was a rendering issue, they would both give correct
results.

My bet is they have two internal ways of storing the value, one for
values less than or (sometimes) equal to 65,535 and one for values
greater than or (sometimes) equal to 65,535.

That is, they have no consistent rule for which representation to set/
use when the value is equal to 65,535.

DS
Click to expand...

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?
 
A

artur.nowocin

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

http://
img256.imageshack.us/img256/6360/excelzu5.th.jpg

Funny, but as you look at real value you have 77,1000000000012 instead
od 77,1 where you get good value.

I put 77,1111 in first row then 77,1110 in following to get values
every 0,0001 but step from 77,1106 to 77,1105 is weird becuase I get
77,1105000000001 instead of 77,1105... then 77,1096000000002.
77,1087000000003 and so one...
 
A

artur.nowocin

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

http://
img256.imageshack.us/img256/6360/excelzu5.th.jpg

Funny, but as you look at real value you have 77,1000000000012 instead
od 77,1 where you get good value.

I put 77,1111 in first row then 77,1110 in following to get values
every 0,0001 but step from 77,1106 to 77,1105 is weird becuase I get
77,1105000000001 instead of 77,1105... then 77,1096000000002.
77,1087000000003 and so one...
Click to expand...


[/
URL] funny
 
H

harald schilly

The numerical result is actually
0.009999999999990910.
Click to expand...

year, that's why you need to check inside an epsilon region for
equalness (the = sign should be replaced by a function, which tests
something like x \in [y - machine-epsilon, y + machine-epsilon]
this happens everywhere where you do floating point calculations on a
CPU.

a good and quick solution would be to calculate everything in cents
(i.e. integers!) and no commas at all.
 
W

wsnell01

Oddly, I also multiplied 2 * 10.2 * 3212.5 and got 100000, so it is
not just when you multiply 2 numbers that equal 65535, it is when
certain combinations of number add up to 65535...

Also tried 2 * 10.2 * 642.5 * 5 and it is 100000
Click to expand...

OTOH, when I tried 5 * 10.2 * 642.5 * 2 I got the correct answer,
65535.
 
D

drybittermelon

try this in Excel 2007 to enable the cascading effect of the bug...

A1: =TEXT(850*77.1,"#,##0.00") 10000
A2: =A1+1 10001
A3: =A2+1 10002
A4: =A3+1 10003

May be due to the fact that TEXT function grabs the value of the cell
from its rendered value rather than the value in memory?
 
S

S P Arif Sahari Wibowo

There appears to be more of them. For example:
=5.1*12850
=10.2*6425
=20.4*3212.5
=40.8*1606.25
=77.1*850
=154.2*425
=212.5*308.4
=308.4*212.5
=425*154.2
..etc
Click to expand...

Is this only happen for multiplication and for number with fractions?
 
D

Dana DeLouis

Is this only happen for multiplication and for number with fractions?

According to the microsoft link that (e-mail address removed) posted,
(
http://blogs.msdn.com:80/excel/archive/2007/09/25/calculation-issue-update.aspx )

it has to do with rounding of the 16th bit, which is just outside Excel's
display of 15 digits.
Hence, at the same 16 digit precision, numbers like the following display
this issue:

=POWER(20333622017/79428817,2)

=PRODUCT(103, 229, 647, 11939, 35969)/100000000000

etc...
 
C

cbminfo

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.
 
E

Erich Neuwirth

A1: 850*77.1
A2: A1+1
A3: A2+1

A3 displays 65537

I think this is a counterexample to you theory.
 
M

m.stgeorges

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.
 
E

Erich Neuwirth

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2007 bug 3
Calculation error found in Excel 2007 under XP 1
BUG in 2007 3
Excel 2003 as default instead of Excel 2007 1
850*77.1=100000 ?! (Excel 2007) 4
Excel 2007 Multiplication Bug 4
why result 77.1 * 850 shown 100000 in excel 2007? 4
answer =850*77.1 formula in excel 2007 is 100000 , WHY? 6

Top