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

Google Spreadsheet bug

[DJ_Perl]

Open a Google Docs spreadsheet. Type =COMBIN(55,27)

Microsoft Excel is the Worst Part of Office 2007

[ChaoticCoyote]

I use the Office 2007 versions of Word, Excel, and OneNote on a regular basis for my Windows business work; the *only* one I've had *any* trouble with is Excel. I can blue screen my machines with Excel on a regular basis; unfortunately, one of my paying customers requires Excel 2007 because they invested heavily in it (long before I was contracted), so I haven't got much choice, since .xlsx documents won't open in any FOSS spreadsheet apps.

For my personal work, I use Gnumeric on Linux.

some limited testing

[indaba]

original 850 77.1 100000
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

Imperiled by binary decimals?

[Protoslo]

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.

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 ;p).

Not the only devastating bug

[Casandro]

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.

Re:In OOXML?

[mikkelm]

Visio is great for network diagrams. Unless you have 1000+ nodes with new ones added every day. For network diagrams, you really need something where the diagram is plotted from the data, not where the data is plotted onto the diagram.

Oblig.

[ZJVavrek]

The number 65,536 is an awkward figure to everyone except a hacker, who recognizes it more readily than his own mother's date of birth: It happens to be a power of 2^16 power to be exact -- and even the exponent 16 is equal to 2, and 4 is equal to 22. Along with 256; 32,768; and 2,147,483,648; 65,536 is one of the foundation stones of the hacker universe, in which 2 is the only really important number because that's how many digits a computer can recognize. One of those digits is 0, and the other is 1. Any number that can be created by fetishistically multiplying 2s by each other, and subtracting the occasional 1, will be instantly recognizable to a hacker.

Re:Good Luck!

[Petrushka]

If it were to happen to any other number, it would either be at the 8 bit number (though I doubt it) or at the 32/64 bit number they use for the larger values.

That's a good point. Has someone with Excel 2007 tested whether a similar bug comes up with (2^32)-1?

Re:Special powers

[OverflowingBitBucket]

How many other apps do you know that replace half of the system libraries?

Try an install of the Visual Studio .NET family. Can't remember if it messes with system files but it messes around with Office files, even if Office isn't around. You then need to use Office patches to update them. It's insane.

Looks like a typo

[jorghis]

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.

Re:Microsoft just announced plans for their fix

[Bionic Vapour Boy]

In fact even this 1/77.1 doesn't work. So the bug isn't 65,534-bug, it's floating point bug. I even re-checked the calculations with Microsoft Calculator.

MS Multiplan on Commodore 64

[innocent_white_lamb]

