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