Good news on the Microsoft Excel hundred millennium bug

 Turns out there is a bit of an uh-oh in Excel land. In Excel, do the calculation 77.1*850. Your Casio (if you still have one around) would tell you that’s 65,535. But Excel says it is 100,000. Yes, a nice, even, no apologies, hundred thousand.

It is quite amazing to me that after all these years of 10s of millions of people using Excel around the world, that this is the first time that a bug like this has been reported.

There is plenty of discussion over at the Microsoft excel blog regarding whether this is a real calculation error, or it is just a display problem. According to Microsoft engineers, this is a display error, while folks on the board have run several calculations and made the point that it is a calculation error, which, of course, would be much more serious.

Well, since we just love excel, we just had to do our own checking to see what was going on, and the good news is that it indeed appears to be just a display error.

The calculation (geek alert) is below. The number N = 77.1*850 turns out to be 100,000 as expected. N-1 is 65534, N-2 is 65533 and so on. N+1, is interestingly, 100,001 while N+2 drops back to 65537. Now when you take the difference of the two series, it is well behaved (you get back 0,1,2,…. as answers) indicating that this is not a storage bug but just a flight of fancy of the numerical representation of the display.

N=77.1*850 x N-x N+x x=[(N+x)-(N-x)/2]
100000 0 100000 100000 0
  1 65534 100001 1
  2 65533 65537 2
  3 65532 65538 3
  4 65531 65539 4
  5 65530 65540 5
  6 65529 65541 6
  7 65528 65542 7
  8 65527 65543 8
  9 65526 65544 9
  10 65525 65545 10

Now 65,535 or its close cousin 65,536 should look familiar to most tech folks. Yes it is 2^16 in binary representation indicating, perhaps some integer computation error in the display. Why excel exhibits this problem for only a handful of calculations (not all) that compute to 65,535, is not clear. But Microsoft promises it will be fixed soon.

Meanwhile, if at the end of a complicated floating point calculation, you get a nice even 100000 for the answer, be sure to get out the calculator!

  1. 1 Trackback(s)

  2. Oct 20, 2007: My Ghillie » Good news on the Microsoft Excel hundred millennium bug

Post a Comment

28 queries. 0.571 seconds.