Slashdot Mirror
Excel 2007 Multiplication Bug
tibbar66 writes with news of a serious multiplication bug in Excel 2007, which has been reported to the company. The example that first came to light is =850*77.1 — which gives a result of 100,000 instead of the correct 65,535. It seems that any formula that should evaluate to 65,535 will act strangely. One poster in the forum noted these behaviors: "Suppose the formula is in A1. =A1+1 returns 100,001, which appears to show the formula is in fact 100,000... =A1*2 returns 131,070, as if A1 had 65,535 (which it should have been). =A1*1 keeps it at 100,000. =A1-1 returns 65,534. =A1/1 is still 100,000. =A1/2 returns 32767.5."
Open a Google Docs spreadsheet. Type =COMBIN(55,27)
-- Subvert the dominant paradigm. Repeat as desired. http://ownlifeful.com/
425 154.2 100000
212.5 308.4 100000
8500 7.71 100000
but this evaluates correctly..
25 2621.4 65535
so it's not every multiplication that evaluates to 65535
I'm using Excel 2007 12.0.6024.5000
It sounds like they are doing small-number math in one representation (perhaps they use short fixed-width decimal representations) and then switching to another method (arbitrary length decimal numbers?) at the binary-inspired boundary 2^16...but somehow they got it mixed up with a different decimal boundary in the edge case.
;p).
Clearly the error is weirdly subtle, if 5.1*12850 gives the bugged behavior, but 8.5*7710 works just fine. In fact, I verified that all permutations of a bugged combination =A*B of the form =A/2*B*2 are bugged. Further...all of the buggy decimal values have no perfect floating point binary representation. 77.1 has an infinite binary expansion using IEE 754, while 8.5 has an exact representation. It seems likely that they are only using their BCD format (or whatever) when binary floating (or fixed) point just won't cut it, but then their internal->decimal conversion code chokes on 2^16 for some reason, while the binary (whether it is floating or fixed point) conversion works just fine (possibly because it doesn't have a boundary at 2^16--maybe it has its own threshold bugs
In the german version there is a bug for almoust a decade now.
If you type in 3.5 you will get "3. Mai". Now if you try to correct yourself by entering 3,5, the correct way to enter decimals in german, you will get "3. Januar". There just is no way for the unexperienced user to get back to normal.
The bug essentially is that the system allows dates without a year.
Surprised noone has pointed this out yet, but this is likely a hardcoded constant being used when excel converts from a 16 bit number to a 32 bit. It should have been "0x10000" but instead was 100000. Speculation sure, but it looks pretty likely, I mean how else would 100000 randomly appear when you did that computation?
Someone was slacking in the testing department.
Just for fun I cranked up Microsoft Multiplan 1.06 from 1983 for the Commmodore 64 (using the Vice emulator, and the magic calculation (850*77.1) gives the correct answer of 65535.
I have always been under the impression that Excel was originally based on MS Multiplan (isn't it?) so the code was correct at that time and has become broken at some subsequent point.
If you're a zombie and you know it, bite your friend!
Back in the 386 days, the tan() function returned the wrong sgn if no coprocessor was present. Contacted and confirmed the error they simply ignored such a basic issue, and replied with 'use sin() and cos() functions instead'. Great.
It seems old habits never die!
What's in a sig?
... your payment is a free office suite.
Bug finding is a valuable contribution to the value of any product. My current testing policy is to find the biggest pain in the ass in my user community - whoever has the largest bug count from the previous release. And give them the first beta. Every bug squashed improves the product for everyone. The cumulative value can't be ignored.
If you find bugs in any software, it's in your interest to report them, because you obviously want them fixed. If it's a commercial product, you may even be able to get more instant satisfaction. OTOH, for open-source products, I've had an instance where I was able to saunter into an IRC channel, mention a particular bug, and have the lead developer upload a new version to my server within 10 minutes, because he recognised the value of having a technically able user put his product through heavy stress.
Don't just swear and cuss about bugs in OpenOffice. Report them, send them copies of the files that break it. You might get your bug fixed. For free, in the next version. When did you last get that sort of deal from Microsoft?