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."
Yep - my office switched to Vista and Office 2007. Then again, we're a networking firm, so it's in our best interests to use stuff while it's still "beta" so we know the bugs and quirks before our customers start playing with it.
As an aside, when I went to pick up a lease renewal form for my apartment complex, I noticed that the lady at the front counter was also running Office 2007, so I'd say it's out there - just not exceptionally widespread at the moment, compared to other versions of Office.
Years ago I wrote a parser and interpreter for MS Excel. At that time, the numerical representation was not IEEE floating point, but a truncated, poor cousin (8 bit) of a decent float implemetation called "RK numbers". I recall that I needed to interpolate and provide semi-randomized numbers for the gaps in the number line that 8 bits (mantissa + ordinate) would incur.
I wonder if MS still uses this implementation - which goes back to the early 1990s. It's a propietary, gappy implementation that was based, I think, on Mac / Motorola implementations.
Just my cut on it - I may be off base.
The bug shows up for me. I'm running on Vista, and my About box gives the Excel version as 12.0.6024.5000 (MSO 12.0.6017.5000).
=(8500 * 1) * 7.71
that evaluates to 65535
but :
=850*(771/10) evaluates to 100000
Office 2K7 on XP SP2 and I see the same results as the article.
It's broken here -- WinXP SP2, MS Office 2007 (Excel v. 12.0.6024.5000).
I've done a lot of defending of MS for Office 2007, but this is a little scary. Word can suck in spots, but math really has to work.
It is pitch black. You are likely to be eaten by a grue.
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."
I like Kivio (probably same complaints as Dia though)
Has stencil sets available for cheap too.
Wow, sent an e-mail as suggested when clicking on "use classic" banner, and got a fast response that addressed my msg
I'm fond of Umbrello. But that replaces a completely different set of functionality from Visio.
A few observations:
1) If you go into the Visual Basic editor and print the "Value" property of the cell containing the formula =850*771 you get 65535, as you should. The "Text" property however is "100000". So it seems the problem is not in the internal computation, but in the conversion to the displayed value.
2) That said, I am not sure why, if you then enter =A1+1 into another cell you get 100001 instead of 65536. It seems the formula is picking up the displayed value, not the internal value when you perform addition, but not when you do multiplication.
3) There is something special about the value 77.1. Other variations on the calculation produce the correct result.
85 * 771 = 65535
850 * 77.1 = 100000
8500 * 7.71 = 65535
I wrote a short C program so I could see what the actual floating point representations of these numbers were:
#include
#include
int main (int argc, const char *argv[])
{
if (argc != 3)
{
puts("You must enter two numbers");
}
else
{
double v1 = atof(argv[1]);
double v2 = atof(argv[2]);
double result = v1 * v2;
printf("%f * %f = %f\n", v1, v2, result);
printf("%#016I64x * %#016I64x = %#016I64x\n", *(long long *)&v1, *(long long *)&v2, *(long long *)&result);
}
return 0;
}
This produced:
850.000000 * 77.100000 = 65535.000000
0x408a900000000000 * 0x4053466666666666 = 0x40efffdfffffffff
Notice that the value 77.1 has a repeating fraction when represented in floating point. So does the result. In fact if you dig out your Intel manuals and decipher the hex representation of the result you find that it is the binary equivalent of 65534.99999999999999...
It seems that the problem occurs in the conversion of that value to displayable text.
Wouldn't that apply to 65,536, rather than 65,535? 65,535 is 0xFFFF, not 0x10000.
How are sites slashdotted when nobody reads TFAs?
...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!
53*47*43*41*37*34*31*29*11*7*5*2
so the 5*2 at the end guarantees that the answer will end in a 0.
Erp. I can't count. That IS a 52 bit number, and so is right on the edge of what a double precision mantissa can store. Move along.
Program Intellivision!
Actually it's quoted from Snow Crash.
By definition, 1 Hz = 1/s and 1 rad = 1. I follows that 1 Hz = 1 rad/s.
The problem is of course in the semantics. There's nothing in the definition of a hertz that limits it to cycles or rotations. So Google's answer is among the infinity of possible interpretations. If an object turning at 1 rad/s beeps at every completed radian, the beep rate is 1 Hz.
By the same vein, Google correctly reports that
1 radian = 1.66053886 x 10^-24 moles
But, sadly, Office 2007 will not run on an FDIV Pentium. The fastest Pentium that had the FDIV bug was 100 MHz, which is far too slow to even attempt to install Windows XP on, which is a requirement for Office 2007. Way back when XP first came out, I got it to install on a Pentium MMX 233 MHz notebook with 64 MB of RAM, but Service Pack 2 made that computer impossibly slow. And it refused to install at all on an old Pentium 166 MHz computer I had lying around. (And it even had more memory.)
Another non-functioning site was "uncertainty.microsoft.com."
The purpose of that site was not known.
Ha fool - Octave is wrong!
3824345300380220 is the answer. Go suck a tree.
Real engineers use the back of an envelope, a pencil and some rules of thumb.
Pirate Party UK
....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.
Yes and no, the definition of a WORD has typically been hardware specific. For example, on a PDP, a word was 18 bits, IIRC. However, it has always meant a bit-field, which doesn't make any assumption about type.
We at slashdot are scientists, specialists and kernel hackers. Your FUD will be found out.
Welcome to the world of Floating point arithmetic.
Look at these articles.
http://support.microsoft.com/kb/q78113/
http://c-faq.com/fp/printfprec.html
http://c-faq.com/fp/fpequal.html
Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers.
It looks more like the result of an overflow error at some stage of the calculation, since 65535 is the maximum value an unsigned 16 bit integer can hold.
For those who don't remember, the reason for the "95" name was likely because Windows "Chicago" kept getting delayed. Eventually Gates announced it was going to be released under the name "Windows 95". Speculation was that the name was chosen to pressure the development team to get it released in 1995 instead of letting it slip to 1996. They released it in late summer 1995, arguably too soon.