Excel 2007 Number Bug

The following is a quote from "OW" newsletter:

1. EXCEL'S PROBLEM WITH 65,535 & 65,536
See this article online with sample worksheet at http://news.office-watch.com?556

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.


2. 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.

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.
________

Comments

  • Just to let users know that statistiXL won't be affected by this bug as 1) it doesn't use Excel to perform calculations and 2) it doesn't display results as calculations, simply as values (which display correctly).

    Cheers

    Alan
Sign In or Register to comment.