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."
They will be disabling multiplication in all future versions of Excel.
What happens if you use this on an older Intel chip? Do the issues cancel out?
See my journal for slashdot ID's by year. Mine created in 2005. http://slashdot.org/journal/289875/slashdot-ids-by-year
1 2 3 4 5 5 6 7 8 9 10 11 12... ...65,533 65,534 100,000
Give em a break, even the Count from Sesame Street cant count that high.
Make SELinux enforcing again!
Try =6*9.
This just in: Florida plans to do use Microsoft Excel to calculate the 08 election results.
News at 11
Make SELinux enforcing again!
I use Matlab and Octave constantly for things everyone around me uses Excel for (I do structural engineering). I am no amazing hacker or anything, but I simply find it scads easier to use that sort of paradigm over the spreadsheet analogy for almost any application. That aside, Excel in particular seems constantly to try to outthink me and consistently to have these sorts of strange calculation errors.
=850*77.1
</MultiplyLikeExcel2007>
Multiplication is an unnecessary abstraction anyway. This should really be represented by summing the value of 77.1 entered independently into 850 cells: =sum(a1:a850).
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
Sufficiently advanced incompetence is indistinguishable from malice.
What, lxvDXXXV?
(And yes, what have the Romans ever done for us, apart from apparently producing correctly functioning spreadsheet software?)
Tedious Bloggy Stuff - hooray?
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
This is yet another example of where Calc fails utterly to be compatible with Excel. How can I use Calc if I can't be sure that it will produce the same answers that my boss gets with Excel?
All those open source developers just don't get it. Geeks that they are, they prize accuracy over consistency and uniformity. The clueless dweebs need to get out of their parents' basements and get a clue about how the REAL WORLD works. Nobody gets promoted for contradicting their boss, duh.
Nope, until Calc can faithfully reproduce every Excel calculation, it simply won't be ready for use in the real world.
Note to ACs: I usually delete AC replies without reading them. If you want to talk to me, log in.
Does the built-in flight simulator still work?
As long as you stay below 65535 feet.
Table-ized A.I.
It could be for Excel users.
65535 is common in computing because it's the highest number which can be represented by an unsigned 16 bit binary. If Excel is mishandling it somewhere in the background, chances are that failure will show up at multiple points.
If I had an important Excel 2007 spreadsheet, I'd be loading it up in OOo Calc or an older version of Excel now.
"I've got more toys than Teruhisa Kitahara."
Was it not Bill himself who said you would never need more than 64K of memory? Well it's official, you don't need any number greater than 64K either!
Just remember Micro$oft knows best... move along, nothing to see here.
And you didn't balk at the 34% increase in rent?
This issue is a bit more complicated than you think.
Well, I'm off to deposit $655.35 less my current balance into my bank account.
CAD derivatives are quicker, less precise, like a blaster vs a light saber, only few use light sabers, many use blasters.
Under the influence of Post-Cyberpunk Gonzo Journalism
a spot-on analogy involving jedis? you sir win two internets.
... still waiting for this free-as-in-beer free beer I keep hearing about.
...except for the minor detail that 65535 is 0xFFFF... :-)
That said, my 32-bit print routine for a 16-bit CPU actually works by printing two 16 bit numbers, with a slight hack to the 16-bit routine to allow it to print numbers in the range 65536 - 99999 for the lower 5 digits. It does this by dividing the 32-bit number by, you guessed it, 100000. It then prints the quotient and the remainder. It has to do some extra legwork, though, to get the leading zeros right across the two words, and I think it's there that the code went south if they're using a technique similar to mine.
I'm guessing what happened here is that there's an off-by-1 error in a comparison somewhere (i.e. ">= 65535" instead of "> 65535"), and the 32-bit quotient/remainder print routine kicks in. Since the number is already smaller than 100000, it probably hits a fall-thru case where the quotient is assumed to be 1, and there's no remainder, hence it printing 100000.
For reference, here's that assembly code I mentioned: prnum32.asm and prnum16.asm
--JoeProgram Intellivision!
to retain backward compatibility with MSDOS 16bit mode, the operands of any multiplication that may exceeds a 16 bit boundary must be converted to farsi and multiplied using an abacus emulator, as per sec (II)par alpha comma 2; the result may or may not appear in Windows Genuine Octal Format (a.k.a. fake octal - that is octal without the leading zero and minus 1) for added convenience of EndUser(tm).
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?
I say we petition Microsoft to include a multiplyLikeExcel2007 element in the next version of OOXML.
The OOXML specs are already 65498 pages long. If Microsoft can give "multiplyLikeExcel2007" a 37 page treatment in the OOXML specs, the total page count for OOXML will reach an amazing 100000 pages (therefore 166 times better than ODF). Sounds like a winning plan to me!
No wonder you can't meet any women!
"most numbers seem to multiply ok"
perhaps making a statement like this about a spreadsheet is not really a shining endorsement. let me know when you've tested the rest of them.
sam brightman
....interesting twist...
I just fired up Excel and created a simple graph:
One column of numbers was a series from 845-855. The next column was the first column * 77.1. As expected, the series jumped from:
65149.5, 65226.6, 65303.7, 65457.9, 100000, 65612.1, 65689.2, 65766.3...
But then when I created a graph to display this, I had a simple straight line -- trying to plot the single data point represented by "100000" also displayed the accurate number. Any other calculations done with this number yielded the right result, too. Taking the value of the cell that displays100000 and multiplying it by 2 results in 131070.
So all things considered, this really amounts to an Easter Egg. Most spreadsheets will calculate, graph, and function exactly as they should even using the results from a cell that displays inaccurately in that one case...
I would have to say that explosives are the most abused technology in all of history.
Wow, that was a lot of work to demonstrate that a number ending in a 5 isn't prime.
I read the internet for the articles.