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
Just an interesting observation.
The last row number in an Excel Spreadsheet (looking at Office XP, at least) is row number 65535.
Just an interesting observation (correction).
The last row number in an Excel Spreadsheet (looking at Office XP, at least) is row number 65536.
George Mack - doesn't that only matter if you are referencing things prior to that date? I should think that the vast majority of modern spreadsheet use does not utilize dated data from 1900. Besides, I've always worked under the assumption that time didn't really exist prior to 1971, so I've just ignored it. :)
I guess any discussion of a bug in Excel that goes on long enough is bound to bring up the 2/29/1900 feature. For those who do not know: this was done on purpose for backward compatibility with older non-Microsoft software.
David, maybe it was just infortuitous timing, but this trollfest makes me sorry I suggested leaving comments open.
That's interesting, I haven't upgraded cause I don't use it too often, and have Excel 97 sr2. I tried a number of these examples, and all of them resulted in 65535, I'm seeing this error in Excel 97 sr2.
It's fun to see such smart people and such ignorant people discuss things! Kind of like science vs. religion stuff... and elephants vs. donkeys. Please can anyone tell me what 2^16 is?
The "problem number" 65536 happens to be the maximum number of rows in an Excel spreadsheet. Somewhere in the Excel code, there's obviously a procedure that tests for values between 65535 and 65536, to trap out of bounds errors. A problem might arise if that subroutine is being unnecessarily called to evaluate formula results.
OK here's another crazy Excel error:
A B C
=A2-B2
4.1 4 0.09999999999999960000
5.1 5 0.09999999999999960000
6.1 6 0.09999999999999960000
7.1 7 0.09999999999999960000
8.1 8 0.09999999999999960000
9.1 9 0.09999999999999960000
10.1 10 0.09999999999999960000
11.1 11 0.09999999999999960000
12.1 12 0.09999999999999960000
13.1 13 0.09999999999999960000
14.1 14 0.09999999999999960000
15.1 15 0.09999999999999960000
16.1 16 0.10000000000000100000
17.1 17 0.10000000000000100000
18.1 18 0.10000000000000100000
19.1 19 0.10000000000000100000
20.1 20 0.10000000000000100000
21.1 21 0.10000000000000100000
...
I found this in Excel 2003. You will nead to 'increase decimal' to see the significant figures. I would be interested to know if any one else has found the same errors. My guess is in how excel stores the numbers, as in a short, int, float, long, or double. And the loss of precision in the calculation.
This does NOT appear to be just a display problem. when I then used those numbers in other formulas the strangeness continued. Try =B2*C2 to see what I mean.
also:
4.1 3 1.10000000000000000000
5.1 4 1.10000000000000000000
6.1 5 1.10000000000000000000
7.1 6 1.10000000000000000000
8.1 7 1.10000000000000000000
9.1 8 1.10000000000000000000
10.1 9 1.10000000000000000000
11.1 10 1.10000000000000000000
12.1 11 1.10000000000000000000
all come out correct. Interesting...
The previous post didn't format the "columns" correctly.
in Excel
4.1 4 =A2-B2
5.1 5 =A3-B3
autofill in those downward to see what I mean.
Thanks
OK I just found another level of strangeness.
the pattern of error changes at every 4^n.
4.1
16.1
64.1
256.1
1024.1
4096.1
1.1 1 0.10000000000000000000
2.1 2 0.10000000000000000000
3.1 3 0.10000000000000000000
63.1 63 0.10000000000000100000
64.1 64 0.09999999999999430000
255.1 255 0.09999999999999430000
256.1 256 0.10000000000002300000
1023.1 1023 0.10000000000002300000
1024.1 1024 0.09999999999990910000
4095.1 4095 0.09999999999990910000
4096.1 4096 0.10000000000036400000
I didn't go beyond 4096.1, but I think I can see a pattern here...
Leap years cause almost as many problems as floating point calculations. This one is not an "error" in Excel, so much as a historical error for compatibility purposes.
Lotus 1-2-3 incorrectly thought 1900 was a leap year, and spreadsheets using the "1900 year system" will, intentionally, consider 2/29/1900 a valid date.
The Macintosh version of Excel offers a choice (under Preferences->Calculation) to set a 1900 or a 1904 year system. The reason for this was that older Macs would only support dates after 1/1/1904 (because of the Lotus-established confusion over the year 1900) - Apple's way of dealing with this compatibility issue.
Windows versions of Excel, living in the PC World, maintained Lotus compatibility regarding dates.
Yes, it's a bug and a confusion, but a societal one, not an Microsoft Excel one. How to maintain awareness of the murkiness around such date calculations on computers is also a societal problem.
See support.microsoft.com/.../180162 for MS's documentation of this issue.
This bug is unacceptable. Many small businesses rely on excel for financial reporting, invoicing etc. I found it suprising that microsoft didn't even put it on the front page and finding it in support or the knowledge base was pathetic. Shame on you microsoft.... You want to move us to the latest products but now I have decided to roll my company back to office 2003 and will keep them there. I have also decided to cancel the vista roll out due to this. Looks like xp for years to come.....
another one for u guys to think abt:
65534.9999 * 0.9999999999 = 0.0000000000000
I can't wade through all 140 comments.. but - is there any correlation between the 65535 number and the amount of possible ports a system has? 0-65535 (if you count zero as a number - 65536?) Could this be an OS/app interaction thing? Seems like that's a pretty "famous" number, eh?
@aah!
Yes, 65,536 is 2^16, that's why this number crops up in these various unrelated fields - it is the largest number of values or states that can be represented by two bytes
(For nitpickers - I mean two bytes can have 65,536 different values, including 0. The largest of these values is of course 65,535)
@Jamie Baranec
I'd be interested to see your tests.
If I have this:
A1=850*77.1
A2=A1
A3=AVERAGE(A1:A2)
then A3 displays 100,000 - not because it has calculated that the average is one hundred thousand, but the result has the same display bug.
If I change A2=A1+1 this displays 100,001 (the second bug region). Now the average correctly displays as 65,535.5
If I fill down A2 to (say) A10 so they show the sequence 100,000; 100,001; 65,537, 65,538 etc. then the average of any range of more than two of these is accurate and as expected.
Same goes for MIN and MAX - if the cell with the bug in is the one which the MIN or MAX function picks out then the result of the MIN or MAX will have the display error. It does not alter the value so it suffers from exactly the same problem.
Try the numbers 65,534; 850*77.1 and 99,000
The MAX of these will be 99,000, the MIN will be 65,534. If you change the 65,534 for 65,535, then the MIN will show 100,000 since it pulls the inaccurate answer to (850*77.1) which is infinitessimally less than 65,535, and then displays it incorrectly.
Adding 2 to any of these AVERAGE, MIN or MAX results demonstrates that the answer they hold is mathematically consistent and stored correctly (ie 'very near to' 65,535 or 65,536), and suffers exactly the same display bug.
Comments: (loading) Collapse