While some members of the Download Squad team may be math whizzes, some are lucky if they can find the Windows calculator. But once it's up and running, we're pretty sure that when you multiply 850 by 77.1, the answer should be 65,535. But for some reason when you perform the same calculation using Microsoft Excel 2007, you get 100,000.Now, 100,000 is a nice round number. Generally speaking, we like it. But in this particular instance it's wrong. And that's not something we like to see in a spreadsheet application. As it turns out, any time you enter an equation that should result in 65,535, you'll get 100,000.
Microsoft has been made aware of the problem and is developing a fix. But it's a bit surprising that the software's been on the shelves as long as it has been without anyone discovering this flaw. Anyone find any other calculations that come up wrong?
[via AppScout]
Reader Comments (Page 1 of 1)
9-26-2007 @ 7:26AM
DigitalOldschool said...
@brc re:rows
Even still, if fixed, you gotta think that 65535 is some sort of number Microsoft can't deal with...be it rows in the past or calculations in the present...
Reply
9-26-2007 @ 8:31AM
lobrien said...
Amazing: More than 10,000 numbers in the range 2:65535 fail! The number 65,535 is very significant as it's 2^16 - 1, but I see no obvious pattern in the numbers that fail.
Reply
9-26-2007 @ 8:34AM
CMcAnt said...
As lobrien noted, there is special significance to 65,535 in computing. See http://en.wikipedia.org/wiki/65535_(number) , but I don't see the link relative the Excel problem. Go figure.
Reply
9-26-2007 @ 8:36AM
Sakesaru said...
Works 100% fine for me. Sounds like more Weblogs Inc bitching and moaning about problems that don't actually exist.
Version 11.8120.8122.
It's not automatically updated at all, and is probably quite old.
Reply
9-26-2007 @ 8:36AM
brc said...
Well in Excel 2007 it doesn't work:
=850*77.1 -> 100000
=850*77+850*0.1 -> 65535
As for the number of rows? The .xlsx of 2007 can do a lot more, it is a restriction of the old .xls file format that stopped at 65535 rows.
Reply
9-26-2007 @ 8:36AM
rosch said...
The calculation is NOT wrong! Excel only shows the wrong numbers. Internally, it calculates correctly.
Example: Multiply 850 by 77.1 Excel shows 100000 as a result. Now subtract 100000 and look what happens.
The result is -34465, which is correct.
Reply
9-26-2007 @ 8:37AM
Vitor Pereira said...
It's working fine for me (using Excel 2003).
I suspect it may be related to the decimal symbol being used, my language (Portuguese) uses comma as the decimal symbol, so:
=850*77,1=65535
Reply
9-26-2007 @ 8:37AM
Billy Bob said...
True, but if you add 1, then it calculates/displays 100001!
If you add 2, then it displays correctly 65537.
"Veerrry interesting... but very stupid!"
Reply
9-26-2007 @ 8:37AM
Billy Bob said...
True, but if you add 1, then it calculates/displays 100001!
If you add 2, then it displays correctly 65537.
"Veerrry interesting... but very stupid!"
Reply
9-26-2007 @ 8:37AM
DigitalOldschool said...
What's even more bizarre is that it is not possible for Microsoft Excel to go beyond 65,536 rows in a
single worksheet.
Coincidence? I think not. :P
Reply
9-26-2007 @ 9:59AM
frink02 said...
The 65,536 row limit has been fixed in Excel 2007 and supposedly supports up to 1,000.000 rows. Sounds like something went a little haywire to allow it.
Reply
9-26-2007 @ 10:41AM
Daniel said...
Regardless whether Excel really calculates it correctly its still wrong. I mean thats like giving to your Fifth Grade Math teacher a test with all questions answered with wrong numbers but the work you show is correct. No matter what you'll still failed because you had the wrong answer lol. I dont think the members are bitchin about it, there just bringing up to Microsoft's attention a problem with their software to help improve it thats all.
- and yes my 2007 Excel has this problem as well. including the +1 that BillyBob found above.
Reply
9-26-2007 @ 8:19PM
OldProgrammer said...
There is a pattern in the number combinations that show the flaw! The pairs listed above are always a combination of a periodic number (which would need an infinite number of digits for exact representation) and a non-periodic number (when converted to the binary number system). That means that Excel 2007 has to "struggle" quite a bit to let the result of such a multiplication look nice and clean as we decimal calculating beings expect it to. The really interesting (and sad) part of the story is that this problem of displaying "clean" numbers (although the internal binary result is slightly off) is well known to real programmers and is solved in every simple calculator - but now popped up again - probably due to some "high-level", "nice and fancy" crap that someone designed into the software. That's the junk we are going to have to expect nowadays. The original intention was to prevent that the "user" has to look at a result like 65534.9999999 - but now the compensation he gets is not to "tune" it to 65535.0 (which is nice) but to 100000.0 - which means there is some strange software design (way off from reality) in there.
Reply
10-01-2007 @ 6:00PM
Donald said...
In before =rand(200,99) in Word
Reply
10-15-2007 @ 8:42AM
Guy0307 said...
there is a bugfix
http://download.microsoft.com/download/6/1/3/61343075-aa12-4152-a761-fccc16d6cef4/office-kb943075-fullfile-x86-glb.exe
Reply
11-05-2007 @ 3:58PM
Jon Woodland said...
Here's one for you. In Excel 2003, when I try to SUM or add these numbers in this sequence only, it is wrong. The numbers are:
-18768.75
1313.81
18768.75
489.13
-1313.81
-489.13
Which should add to 0 but adds to 1.47793E-12. If you change the order in which they are added, they come out to 0. How does that work?
Reply
11-13-2007 @ 10:34PM
Alexandre Jourdain said...
For those who didnt notice 65 535 is the exact number of the LAST row in Excel 2003
coincidence ??
Reply