Excel-lent Micro-bytes?

September 27, 2007 7:59 AM

Is Microsoft the latest victim of binary prefix confusion? [via TechAmok]
posted by Fezboy! (26 comments total)

I know, I know...It's related to floating point accuracy, but it's way more funny to me the other way. Joel on Software explains the problem.
posted by Fezboy! at 8:05 AM on September 27, 2007

but it's way more funny to me the other way

Isn't one "LOLMICROSOFT!!!1!" post sufficient?
posted by CitrusFreak12 at 8:11 AM on September 27, 2007

CitrusFreak: I'd say 'no', because some of us rely on Excel every working day.
posted by pompomtom at 8:14 AM on September 27, 2007

This will be fixed in Excel 100,000.
posted by brain_drain at 8:35 AM on September 27, 2007 [1 favorite has favorites]

LOL IEEE 754

1 0 0 1 0 0 1 S O S
posted by GuyZero at 8:36 AM on September 27, 2007

I think it's bizarre they don't just bite the bullet and do proper decimal math. At least for currency types. Floating point is too spooky.

Maybe the Intel chips don't hardware accelerate it, but you could recalculate most spreadsheets in the time it takes to render a single Aero window frame. And heck, my old Apple ][ had hardware support for decimal math.
posted by Nelson at 8:38 AM on September 27, 2007

I think it's bizarre they don't just bite the bullet and do proper decimal math. At least for currency types. Floating point is too spooky.

From the link to Joel on Software:

And let's face it -- do you really want the bright sparks who work there now, and manage to break lots of perfectly good working code -- rewriting the core calculating engine in Excel? Better keep them busy adding and removing dancing paper clips all day long.
posted by straight at 8:41 AM on September 27, 2007 [1 favorite has favorites]

According to this google groups discussion, the problem is a display bug in Excel 2003, not a calculation bug. Specifically, this comment describes that calculations using the erroneously displaying value still work correctly.

And Open Office evidently does not have this problem.

How Microsoft could allow a problem like this is ridiculous. For years in Windows 3.1 there were calculation errors in, of all places, Windows Calculator.

This company has a lot of problems.
posted by Pastabagel at 8:44 AM on September 27, 2007

Also, this problem did not exist in Excel 2000 Which makes me wonder just what kind of programmers microsoft hires.
posted by Pastabagel at 8:46 AM on September 27, 2007

You occasionally see these more correct prefixes used in software, but adoption has been slow at best. There are several problems:

1. They sound ridiculous. I hear the metric system used more often in the United States than I hear the words "kibibyte" or "mebibyte" uttered by anyone with a straight face. Which is to say, never.

I agree 100%. Well, actually I agree 87.5%. I think "yobibyte" sounds awesome, especially compared to "yottabyte"
posted by delmoi at 8:50 AM on September 27, 2007

Over the years, Microsoft got so much heat for floating point rounding artifacts in the Windows Calculator that they rewrote it to use an arbitrary-precision arithmetic library. Since you have to poke at Windows Calculator with a stick, it doesn't have to be as fast as Excel. That said, CPUs have gotten pretty fast. I'll bet an arbitrary-precision version of Excel would perform pretty well these days. Still, the Microsoft Excel support team has spent the last 20 years defending IEEE 754, and it's not surprising that they've started to believe in it.

Windows Calculator uses a hand-coded arbitrary precision system for doing math? Windows Calculator?
posted by delmoi at 9:00 AM on September 27, 2007

If they fix it then it will break all my excel vba billing programs which rely on this undocumented feature.
posted by srboisvert at 9:00 AM on September 27, 2007 [1 favorite has favorites]

Maybe the Intel chips don't hardware accelerate it, but you could recalculate most spreadsheets in the time it takes to render a single Aero window frame. And heck, my old Apple ][ had hardware support for decimal math.

Oddly enough, Intel chips have always had support for BCD (binary coded decimal) numbers. I don't think you would seriously want to use these old instructions for new applications, they are probably pretty limited.
posted by delmoi at 9:08 AM on September 27, 2007

So the one MS application that I actually use and like is fucked up. Great.
posted by tkchrist at 9:10 AM on September 27, 2007

From the Joel on Software link: "A: IMHO, no, the chance that you would see this in real life calculations is microscopic." Sure, if any number in the range Excel can represent were as likely to be displayed by all users. But given the tens of millions of people using Excel, 65,535 must be misrepresented multiple times every day.

