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
Here's the quick test i made yesterday :
Put 65535 on a row ...
Divide it by 1 then 1.1 then 1.2 etc etc
Multiply it back by 1 1.1 1.2 etc etc
Round it so to know how many "display bug" we have
Go up to say 10000 for ex ....
65535 998 65,66633267 65535 65535 0
65535 998,1 65,65975353 65535 65535 0
65535 998,2 65,65317572 65535 65535 0
65535 998,3 65,64659922 65535 65535 0
65535 998,4 65,64002404 65535 65535 0
65535 998,5 65,63345018 100000 100000 1
65535 998,6 65,62687763 100000 100000 1
65535 998,7 65,6203064 65535 65535 0
65535 998,8 65,61373648 65535 65535 0
65535 998,9 65,60716788 65535 65535 0
65535 999 65,6006006 100000 100000 1
65535 999,1 65,59403463 65535 65535 0
65535 999,2 65,58746998 65535 65535 0
65535 999,3 65,58090663 65535 65535 0
65535 999,4 65,57434461 100000 100000 1
65535 999,5 65,56778389 65535 65535 0
65535 999,6 65,56122449 65535 65535 0
65535 999,7 65,5546664 65535 65535 0
65535 999,8 65,54810962 65535 65535 0
65535 999,9 65,54155416 65535 65535 0
65535 1000 65,535 65535 65535 0
1436
I get to the "Magic" 6 65534.99999999995 to 65535 numbers more than 14% of the time ?????
Oh well ....
Back to 2003 until the patch ,(
Let's Rock GUYS!!!!
Give us a fix and we will be happy!
Any bad word... any worry... any... any....
don't think about it...
Work hard...
give us the fix...
And thanks for your work! : )))
Ale from Milano
: )
That's why most of us are staying with Office 2000 and Windows XP.
Remember my boss was so mad when his wireless keyboard missed a key stroke. One hundred thousand became ten thousand could be a very serious mistake.
If you send out an Excel order form to order 850 pieces of $77.1 parts. The recipient open it with Excel 2007 and charge you $100,000. This is no fun.
A1 850
A2 77,1
A3 =(A1*10)*A2/10
Result 65536
A4 =(A1*10)*(A2/10)
Result 100000
Maybe this helps you.
Bye
guys, these is serious matter for us as power users of excel next year. When is solution coming?
Assuming B1 as the bad calculation, we got this:
A B C D E
1 65534 100000 100001 65537 65538
-> B1-1 B1+1 C1+1 D1+1
Ok but if my bank used excel to calcule something about my money and I lost $ can I ask a refund to Microsoft??????? It´s absolutely uncorrect!! Fight Microsoft!!
If your bank is using EXCEL 2007 to calculate something about your money... I suggest you to change bank! : )))))
Microsoft explained the bug as a display problem. Is it true? Unfortunately, it's definately a calculation problem.
Try this:
A1=round(850*77.1,1)
then all the calculation error with the A1 will be permanent. It's very serious problem, e.g.
A2=A1+2, as they explained the 850*77.1+2 should be 65537.
ROUND and "set precision as displayed" will both give rise to an actual incorrect value.
I guess the code for both are predicated on the same algorithm for converting from the floating point format.
ROUNDUP and ROUNDDOWN seem unaffected, though
veroblog.wordpress.com/.../excel-2007-calculation-bug-displays-apparently-wrong-numbers
same URL made tiny: http://tinyurl.com/3xweb5
"If your bank is using EXCEL 2007 to calculate something about your money... I suggest you to change bank! : )))))"
Since MS is forcing everyone to switch to 2007 by ceasing support for older versions of office in the coming year(s), all major corporations are having to plan and test for the switch this year. Meaning that there are many branches in testing now.
If you don't think that every bank branch in America is using Excel at their desks to look at data, process data, prepare the files for uploading to batch process overnight, Your are in a dreamland and I really hope you never realize how deeply MS is embedded in the financial institutions, you might have a heart attack.
Enquanto eu puder piratear o Excel, vocês podem demorar o tempo que quiserem pra liberar o patch....
Any chance to know when the fix will be released?
I'm not looking for precise date, but are we talking about a few days or weeks?
and if you try with OpenOffice?
OOo, iWork Numbers, and a few others that I have heard tested in the past few days are fine.
this is specific to MS Excel.