M
Molham Serry
Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!
100000 !!!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bernard Liengme
Confirmed!
best wishes
best wishes
D
Dana DeLouis
Wow! How did you find this major bug?
Even Sumproduct returned 100000.
=SUMPRODUCT(850,77.1)
=SUMPRODUCT(850,77.1,2,0.5)
(2*0.5 = 1)
This seems Major to me!
Even Sumproduct returned 100000.
=SUMPRODUCT(850,77.1)
=SUMPRODUCT(850,77.1,2,0.5)
(2*0.5 = 1)
This seems Major to me!
W
Wild Bill
Bump - this sounds alarmingly serious. Maybe someone seeing this is
plugged in enough to call & get someone out of their bed or their bong.
plugged in enough to call & get someone out of their bed or their bong.
E
Earl Kiosterud
Sheesh! The correct answer, 65,535, happens to have two bytes worth of 1 digits in binary.
I wonder what that might have to do with it. I haven't installed 2007 yet (prescient?), so
I wonder if anyone tried other numbers that should produce the same answer, like 4 *
16383.75, or 222 * 295.2027?
I wonder what that might have to do with it. I haven't installed 2007 yet (prescient?), so
I wonder if anyone tried other numbers that should produce the same answer, like 4 *
16383.75, or 222 * 295.2027?
B
Bernard Liengme
This has now been reported to Microsoft
D
Dana DeLouis
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
What's even stranger is this: Suppose the formula is in A1.
=A1+1 returns 100001, which appears to show the formula is in fact 100000
and a very Serious problem.
And if you multiply be say, 2 you get something else:
=A1*2
returns 131070, as if A1 had 65535. (which it should have been)
=A1*1
Keeps it at 100000.
=A1-1 returns 65534
=A1/1 is still 100000
=A1/2 retuns 32767.5
Using MAX() on a range appears not to see 100000.
Very Serious!
=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
What's even stranger is this: Suppose the formula is in A1.
=A1+1 returns 100001, which appears to show the formula is in fact 100000
and a very Serious problem.
And if you multiply be say, 2 you get something else:
=A1*2
returns 131070, as if A1 had 65535. (which it should have been)
=A1*1
Keeps it at 100000.
=A1-1 returns 65534
=A1/1 is still 100000
=A1/2 retuns 32767.5
Using MAX() on a range appears not to see 100000.
Very Serious!
D
Dana DeLouis
I also see that Excel 2007 still has this over 10+ year bug still in it:
=40000.223
=41000.348
=52000.723
Such numbers are converted to:
=40000.2229999999
=41000.3479999999
=52000.7229999999
=40000.223
=41000.348
=52000.723
Such numbers are converted to:
=40000.2229999999
=41000.3479999999
=52000.7229999999
E
EpsilonRho
Excel 2000 gives the correct answer.
Gene
Gene
M
Marcus =?iso-8859-15?Q?Sch=F6neborn?=
»Dana DeLouis« said:I also see that Excel 2007 still has this over 10+ year bug still in it:
=40000.223
=41000.348
=52000.723
Such numbers are converted to:
=40000.2229999999
=41000.3479999999
=52000.7229999999
That isn't a bug, but well-known floating point representation
inaccuracy. FPUs don't work in decimal.
The question just is whether Excel should work with a FPU's floating
point type, or make its own and calculate "exactly" in it. The latter
would be much slower, so it's doubtful if it is really worth the major
slowdown this would cause.
OTOH, Windows Calculator did get changed away from FPU's own floating
point types to its own arbitrary precision type. But it's only
calculating one cell at once...
D
Dana DeLouis
That isn't a bug, but well-known floating point representation
Hi. I believe Microsoft still considers it a "Problem." (ie Bug)
It affects numbers between 2^15 - 2^16 (32768 - 65536) that end with:
{.098, .223, .348, .473, .598, .723, .848, .973}
(Note: the endings are offset by 1/8)
Hi. I believe Microsoft still considers it a "Problem." (ie Bug)
It affects numbers between 2^15 - 2^16 (32768 - 65536) that end with:
{.098, .223, .348, .473, .598, .723, .848, .973}
(Note: the endings are offset by 1/8)
M
Marcus =?iso-8859-15?Q?Sch=F6neborn?=
»Dana DeLouis« said:Hi. I believe Microsoft still considers it a "Problem." (ie Bug)
It affects numbers between 2^15 - 2^16 (32768 - 65536) that end with:
{.098, .223, .348, .473, .598, .723, .848, .973}
(Note: the endings are offset by 1/8)
If they really get turned into
40000.2229999999
it is an actual bug because:
as float: 40000.22265625000000000000
as double: 40000.22299999999813735485
as long double: 40000.22299999999999897682
Excel: 40000.2229999999 (rounded down)
so this would be LESS precision than IEEE doubles, or somewhat broken
rounding.
If there is an additional 98, it's no bug and a consequence of IEEE
doubles.
I
ilia
Some more observations.
None of the other n-byte integers of all 1s have this issue.
A1 =((2^16-1)/425)*(850/2)
B1 = 65,535
C1 =(A1=B1)
C1 displays TRUE.
In VBE, typing Debug.Print ActiveCell.Value yields the correct
result. Likewise, the Application.Evaluate() method yields the
correct result.
This code (in worksheet's code module):
Public Sub testBug()
Me.Range("D5").Value = 850
Me.Range("D6").Value = 77.1
Debug.Print _
Application.WorksheetFunction.SumProduct(Me.Range("D5"),
Me.Range("D6"))
End Sub
Also displays 65,535.
Curious bug indeed!
None of the other n-byte integers of all 1s have this issue.
A1 =((2^16-1)/425)*(850/2)
B1 = 65,535
C1 =(A1=B1)
C1 displays TRUE.
In VBE, typing Debug.Print ActiveCell.Value yields the correct
result. Likewise, the Application.Evaluate() method yields the
correct result.
This code (in worksheet's code module):
Public Sub testBug()
Me.Range("D5").Value = 850
Me.Range("D6").Value = 77.1
Debug.Print _
Application.WorksheetFunction.SumProduct(Me.Range("D5"),
Me.Range("D6"))
End Sub
Also displays 65,535.
Curious bug indeed!
D
Dana DeLouis
Hi. Just to add, code that joins both text and values could be affected by
this bug.
Sub Bug()
Dim x
[A1].Formula = "=77.1*850"
x = Range("A1").Value * 2
x = Range("A1").Value2 * 2
'// This is 200000 !!!
x = Range("A1").Text * 2
End Sub
Even numbers like this display this issue:
=SUMPRODUCT(0.41,65535/0.41)
this bug.
Sub Bug()
Dim x
[A1].Formula = "=77.1*850"
x = Range("A1").Value * 2
x = Range("A1").Value2 * 2
'// This is 200000 !!!
x = Range("A1").Text * 2
End Sub
Even numbers like this display this issue:
=SUMPRODUCT(0.41,65535/0.41)
D
Dana DeLouis
Hi. Took me a while to find it.
Numbers Ending in .848 Appear Incorrectly
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161234
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem ...
....Apparently since Microsoft Excel 5.0 <vbg>
Numbers Ending in .848 Appear Incorrectly
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161234
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem ...
....Apparently since Microsoft Excel 5.0 <vbg>
E
Earl Kiosterud
Gene,
So does 2003. It appears to be a 2007 issue.
So does 2003. It appears to be a 2007 issue.
A
*alan*
It may be an insignificant coincidence, but I found it interesting that in
the article mentioned below, it's stated that the incorrect representation
of numbers ending in .848 occurs between "32,768 and 65,535". Isn't 65,535
the product that's getting misrepresented as 100000 when you calculate
850*77.1 in Excel2007?
Perhaps they were finally attempting to address the previous bug, and just
mucked it up even more?
the article mentioned below, it's stated that the incorrect representation
of numbers ending in .848 occurs between "32,768 and 65,535". Isn't 65,535
the product that's getting misrepresented as 100000 when you calculate
850*77.1 in Excel2007?
Perhaps they were finally attempting to address the previous bug, and just
mucked it up even more?
H
Harlan Grove
....Dana DeLouis said:There appears to be more of them. For example:
Makes it appear this is a rendering issue.
....What's even stranger is this: Suppose the formula is in A1.
=A1+1 returns 100001, which appears to show the formula is in fact
100000
and a very Serious problem.
And if you multiply be say, 2 you get something else:
=A1*2
returns 131070, as if A1 had 65535. (which it should have been) ....
=A1-1 returns 65534
Almost makes it appear some calculations use the .Text property rather
than the .Value property of A1. If so, definitely a bug.
Amusing, though
H
Harlan Grove
@#$% laptop's @#$% mousepad!
Makes it appear this is a rendering issue.
Almost makes it appear some calculations use the .Text property rather
than the .Value property of A1. If so, definitely a bug.
Amusing, though, that A1+1 would be odd while A1-1 would be even.
Actually, I'd guess someone was trying to rewrite the 15 decimal
digits truncation code but screwed up, perhaps missing a bitwise AND
or XOR. I wonder how many programmers on the Excel team have any
experience with assembler? I'd guess not many, if any.
Meaning that if A1 contained this false 100000 and B1 contained 5,
MAX(A1:B1) returns 5? Or A2 contained 70000 and MAX(A1:A2) returns
70000?
Definitely. How soon do you suppose they'll fix this? We should start
a pool. I'll take not until SP2, which, based on SP1's release date
(not yet), I'd guess would be mid-Fall 2008.
....Dana DeLouis said:There appears to be more of them. For example:
Makes it appear this is a rendering issue.
....What's even stranger is this: Suppose the formula is in A1.
=A1+1 returns 100001, which appears to show the formula is in fact
100000
and a very Serious problem.
And if you multiply be say, 2 you get something else:
=A1*2
returns 131070, as if A1 had 65535. (which it should have been) ....
=A1-1 returns 65534
Almost makes it appear some calculations use the .Text property rather
than the .Value property of A1. If so, definitely a bug.
Amusing, though, that A1+1 would be odd while A1-1 would be even.
Actually, I'd guess someone was trying to rewrite the 15 decimal
digits truncation code but screwed up, perhaps missing a bitwise AND
or XOR. I wonder how many programmers on the Excel team have any
experience with assembler? I'd guess not many, if any.
Using MAX() on a range appears not to see 100000.
Meaning that if A1 contained this false 100000 and B1 contained 5,
MAX(A1:B1) returns 5? Or A2 contained 70000 and MAX(A1:A2) returns
70000?
Very Serious!
Definitely. How soon do you suppose they'll fix this? We should start
a pool. I'll take not until SP2, which, based on SP1's release date
(not yet), I'd guess would be mid-Fall 2008.
W
wilsontp
I also see that Excel 2007 still has this over 10+ year bug still in it:
=40000.223
=41000.348
=52000.723
Such numbers are converted to:
=40000.2229999999
=41000.3479999999
=52000.7229999999
FP "bugs" abound. I wonder why Excel doesn't use BCD for its
calculations? Since the primary use of Excel is to calculate money,
and money is decimal....
oh wait. I just answered my own question. Rounding to 2 decimal places
generally eliminates that problem.