Would you know a bottom if you saw one? - Kevin Delaney, WSJ
|
|
|
Excel goes nuts at 65,535
A THREAD on Google Group microsoft.public.excel reveals that Excel 2007 loses its grip with arithmetic that involves the number 65,535.
Several examples are shown, perhaps the simplest of which is the calculation ( 850 X 77.1 ), which should produce 65,535 but instead returns 100,000.
There's all sorts of speculation as to how this bug occurred, postulating floating-point and rounding errors and the like, but it seems much more likely that some Excel developer simply punted at some point and the Vole's stringent quality control (cough) never caught it.
Some might recall that mathematical errors have been discovered in Excel periodically in various releases going back at least as far as Excel 5.
Microsoft people appear to have been involved in the discussion and confirmed the bug. ยต
L'INQ
Google
Groups
Tags: Microsoft
Share this:
Share
@Raj:
Actually, in Excel 2007, rows end at 1,048,576.
This is a freshman year programming mistake. Is quality control so lacking at Microsoft these days that they don't even test for overflows?
The article is talking about Excel 2007 you numpty.
Wao, this will be the best bug, hopefully they can fix it in SP1. I have try multiply 850 by 77.1 in Excel 2007 + Autopathcer Office 2007 August 2007 and still got the 100.000.
If you read the article carefully, they are talking about Excel 2007
Excel rows end on the very next number 65536 - wonder if thats got something to do with it
It seems Excel 2007 also can't calculate 840*78.0178571428571428571428571428571 but correctly reports 65535 for 840*78.017857142857142857142857142857 (dropping the last decimal). Evidently the bug is smart enough to affect only calculations that produce "exactly" 65535.
when you calculate 850 * 77.1 and then from the result subtract 2 you get what looks like...
1000000 - 2 = 65533...
"65535 is a frequently occurring number in the field of computing because it is the highest number which can be represented by an unsigned 16 bit binary number."

http://en.wikipedia.org/wiki/65535_(number)
And if you multiply 77.1*850 you get 5052748.5. Put 850 in on cell (A) and 77.1 in another (B). Try A*B and B*A.
My Excel 2003 has 850*77.1 equaling 65,535. Might already be patched?!?!