Excel 2007 and Excel Services 2007 have a problem with a few numbers it doesn’t like … truly.
A few numbers around the 65,535 and the 65,536 mark will 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.
Some Excel 2007 calculations with a result in the range:
65,534.99999999995 to 65,535
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.
The online version of this article has a link to the worksheet we used to test this bug – Excel 2007 users can download our test worksheet and see for themselves.
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 minimise 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. But 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.
What will happen
As you’d hope, Microsoft is working on a patch. While customers are entitled to expect a quick fix, keep in mind that Excel is an incredibly complex program and a ‘simple’ fix can have unexpected consequences. The clever people on the Excel team need to fix the problem but not make things worse.
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?
Naturally we’ll be keeping an eye on this and reporting in the Office Watch newsletter and online at http://news.office-watch.com
Article posted: Friday, 28 September 2007
there's more ...
If you liked this article you'll LOVE our new ebooks.
Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8
A focused and unvarnished look at Windows 8, especially written for
the many people who use Microsoft Office Get it today
- click here.
ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox
100+ pages of practical tips and help to streamline,
automate and search your Inbox. Get more
than you ever thought possible from Outlook. Read it today
- click here.
More from Office Watch: