Yesterday we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The Excel team would like to provide a description of the issue and explain what we're doing about it.
BackgroundYesterday evening we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The first example that we heard about was =77.1*850, but it became clear from our testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535. The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but our testing showed that this really didn't have anything do to with multiplication - it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance). Further testing showed a similar phenomenon with 65,536 as well. This issue only exists in Excel 2007, not previous versions.
The ProblemThis 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).
So what, specifically, are the values that cause this display problem? Of the 9.214*10^18 different floating point numbers (floating point on wikipedia) that Excel 2007 can store, there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem. You can’t actually enter these numbers into Excel directly (since Excel will round to 15 digits on entry), but any calculation returning one of those results will display this issue if the results of the calculation are displayed in a cell. All other calculation results are not affected.
The SolutionWe take calculation in Excel very seriously and we do everything we can in order to ensure that calculation is correct for all cases. We’ve come up with a fix for this issue and are in the final phases of a broad test pass in order to ensure that the fix works and doesn’t introduce any additional issues - especially any other calculation issues. This fix then needs to make its way through our official build lab and onto a download site - which we expect to happen very soon. We’ll add another post once that’s taken place with a link to the download.
PingBack from http://www.artofbam.com/wordpress/?p=2948
David, your explanation of the source of the problem is not clear. You are emphasizing that Excel is performing the calculation properly, but not explaining what component of Excel that is causing the results to be displayed incorrectly.
If there is a problem in the presentation or data translation layer of Excel then elaborate on those aspects, as your current explanation gets murky with all the discussions of floating point math and floating point value representation.
Your explanation won't make sense to any top business executive who relies on Excel in their organization, and can be swayed by positive press from competing products.
Due to the PR issue this has caused your company will need to advertise the download and emphasize the importance to users to ensure they download the update. Otherwise this issue will resurface infrequently, generating more bad press.
You should also review your quality assurance testing process over the presentation portion of Excel. Perhaps it is time to reorganize the policies and procedures of the responsible QA team.
So what, specifically, are the values that cause this display problem? Of the 9.214*10^18 different floating point numbers (floating point...
Thanks for the fast response and the open explanation David.
Would I be right in thinking part of how this got through QA is that its a presentation issue rather than an underlying value thing, so any value-based test will pass?
drewbc - sorry, I was trying to walk the line between not too much detail and too much detail.
drewbc and simon - this is an issue in a function that puts numbers in cells, so the values in Excel's memory are actually correct. Imagine A1 contains =77.1*850 ... Excel actually calculates the correct answer, and you can see that if you use VBA to check the value for A1 - it will be 65535. But in the function that takes that value and formats it to be displayed on the screen, for the values described above, there is a bug. Any calculations based off that cell will be accurate too. Hope that helps.
This same calculation issue affects Excel Services, and is being addressed concurrently. http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.asp
David,
You said "Any calculations based off that cell will be accurate too. Hope that helps."
But that's not entirely correct. At least not from what I have seen. If you happen to be rounding your calculations (=ROUND(850*77.1,2)*2), it also rounds it to 100K making that permanent.
So, while it is true that most cases Excel treats the value as correct except for the visual side of it, in others it actually DOES treat it as 100K.
Rob
Dave, thats what I thought - I've some VBA code on my blog to test the .text and .value2 of formula cells looking for this issue. some numberformats break it though.
Gisteren had ik er al over ge3schreven en nu komt het Excel team met een reactie: Yesterday we were...
"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)."
This is not always correct. In your example, put =A1+1 in cell A2. It will not return 65,536, but rather 100,001. If you put the calculatoin in a table (just a row and columns of numbers, not an Excel "Table" and then put a filter on it, the fitler will see those erronious values as 100,000, not 65,535.
A1 =850*77.1 --> 100000
B1 =A1+1 --> 100001 (realy a display bug???)
C1 =A1-1 --> 65534
D1 =B1-C1 --> 2
About the ROUND() and A1+1 - didn't it occur to any of you, that these operations actually produce another one of the "bad" values? ;)
Try A1+2 or A1+3.
Nope. It's not a "display bug".
"+1" gives 100001
"-1" gives 65534
Just to add to the oddity...
12*5461,33333333333 = 100001 :-)
The test "D1 =B1-C1 --> 2" above is consistent with a display bug since C1 = 65534
so B1 must be equal to 65536 (which, due to the display bug) according to the posted, is shown as
100001