"Whoa, when did Carl get that $35,000 raise?"
posted by ardgedee at 9:23 AM on September 27, 2007

But given the tens of millions of people using Excel, 65,535 must be misrepresented multiple times every day.

It's not 65535 that's the problem. From the first link:

6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem

It's unlikely that 12 specific floating point numbers in a pool of 9.214*10^18 numbers are represented very often. Which makes sense, considering how quickly this would have been reported and fixed if that wasn't the case.
posted by splice at 9:44 AM on September 27, 2007

Didn't this happen in Office Space?
posted by tippiedog at 10:33 AM on September 27, 2007

it looks like a display bug, not a data bug. if A1 is "=88.1*750" and A2 is "=A1+2", A2 displays the correct number (65537). This is serious, it can confuse people, but the correct value is what is used for calculations.
posted by jenkinsEar at 11:08 AM on September 27, 2007 [1 favorite has favorites]

How Microsoft could allow a problem like this is ridiculous . . . This company has a lot of problems.

I'm all for bashing MS, but come on. From the Joel link:

"Q: Shouldn't they be testing for these kinds of things?

A: I'll bet that most of the numeric testing done on the Excel team is done automatically with VBA code. Cells containing this value display as 100,000, but from VBA, they're going to look like 65,535 (since the number would be passed into the Basic runtime in binary, before the display formatting.) I'm sure there's plenty of code to test display formatting, but with a bug like this that only happens on 12 out of 18446744073709551616 possible floating point binary numbers, it's unlikely that any set of black-box tests would cover this case."

How do you propose to test the visual display of a number close to, but not x for every case of x an Excel user might use. If you think this represents endemic problems, step back and consider the QA impact of having tens or hundreds of millions of users.
posted by yerfatma at 11:24 AM on September 27, 2007

Using Excel for anything requiring numerical presicion has always been a problem. Microsoft has never, and still does not implement IEEE 754 properly. Now 754 isn't perfect, but at least its fault are regular and well-understood. Excel's numberical underpinnings have changed with each new verion of the MS C run-time library; every verision of Excel does numerical calculation slightly differently.

MS has more or less admitted that they nevery intend to fully support 754, prefering to opimize their math library for speed rather than correctness. "Optimizing the customer experience" is the way they usually put it. Gamerz beat science here.

It's so bad, and being going on for so long, that scientific standard bodies actively recommend against using Excel (by name) for important calculations. The one I have to live up to, ISO 17025, requires that either I prove that Excel can do the calculations I need (for every single version I use) or that I use an IEEE 754 compliant peice of software. Since a lot of independent vendors simply use the MS math library, this is harder than it sounds.
posted by bonehead at 11:54 AM on September 27, 2007

Q: Shouldn't they be testing for these kinds of things?

If you are solely relying on QA-team black-box testing to ensure quality in a product as big and complicated as Excel, you're going to miss stuff like this. Mistakes at this level need to be caught in design and code reviews.
posted by octothorpe at 11:58 AM on September 27, 2007

So, um, I guess I'll just go withdraw and redeposit $655.35 from my bank account a few times.
posted by East Manitoba Regional Junior Kabaddi Champion '94 at 12:12 PM on September 27, 2007

My favorite quote from the original article, if only because MSDN's David Gainer felt it needed to be said:

We take calculation in Excel very seriously.
posted by rokusan at 1:32 PM on September 27, 2007

If you are solely relying on QA-team black-box testing to ensure quality in a product as big and complicated as Excel, you're going to miss stuff like this. Mistakes at this level need to be caught in design and code reviews.

How, exactly, would you expect human testers to catch a bug like this? I mean, would you seriously expect people to just type in millions of formulas over and over again? This is the exact kind of thing that you write automated test for. What they should have been doing was checking the formatted result, rather then the numerical result.

Furthermore, code reviews wouldn't catch this, since they are relying on standard libraries, which are assumed to work.
posted by delmoi at 1:32 PM on September 27, 2007

This issue only exists in Excel 2007, not previous versions.

Has anyone found any reason to upgrade beyond Office 2000? I haven't, but I'd be curious what others have to say.
posted by Triplanetary at 1:38 PM on September 27, 2007

Triplanetary writes "Has anyone found any reason to upgrade beyond Office 2000?"

All of the students sporting brand spanky new laptops are laden with Vista and Office2K7. They generate documents in 2K7 and then wonder why their documents are useless everywhere on campus. That's about the only justification I've run across so far this year.
posted by Fezboy! at 1:47 PM on September 27, 2007