Latest spreadsheet program has trouble with 16-bit calculations but a fix is on the way.

It’s unusual for a spreadsheet application to have problem multiplying two numbers together, a function that is typically “bread and butter” for these types of apps however that’s exactly what Microsoft is facing with its latest version of Excel.

Microsoft Excel team blogger, David Gainer, posted on his blog yesterday that Excel 2007, along with Excel Services 2007, is having trouble involving calculations based around 65,535, known as 2^16-1 in binary notation.

Multiply 77.1 by 850 and you should end up with 65,535 however Excel 2007 returns a value of 100,000 however Gainer has reported that the problem doesn’t just multiplication but involves calculations centering around 65,535 and 65,536.

“This issue was introduced when we were making changes to the Excel calculation logic in the Office 2007 time frame.  Specifically, Excel incorrectly displays the result of a calculation in 12 very specific cases (outlined below).  The key here is that the issue is actually not in the calculation itself (the result of the calculation stored in Excel’s memory is correct), but only in the result that is shown in the sheet.  Said another way, =850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer (i.e. if A1 contains “=850*77.1”, and A2 contains “=A1*2”, A2 will return the correct answer of 131,070).” Gainer wrote on his blog today.

According to Microsoft research, the problem revolves around 12 of the possible 9.214 x 10^18 possible floating-point numbers Excel 2007 can hold – six between 65534.99999999995 and 65535 and six between 65535.99999999995 and 65536.

While calculations in general are not affected, it appears that when these calculation results are displayed in a cell that the problem occurs.

The fault only occurs in Excel 2007 and not in previous versions of the popular spreadsheet app.

Gainer says the Excel team has a fix for the problem and it should make it up onto the Microsoft website once it worms its way through the bureaucracy that is Microsoft testing and official builds lab.

There is an old saying “if it ain’t broke, don’t fix it” but no doubt, Microsoft’s Excel team have problem heard that from higher up the Microsoft foodchain by now…