Excel 2007 can't do math (unless 850 * 77.1 = 100,000)

by Brad Linder Sep 25th 2007

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]

Tags: bug, excel, excel-2007, multiplication

Reader Comments (Page 1 of 1)

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...

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.

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.

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.

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.

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.

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

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!"

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!"

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

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.

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.

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.

Donald said 6:00PM on 10-01-2007

In before =rand(200,99) in Word

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

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?

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 ??

Featured Time Waster

Civiballs is a beautiful, soothing physics puzzle Time Waster

I have an absolute weakness for physics games, and while Civiballs isn't the strongest physics-based game, what it lacks in the physics department it makes up for a few times over in style and fun.

In Civiballs, you are presented with a few colored balls, and your goal is to get those balls into the same-colored urn on the level. The "civi" part of Civiballs is that there are 3 sets of levels to play, each representing a different civilization. While the civilization doesn't affect gameplay, the artwork for each level is beautifully themed to it's appropriate era.

To play the game, you are given only one tool - a sword with which to cut the chains that are holding the balls. The puzzle part of the game is in figuring out what order, and with what timing to cut each chain. Do it right, and all the right balls end up in the right urns, with no stray balls entering an urn (a no-no). Do it wrong, and you get to start over again.

Civiballs is not terribly deep on gameplay; the entire game can be completed in about 15 minutes. But if you enjoy this type of game, it will be a very enjoyable 15 minutes.

View more Time Wasters