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? -- Regards from Virginia Beach,
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! -- Dana DeLouis
"Molham Serry" <mse...@contact.com.eg> wrote in message
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...
> 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...
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.
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!
On Sep 22, 11:44 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> 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? > -- > Regards from Virginia Beach,
> > "Molham Serry" <mse...@contact.com.eg> wrote in message > >news:834DAE15-EE9C-4996-A63B-2C443F09F604@microsoft.com... > >> Simply when you try to multiply 850 by 77.1 excel display the result to be 100000 !!!- Hide quoted text -
> 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!
> On Sep 22, 11:44 pm, "Earl Kiosterud" <some...@nowhere.com> wrote: >> 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? >> -- >> Regards from Virginia Beach,
>> > "Molham Serry" <mse...@contact.com.eg> wrote in message >> >news:834DAE15-EE9C-4996-A63B-2C443F09F604@microsoft.com... >> >> Simply when you try to multiply 850 by 77.1 excel display the result >> >> to be 100000 !!!- Hide quoted text -
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>
-- Dana DeLouis
"Marcus Schöneborn" <divZ...@googlemail.com> wrote in message
> 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.
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? -- alan
"Dana DeLouis" <ddelo...@bellsouth.net> wrote in message
> 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 > ...
>> 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.
"Dana DeLouis" <ddelo...@bellsouth.net> wrote... >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.
"Dana DeLouis" <ddelo...@bellsouth.net> wrote... >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.
> > Simply when you try to multiply 850 by 77.1 excel display the result to be > > 100000 !!!
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.
This is simple rounding error. Down at the hardware level of PCs, when working with floating point numbers, you are only guaranteed a number of significant digits (varies depending on the size of the data type). In this case, your numbers are good up to 7 digits (NNNNN.NN), which fits with the double data type in C.
On Sep 23, 9:25 am, "Dana DeLouis" <ddelo...@bellsouth.net> wrote:
> This is simple rounding error. Down at the hardware level of PCs, > when working with floating point numbers, you are only guaranteed a > number of significant digits (varies depending on the size of the data > type). In this case, your numbers are good up to 7 digits (NNNNN.NN), > which fits with the double data type in C.
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. -- MB
> 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?
Bugs are usually found on boundaries. In this case 65535 is the smallest number that fits into 16bits and 65536 requires 32 bit. Although excel will use floating point for the numbers so it might not apply in this case there is likely some significance.
> 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.
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.
> On Sep 24, 10:11 pm, mega...@gmail.com wrote: >> This is simple rounding error. Down at the hardware level of PCs, >> when working with floating point numbers, you are only guaranteed a >> number of significant digits (varies depending on the size of the data >> type). In this case, your numbers are good up to 7 digits (NNNNN.NN), >> which fits with the double data type in C.
> 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.
Are you saying excel doesn't use the FP processor? How then did the previous intel floating point bug appear in excel?
|