Just for fun I cranked up Microsoft Multiplan 1.06 from 1983 for the Commmodore 64 (using the Vice emulator [viceteam.org], 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.

Re:Is anyone using Excel 2007?

[ultrasound]

I'm using Excel '97 and Word '97 on my XP Pro 2.6GHz dual core and they work fine. Excel loads in an instant, most numbers seem to multiply ok (haven't tested them all but I'm sure the service packs have fixed most major errors by now). In 10 years I haven't seen a single new feature that makes a compelling reason to upgrade.

I started with Excel 2.0*, in those days every release seemed to add significant new features (and floppies) and there was always a rush to install the new version and some excitement as you discovered the new features. By Excel '97 the program was so feature rich that most users never used more than a fraction of its functionality, same goes for Word '97. Where do you go from there?

(OOXML etc, I know).

*I think it was 2.0, it was 20 years ago.

basic math errors in MS libs? Nothing new..

[12357bd]

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!

Re:I have another theory entirely.

[Mr Z]

As Harlan Grove pointed out in the thread, [google.com] some functions may be taking the value from the .Text property rather than the .Value property. Most things see the correct value despite it being misdisplayed.

Why would they do something goofy like that? I have no idea, and I don't have an easy way to check off-hand, at least not in Excel 2007.

Re:Microsoft just announced plans for their fix

[thealsir]

I know that one of the main spreadsheet enhancements in '07 was the ability to have 1,048,576 rows instead of 65,536. It seems like somewhere in testing M$ used the old bounds, and forgot to check for the new ones. Or some functions are hard-coded to the old bounds instead of the new ones. Either way, sloppy and un-M$ office like (okay, maybe very M$).

Re:In OOXML?

[Anonymous Coward]

Actually, this does: http://www.omnigroup.com/applications/omnigraffle/pro/ [omnigroup.com] Quote: OmniGraffle Professional 4 now uses the recently-opened Visio XML schemas, so there have been many improvements to the Visio XML import/export function, and some of the newer feature sets (such as the Bezier drawing tool) bring OmniGraffle's Visio support to a higher level.

They DO pay you to find bugs ..

[Dr_Barnowl]

... 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?

Re:please dig this story and get it to the masses

[nschubach]

Not to mention I think this comment makes a good point:

You do know there's a big of angst between Slashdot and Digg, right? As the post on Slashdot implies that this will not be seen unless it's on Digg would tell me that whoever posted it on Slashdot has little respect for Slashdotters and Slashdot itself.

Re:In OOXML?

[v1]

I have a spreadsheet I use for my finances, and I have been having to resort to converting some numbers to strings and back again to get around rounding errors, but excel pulled a new one on me. After months of entering my checkbook data, a cell toggled to indicate a balance error. It was something like $1234.56 != $1234.56. I bumped the precision as high as it would go and both cells still showed exactly the same value but would not evaluate as equal. So I set a third cell thusly A3=A2-A1. And the third cell now showed as something like -1.0032E-18. (that's like 0.0000000000000000010032) Lovely. This is not the first time I have encountered cumulative rounding errors in Excel. So now I have to test not for (in)equality, but to test for the difference to be less than 1/100th of a cent. That's BS for a spreadsheet app.

I was surprised to find that the error actually had came up much earlier, but when the difference between cells is less than a certain amount, excel's equality resolves as true. So removing the last few entries, the book balanced again, even though the difference was now -2.18E-19. Clearly they are aware of this problem and are trying to hide it. If it was not actively fudging numbers there would probably be a lot more people having numerical problems. (numbers cited here are illustrations, I do not recall the exact values or precision)

Re:please dig this story and get it to the masses

[ekimminau]

I found this interesting.

+44 diggs by trib4lmaniac 3 hours ago
Of interest is the fact that 65,535 is the largest number that can represented by an unsigned, 16 bit integer (i.e. 0xFFFF).

After a little experimentation in VBA, I have found that 100000 is only returned when accessing the cells "Text" property. When asking for the cells "Value" 65,535 is returned as expected.
I created a quick macro to test this after filling cell A1 with the formula "=850*77.1"

MsgBox (Range("A1").Text) => Alerts 100000
MsgBox (Range("A1").Value) => Alerts 65535

Re:In OOXML?

[porpnorber]

Yes. Spreadsheet applications use floating point arithmetic, and are thus inappropriate for accounting. Does nobody go to school anymore? What is the entire damned world thinking?

Surely your (hopefully government mandated) accounting procedures tell you to do your calculations in cents, or mils, or something, do they not? Then, I would think, you should follow those procedures. You really should. Even if Excel is (a) sold by Microsoft and (b) pretty convenient.

Or is it me that's insane?

(A quick check at Wikipedia tells me, for example, that the US Coinage act of 1792 said "That the money of account of the United States shall be expressed in dollars or units, dismes or tenths, cents or hundredths, and milles or thousandths, a disme being the tenth part of a dollar, a cent the hundredth part of a dollar, a mille the thousandth part of a dollar, and that all accounts in the public offices and all proceedings in the courts of the United States shall be kept and had in conformity to this regulation." Have these sorts of regulations really all been overturned?)