Filed under: Office, Microsoft
Excel 2007 can't do math (unless 850 * 77.1 = 100,000)
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]
I don't know if this is a labor of love or merely the brainchild of four very gifted games designers, but Level Up is a really weird mash-up of gaming elements that you have probably never seen in a Flash game before.
Let's start with the premise itself: Groundhog Day meets Memento. The game experience revolves around 'days': you explore the world and the clock slowly ticks towards the evening. You bounce around picking up gems and talking to the denizens of 'Level Upland'. Eventually you feel tired and head back to ...
Reader Comments (Page 1 of 1)
lobrien said 8:31AM on 9-26-2007
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
DigitalOldschool said 8:37AM on 9-26-2007
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
CMcAnt said 8:34AM on 9-26-2007
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
Sakesaru said 8:36AM on 9-26-2007
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
brc said 8:36AM on 9-26-2007
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
rosch said 8:36AM on 9-26-2007
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
Vitor Pereira said 8:37AM on 9-26-2007
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
Billy Bob said 8:37AM on 9-26-2007
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
Billy Bob said 8:37AM on 9-26-2007
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
DigitalOldschool said 7:26AM on 9-26-2007
@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
frink02 said 9:59AM on 9-26-2007
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
Daniel said 10:41AM on 9-26-2007
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
OldProgrammer said 8:19PM on 9-26-2007
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
Donald said 6:00PM on 10-01-2007
In before =rand(200,99) in Word
Reply
Guy0307 said 8:42AM on 10-15-2007
there is a bugfix
http://download.microsoft.com/download/6/1/3/61343075-aa12-4152-a761-fccc16d6cef4/office-kb943075-fullfile-x86-glb.exe
Reply
Jon Woodland said 3:58PM on 11-05-2007
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
Alexandre Jourdain said 10:34PM on 11-13-2007
For those who didnt notice 65 535 is the exact number of the LAST row in Excel 2003
coincidence ??
Reply