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.
Tips
How-to
News
Videos
Stories
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.
Comments: (205) Collapse
With all the talk of floating point accuracy and the seriousness with which Excel takes it have a glance at these reports that show Gnumeric as a spreadsheet of choice if you take accuracy seriously.
www.csdassn.org/software_reports.cfm
I don't know if they have been updated for the latest Excel.
I've found the fix to the Excel bug. You can download it here:
download.openoffice.org/.../index.html
To David Gainer:
Can you comment on the posting by Brian H. on September 26, 6:58 AM, that I have appended, in which he indicates there is instance of where the display representation seemed incorrect, for a value much different than 65535 or 65536? In particular, he indicates that he gets:
12*5461,33333333333 = 100001
Thanks and regards.
Can posters please exercise some logical checking
12*5461.33333333333 = 65535.99996 -> i.e. 65536
The poster was probably in Europe when a "," is used for a "." .. or simply a typo of the poster.
JPDeyst : before asking for David to reply, did you check if you get the same result ?
I'm curious about how this bug was generated. I am baffled because .NET applications can perform this calculation perfectly.
Unless you used VB.net instead of C#
Anonymous:
Thanks for the explanation. Now I get it; Brian H. was having some fun at the expense of those of us not well versed in using comma delimiters.
No I don't have Excel 2007 at my work place.
-JPDeyst
So if you just mutiple eveything by 1 it will work...
what is so wrong with that?
I think that I have found a second related bug that is in both the 2003 version of Excel and the 2007 version. Namely:
If you key in the following:
=DEC2HEX(2^16), it correctly gives 10000 (hex)
=DEC2HEX(2^16-1), it correctly gives FFFF (hex)
=DEC2HEX(65535), it correctly gives FFFF (hex)
=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)
Hi,
I have created Excel Addin in 2003. I have converted it to Excel 2007 Addin , but it contain more no of rows and columns as compared to excel 2007. I would like to preserve same no of rows and columns for Addin in Excel 2007. Is there any solution for it ???
Please reply.
Thanks & regards
-Mangesh
Hi, Few changes in first comment.
I have created Excel Addin in 2003. I have converted it to Excel 2007 Addin , but it contain more no of rows and columns as compared to excel 2003. I would like to preserve same no of rows and columns for Addin in Excel 2007. Is there any solution for it ???
Isn’t it a coincidence that 65,536 is the number of rows available in Excel 2003. Conversion issue?
@Scooter
Nothing to do with it at all. What would conversion have to do with anything anyway? The bug appears on any new spreadsheet in native Excel 2007.
It just happens that 65,536 = 2^16, so it is the largest numnber which can be represented in two bytes, and seemed a reasonable way to save file space by not wasting lots of memory remmembering low row numbers when the large ones may never have been used.
It was a reasonable limit for Excel given the power of the machines available at the time to calculate large sheets (arguably the move to over 1M rows is now arriving a few years later than it could have done).
Check your graphics card settings - do you have choices for 256 colours, then 65,536, then 16.1 Million? Does that 65,536 have anything to do with this Excel bug? Clearly not, this is simply 2^8, 2^16, 2^24 expressed in decimal.
@wlandrum : You say
"=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)"
850*77.1 returns 65534.9999999994321564 (which is acceptable for floating point math as seen in prior postings in this thread) -- so DEC2HEX( ) seems to "round down", and thus gives the CORRECT answer FFFE (=65534).
while 65534.9999999994321564 is a PERFECTLY acceptable value for 850*77.1, FFFE is in no way acceptable for DEC2HEX(850*77.1). It is plain WRONG. No matter how you look at the problem.
(BTW. OpenOffice properly displays FFFF for this)
@mihaimm :
Since you agree "65534.9999999994321564" is a PERFECTLY acceptable value for 850*77.1"
then what would you expect
DEC2HEX(65534.9999999994321564) to return if it is defined as rounding down ?
Surely FFFE. Therefore DEC2HEX(850*77.1) must return FFFE
Comments: (loading) Collapse