Excel 2007 had a serious bug where some numbers display as ” 100,000 ” regardless of the true value around 65,535 and 65,536.
Excel 2007 and Excel Services 2007 had a problem with a few numbers it doesn’t like … truly.
A few numbers around the 65,535 and the 65,536 mark did not display properly and instead of the correct result it will show ” 100,000″. The true result is stored and most cells based upon the flawed display will work out correctly – but any screen display or printout is wrong.
If you have a fully updated Office 2007 it will include the fix for this bug.
What happened
Some Excel 2007 calculations with a result in the range:
65,534.99999999995 to 65,535
or
65,535.99999999995 to 65,536
will display the characters ” 100,000 ” instead of the correct result. The right number is stored ‘under’ the cell so calculations based on the ‘bad’ cell should be OK (unless that result is in the problem range too).
The problem is exacerbated by being such relatively low numbers and two integers which are more likely to be the result of live customer worksheets than a higher number in the millions or billions. Eg – I buy 850 widgets at $77.10 each – is the total cost $65,535 or the $100,000 Excel tells me?
You don’t need fancy formulas to make this happen – the bug will appear with any of the following formulas:
= 77.1 * 850
= 10.2 * 6425
=20.4 * 3212.5
=850 * $77.10
But not all results are affected, for example =32767.5*2 displays the correct result.
Iterative calculations which ‘pass through’ the range of problem numbers should be OK because they will work on the real cell value not the displayed value – however if the final result of an iteration is in the range then the displayed value could be wrong.
Conditional formatting still works correctly thought it might seem wrong. That’s because Excel conditional formatting works off the actual cell value not the displayed number. If you have a condition to work if a cell equals 65,535 then Excel 2007 may trigger that condition even though the cell is displaying ” 100,000 “.
Surprisingly, the TEXT function (which converts a number to text) works off the displayed value not the true cell value.
Microsoft is at pains to say that the problem is only with the displayed value of a cell not the actual value – however that glosses over the fact that some functions (like CELL and TEXT) work off that displayed value not the real one.
That attempt at mitigation does not give much comfort to Excel 2007 customers and should not give ANY reassurance to Microsoft.
Another attempt to minimize the importance of the bug is Microsoft’s effort to reduce the scale of the problem. They point out that Excel can use ” 9.214*10^18 different floating point numbers ” but the bug only affects 12 of that incredibly large number of possibilities. So what? The numbers involved are relatively small and include two integers – as such they are likely to appear in daily worksheets. Excel is a worksheet and mathematical accuracy applies to ALL numbers not just a very large percentage of them.
Imagine if they said that Word can display thousands of different characters and only has a problem with a few?
What happened
Microsoft released a patch. As long as you have an up-t0-date Office 2007 with the final patches, your Excel won’t have this bug.
We have to wonder if the problem with numbers around the 65,535/65,536 is the only one of its type? Perhaps there are other ‘magic’ numbers which display incorrectly. In case anyone on the Excel teams thinks that’s impossible – we’d like to remind them about the Excel 97 bug where Microsoft produced a ‘fix’ only to be severely embarrassed within days. The first fix dealt with only one narrow bug and not the broader issue, so yet another patch was required. Their team leader bravely says “All other calculation results are not affected” – we hope he’s right.
What should happen
If Microsoft felt truly responsible to their clients, there would be a formal notice of the problem on their Knowledge Base instead of the ‘back-channel’ of an employee blog.
The problem is a little understandable when you consider that Microsoft’s testing focuses on the real results of calculations rather than what appears on the screen. Testing the latter is harder but not impossible even using Excel’s in-built functions like CELL() to return the displayed contents of a cell.
Excel 2007 has been out for almost a year, so you have to wonder how many people have reported the problem and Microsoft has ignored them? With the past Excel calc bugs, Microsoft had ignored many reports from customers – the faith in the mathematical accuracy of Excel is strong and it’s easier to believe the customer is wrong.
Clearly Microsoft needs to further improve their testing regime for Excel and not take anything for granted.
And it will be interesting to know why only those particular numbers are causing a problem?
See Also
- Excel bug: Row Insertion and cell ranges
- Singular / Plural text in Excel
- Office 2007 Service Pack 1 – a cautious first look
- What you see isn’t what Excel knows
- Excel calc bug fix
