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

by Volker Weber

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.

More >

Comments

I've been reading about this since yesterday but only tested it now, it works fine here (using Excel 2003).
Did you try it?

Vitor Pereira, 2007-09-26

I have to say, it is really surprising that this would get through Q&A. This is one of the classic edge cases (what happens when you get results that are just before, at and just beyond 65,535 and each of the other maxed out sizes).

Ben Langhinrichs, 2007-09-26

Vitor, read the headline (or the text) again ;-)

Martin Hiegl, 2007-09-26

Martin, what exactly is you point? I've read it.

Vitor Pereira, 2007-09-26

OK, Excel 2007 only :-)

It appears they were trying to fix one problem and created another one.

Vitor Pereira, 2007-09-26

@Ben: It's not quite that typical overflow. The bug is a lot more obscure. Only multiplying two numbers where at least one is a floating point number causes this. It gets even more obscure: Let's say you have the result in cell A1. If you now enter =A1+1 in A2, A2 will display 100001. However, if you enter =A1*2 in A3, A3 will display 131070. Similarly, =A1/2 will yield 32767,5. =A1*1 will, interestingly, yield 100000... There are more strange behaviors, I I think I saw something about accessing the cell in different ways in VBA yielding different values.

@Vitor: I have tried it in Excel 2007 and for me the bug occurred.

Thomas Lötzer, 2007-09-26

It ist quite understandable, that if you calculate 851*77,1 in cell A1 you get the correct result 64.612,1. If you set B1 to A1-77,1 you'll get - yes you are right ;-): 100.000. The same occurs with 849 and +77,1 ;-)

Quite interesting: saved the file to xls and opened it with numbers. Every thing fine hear, means 65.535.

Frank Mueller, 2007-09-26

@Thomas - Thanks, I see how that is less likely to get caught.

Ben Langhinrichs, 2007-09-26

Out of curiosity, as I don't have Office 2007 here right now, has anybody saved in Open XML and seen whether the two numbers (the value and the representation) differ?

Ben Langhinrichs, 2007-09-26

Perhaps MS can convince the ISO to introduce a new standard for mathematic calculations. ;-)

Jan-Piet Mens, 2007-09-26

maybe they should a quick-link to google

Samuel Orsenne, 2007-09-26

maybe they should revisit whatever they have submitted to the IRS ;-)

Armin Roth, 2007-09-27

@Ben: I have tried it. I am not too familiar with the format, but this is what I found in the worksheet's XML:

<row r="1" spans="1:1">
<c r="A1">
<f>850*77.1</f>
<v>65534.999999999993</v>
</c>
</row>

If there is another place to look, let me know.

Thomas Loetzer, 2007-09-27

Joel Spolsky posted an interesting explanation with some background infos. He says that the bug affects only how the cell is displayed (not any calculations) and only if the number is very close to 65535, not 65535 itself.

Thomas Loetzer, 2007-09-27

@Joel - Yes, it appears to be a "display" issue. Because you can write formulas against the result and they're accurate.

I tried this:

A3 77.1
A4 850
A5 =A3*A4 (result 100000)
A6 =A5*3 (result 196605)

Now everybody, in the voice of Dana Carvey impersonating Johnny Carson, "This is some weird, wild stuff!"

Henry Ferlauto, 2007-09-27

Recent comments

Nina Wittich on Surface Pro X :: Das Gerät macht mir große Freude at 09:43
Lucius Bobikiewicz on Switching to Signal at 06:31
Ragnar Schierholz on Switching to Signal at 19:33
Jens-Christian Fischer on Switching to Signal at 14:23
John Keys on Office 365 im Tagesangebot at 10:20
Jens Wagner on Surface Pro X :: Das Gerät macht mir große Freude at 16:52
Hubert Stettner on Office 365 im Tagesangebot at 16:26
Volker Weber on Surface Pro X :: Das Gerät macht mir große Freude at 15:35
Jens Wagner on Surface Pro X :: Das Gerät macht mir große Freude at 15:26
Christoph Spitz on Office 365 im Tagesangebot at 13:38
Yves Menge on Office 365 im Tagesangebot at 12:44
Volker Weber on Marshall Monitor II A.N.C. :: Erste Eindrücke at 08:13
Maikel Maes on Marshall Monitor II A.N.C. :: Erste Eindrücke at 07:58
Jochen Kattoll on The Neighbor’s Window :: Oscar Winning Short Film at 22:21
Harald Gärttner on Microsoft Office app on Android and iOS at 15:09
Volker Weber on App-Store-Interna: Apple geht gegen Buchveröffentlichung vor at 14:55
Oliver Stör on App-Store-Interna: Apple geht gegen Buchveröffentlichung vor at 13:42
Volker Weber on Microsoft Office app on Android and iOS at 13:02
Harald Gärttner on Microsoft Office app on Android and iOS at 12:56
Volker Weber on Marshall Monitor II A.N.C. :: Erste Eindrücke at 08:07
Thomas Cloer on Marshall Monitor II A.N.C. :: Erste Eindrücke at 07:57
Matthias Lorz on The Neighbor’s Window :: Oscar Winning Short Film at 12:47
Volker Weber on The Neighbor’s Window :: Oscar Winning Short Film at 11:54
Fabio Peruzzi on The Neighbor’s Window :: Oscar Winning Short Film at 11:48
Fabio Peruzzi on The Neighbor’s Window :: Oscar Winning Short Film at 11:45

Ceci n'est pas un blog

I explain difficult concepts in simple ways. For free, and for money. Clue procurement and bullshit detection.

vowe

Contact
Publications
Stuff that works
Amazon Wish List
Frequently Asked Questions

rss feed  twitter  amazon

Local time is 14:00

visitors.gif

Paypal vowe