You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
Regarding x = 850 * 77.1 // 65534.999999999993
This seems link normal floating point rounding, and present in all floating point math.
x = 850 * 77.1
and execute it, the result is
Now, that is not unexpected.
You can't really convert 77.1 into binary *precisely* (it's a periodical binary fraction), so there will be a rounding error. Which will be increased when multiplying by 850.
Normally when displaying, you cut off the last digit or two while rounding the last remaining, to not show the error, and it gives an illusion of integer numbers as results...
Will this fix be pushed via Windows Update? Or will explicit download and installation be required?
This is not the case. Any calculation made with a direct result of the 65535 results in a correctly displayed value. So, this was David's original point.
However, that breaks when you add "external" elements to the calculation such as rounding. In this case, the display value of 100K that behaves like 65535 except for the visual part, now effectively becomes 100K. THAT, to me, is much more dangerous because if you end up in a transition number that you have rounded, it will become 100K and everything else will be based on this new number!
It will be pushed via Microsoft Update, which is part of Windows Update for Microsoft Office users.
I don't see how 850 x (the rounding error you are describing) = (100,000 - 655534.xxxxx)
To me the idea that the value in memory is correct (for most instances where I see Excel used) is meaningless. It's normal users who are making determinations from what they see in the cell, and what they see is wrong. Most of the users I work with do not know how to get to the actual value in memory and would look at me as if I had two heads if I made that argument to them. The number is wrong and that is all there is to it.
You say that only the "graphical" representation is wrong. You say that the number is internally correct.
A:1 cell value 850
B:1 cell value 77.1
C:1 cell formula =A:1*B:1 gives 100000
D:1 cell formula =C:1+1 gives 100001
E:1 cell formula =A:1*B:1+1 gives 100001
PLUS the excel is so arrogant as to point out that E:1 formula is "inconsistent"
Above is consistent with a display issue once you realise 65535 is displayed as 100000 and 65536 is displayed as 100001
As others have suggested, add +2 or +3
Also do not forget that many people copy and paste values into another sheet for batch processing in many financial institutions.
This error is carried over in those cases. and will post as 100,000 in a CSV file as well. so it is NOT a display error only.
I think the key of this bug maybe the number 17 and at least one decimal ended without "5".
the detail description was written in Chinese here:
"Question: When does 850 * 77.1 not equal 65,535? Answer: When you're using Excel 2007. A bug in Excel."
Answer: When you are dealing with infinite binary fractions, 77.1 can only be approximated. So multiplication just increases this error. Normally you don't see it because last 1-2 digits are thrown off and the rest is rounded and you see precise result...
But the bug is in Excel all right.
I thought i'd spent my Google Adwords budget for the month but i've actually got $34,465 left!
Hi folks, thanks for all the comments.
Couple of points. First, I want to reiterate that we take calculation in Excel very seriously and we are working hard to produce a fix. I made the distinction between display and calculated value in an effort to explain the bug to the community, not to suggest the bug was less severe.
Second, the reason you need to add 2 in my example above is because adding one just gets you from a value near 65534.99999999995 to a value near 65535.99999999995, which is still problematic.
Third, we will let everyone know how to get the fix as soon as it is available.
Thanks for the update, Dave. Now that we've beaten this issue to death, hopefully we can move on to other things and I can de-clutter my Inbox of all the automated messages this darn discussion has generated :-)