You say 100,000 I say 65,535! Let’s call the whole thing off!

According to this Google groups thread, Excel 2007 has a serious bug. Certain calculations (e.g. =850*77.1) that should yield 65535 are being rendered by Excel 2007 as 100,000. Brilliant, bloody brilliant!

I’ve been a fan of 2007 especially the new table handling features and the ability to handle more than 65536 rows, these are particularly useful for someone like myself who uses Excel as an ETL and data cleansing tool. Unlike many others, the new ribbon UI doesn’t bother me, it’s a slight annoyance, but within a day or so I’d mastered it. In fact, my wife who’s trained first time Excel users in both 2003 and 2007 reported back that novices found the new UI much easier to master.

But returning the wrong answer! “Well that beats Banagher”. I’ll not be recommending any client of mine to upgrade to Office 2007, until this is fixed.

If you don’t have a copy of Excel 2007, you can try it out using the Office 2007 online “test drive”, bugs and all.

Below is a screen shot of the bug in action using the online test drive version. Column A is set to =850*77.1, Column B uses =5.1*12850 and Column C is set to =100000. The first two should yield 65535 but all three display as 100,000! If you SUM() column A you get 196605 (see A7, which is the correct answer for 65535+65535+65535). But if you AVG() the affected cells you get 100,000, see B7. Also, see the various results when an affected cell is used in other calculations (E5 to H5).

Advertisements

2 responses to “You say 100,000 I say 65,535! Let’s call the whole thing off!

  1. Pingback: MS to fix “65,535 as 100,000 bug” - ASAP « Gobán Saor

  2. Pingback: Excel 2007 bug and the chance of being hit by a meteorite. « Gobán Saor