Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Excel’s problem with 65,535 & 65,536

Excel 2007 has a serious bug where some numbers display as " 100,000 " regardless of the true value.

by Office Watch

Bookmark and Share

  | Mobile | click for more article services     


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.

What happens

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.

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.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

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:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» OneNote for Windows updated
» Office for iPhone v1.2 secrecy
» Hanx Writer and Office for iPad
» Windows 8.1 update disappoints
» Adding the Ruble to Office
» Why PivotTables get confused and how to fix them


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).