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.
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?
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).
Vitor, read the headline (or the text) again ;-)
Martin, what exactly is you point? I've read it.
OK, Excel 2007 only :-)
It appears they were trying to fix one problem and created another one.
@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.
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.
@Thomas - Thanks, I see how that is less likely to get caught.
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?
Perhaps MS can convince the ISO to introduce a new standard for mathematic calculations. ;-)
maybe they should a quick-link to google
maybe they should revisit whatever they have submitted to the IRS ;-)
@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.
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.
@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!"
Post a comment
Recent comments
Ben Rose
on Remember, it's always the cable at 11:33
Ben Poole
on It has only been less than two hours at 09:44
Frank L. Quednau
on It has only been less than two hours at 09:29
Martin Hiegl
on It has only been less than two hours at 08:27
Stephan H. Wissel
on Notes.ini parameter RunFaster=1 is finally here at 05:24
Volker Weber
on It has only been less than two hours at 01:33
Thomas "Duffbert" Duff
on It has only been less than two hours at 01:26
Chris Linfoot
on Planet Lotus not picking up Christopher's feed at 21:56
Yancy Lent
on Planet Lotus not picking up Christopher's feed at 19:48
Bruce Elgort
on Robin Bloor: Why Google Chrome Will Dominate at 18:51
Mac Guidera
on Planet Lotus not picking up Christopher's feed at 16:04
Kevan Emmott
on 824 Chrome users so far today at 15:56
Chris Linfoot
on Planet Lotus not picking up Christopher's feed at 14:54
Lars Berntrop-Bos
on Planet Lotus not picking up Christopher's feed at 13:12
Andreas Braukmann
on 824 Chrome users so far today at 11:33
Nick Daisley
on Robin Bloor: Why Google Chrome Will Dominate at 10:14
Chris Linfoot
on Planet Lotus not picking up Christopher's feed at 09:42
Alper Iseri
on 824 Chrome users so far today at 09:38
Jean Pierre Wenzel
on 824 Chrome users so far today at 08:37
Jan-Piet Mens
on Robin Bloor: Why Google Chrome Will Dominate at 08:26
Benjamin Stein
on Synchronizing iPhone with ... Lotus Notes at 07:18
Greg Walrath
on Party like it's 2008 at 06:56
Andy Brunner
on Party like it's 2008 at 05:41
Michelle O'Rorke
on Synchronizing iPhone with ... Lotus Notes at 05:01
Arthur Fontaine
on Chrome in the wild at 03:26
Click here to subscribe
