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
Could you please tell me what Bank is using Excel 2007?
I would like to deposit 850 times the amount of 77.1$ and then withdraw 100K
Thanks
When Excel fix this will the ordinary Joe Blow user such as myself be sent the fix automagically?
probably you can loan 77.1$, 850 times from the bank.
I haven't read the whole thread yet, but I'd like to add a point about OpenOffice: OpenOffice uses wrappers around floating-point operations to correct some round-off errors. That's why it appears to be "correct", but in fact, you should be aware that this is not reliable and can hide real user-side bugs. There has been a discussion about this in the French users mailing-list in February/March 2005 (thread "[users-fr] [CALC] Calculs imprécis").
@AdamV
That was cut and paste, but I must say it changed after I saved and re-opened the file (prompted by your post). Strange.
Emmh, I like it how light hearted this issue is taken but MS is charging $650 for this *COMMERCIAL* suite, can we expect a refund?
Neil Rubenking's (of PC Magazine programming fame) analysis:
"If it were just 850*77.1 that gave a wrong answer, we could probably work around that. But there are tons of other problem numbers, as I discovered for myself. I set up a spreadsheet to divide 65,535 by every number from 1 to 65,535 itself, then multiply the number by that result. So, for example, it divided 65,535 by 26 to get 2,520.577. Then it multiplied 26 by 2,520.577 to get...
100,000?! Over ten thousand of these simple calculations gave the wrong answer."
www.computerworld.com/.../6254
To: ***...
# re: Calculation Issue Update
> Saturday, September 29, 2007 8:59 PM by ***...
>
> For all the Open Office posts
> If Open Office is so great why do you read this blog ?
> ***...
The reason is very simple, the same as you look for cartoons and comics @newspapers ... to have fun laughing !
***... enjoy Excel, 'because you're worth it'
I don't know why everyone is making big deal about it. It depends on how often you do calculation of 77.1*850 and I highly doubt it'll be used other than this comment section. I recommend to use the previous version of Excel. Easy solution~
Its not the single calculation of 77.1*850. If you take 65535, divide by each integer between 1 and 65535, take that result, multiply it against the original divisor, you will get 10,023 wrong calculations out of 65535. over 15% of the solutions.
If you commonly round your numbers off, like many financial companies do in their spreadsheets, those become a REAL value of 100,000 , not the 65535 MS says is residing in memory.
cleve is right, but take more simple case. You buy some items of 38.55 each, for your 34 offices over the region e.g. 50 pcs for each office. If you put formula (=product(..)) in excel 2007 you will get folloving result. There is also a result for 51 pcs ...
Price/item 38.55 38.55
Offices 34 34
Items per office 50 51
Total 100 000.00 66 845.70
It means you can get 77.1*850 in various ways and it could happen more frequently then a meteor strikes ...
Ok, so the inaccuracy of floating point numbers has been discussed to death but, if this is simply a display issue, I'd be interested to see some technical description of where the display formatting goes wrong.
Hi to all, I don't know if you notice that the number 65,535 is the some number of lines as excel 2003 have.
Its NOT just a display issue. Export your data to a CSV file (which many companies do for batch processing on Main frames etc...). The error is sent out as real data, not put back to 65535 like is claimed.
also do any calculation of any of the combinations that produce the error and round the answer. Again it locks in the wrong answer. that IS a calculation error.
I think MS is just being very wary of admitting a calculation error, when there are so many people out there just waiting to claim a mistake based on an "Admitted" calculation error in the software.
I need by Microsoft a workaround proposal. In detail how should a user verify, that the values are correct.
this "workaround is needed until the bugfix is available. It makes no sense to design a workaround myself (i cant ensure that my solution would be 100% correct - this has to be done by the owner of the product: Microsoft.
Comments: (loading) Collapse