Chris Posted September 26, 2007 Report Share Posted September 26, 2007 Calculation Issue UpdateYesterday 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.More | Here :0 Quote Link to comment Share on other sites More sharing options...
Irene Posted September 27, 2007 Report Share Posted September 27, 2007 Ooopps ... I'm not likely to encounter problems with that calculation on my bank account! :lol: Quote Link to comment Share on other sites More sharing options...
expertec Posted September 27, 2007 Report Share Posted September 27, 2007 Goddamn crappy Excel... ruined my plan for world domination. :lol: Quote Link to comment Share on other sites More sharing options...
Scarecrow Man Posted September 27, 2007 Report Share Posted September 27, 2007 Calc doesn't have this problem... :)Of course, there was always something fishy with 66535. It's the "highest number which can be represented by an unsigned 16 bit binary number." (Wikipedia)A strange number this one is. I think this is not the only problem we're going to see with it. Quote Link to comment Share on other sites More sharing options...
Irene Posted September 27, 2007 Report Share Posted September 27, 2007 Calc doesn't have this problem... :)Of course, there was always something fishy with 66535. It's the "highest number which can be represented by an unsigned 16 bit binary number." (Wikipedia)A strange number this one is. I think this is not the only problem we're going to see with it. ... fascinating... If I could understand it!! Quote Link to comment Share on other sites More sharing options...
ɹəuəllıʍ ʇɐb Posted October 2, 2007 Report Share Posted October 2, 2007 ... fascinating... If I could understand it!! That Wiki article has a rather stupid way of "explaining" things.It's much more clear if you look at it this waydecimal 65535 = binary |1111111111111111| = hexadecimal |FFFF|If you have a 16-bit word containing the above, then add 1, then the word overflows (see below) and should trigger an error. If for some reason the error is not triggered, then the result in that word is 0.The 16-bit word after the overflow (the blue vertical bar indicating the boundaries of the word)binary 1|0000000000000000| = hexadecimal 1|0000| Quote Link to comment Share on other sites More sharing options...
Chris Posted October 10, 2007 Author Report Share Posted October 10, 2007 Calculation Issue Update (Fix Available) Quote Link to comment Share on other sites More sharing options...
WExcelN Posted July 21, 2008 Report Share Posted July 21, 2008 Hi thereI've just stumbled on a strange excel calculation error in a simple summing formula:If you sum a column with the following figures which should add up to absolute zero but in Excel it comes to -0.00000000000000444089209850063. 166.1-81.8-60.6-3.7I've checked this on three machines and the error keeps occuringWhat is going on?? Quote Link to comment Share on other sites More sharing options...
WExcelN Posted July 21, 2008 Report Share Posted July 21, 2008 Sorry a typo in the first post.. the numbers should be in sequence:116.1-81.8-60.6-3.7We have noticed that the error disappears if you change the sequence.Hi thereI've just stumbled on a strange excel calculation error in a simple summing formula:If you sum a column with the following figures which should add up to absolute zero but in Excel it comes to -0.00000000000000444089209850063. 166.1-81.8-60.6-3.7I've checked this on three machines and the error keeps occuringWhat is going on?? Quote Link to comment Share on other sites More sharing options...
ɹəuəllıʍ ʇɐb Posted July 23, 2008 Report Share Posted July 23, 2008 Welcome to the Windows Forum.I have not very much experience with Excel, so I cannot give you a definitive answer. But from what you describe, it appears as if Excel is using floating point to calculate numbers. Floating point, by its nature, cannot be 100% accurate as soon as you use any fractions. You will very often get very slight differences to the result that you'd expect. And yes, if you change the sequence, the result may also slightly change.I don't know if Excel has a 'rounding' function, but this is usually what I use (in other programs that use floating point) to round the results off.P.S. what version of Excel do you use? Quote Link to comment Share on other sites More sharing options...
WExcelN Posted July 25, 2008 Report Share Posted July 25, 2008 Welcome to the Windows Forum.I have not very much experience with Excel, so I cannot give you a definitive answer. But from what you describe, it appears as if Excel is using floating point to calculate numbers. Floating point, by its nature, cannot be 100% accurate as soon as you use any fractions. You will very often get very slight differences to the result that you'd expect. And yes, if you change the sequence, the result may also slightly change.I don't know if Excel has a 'rounding' function, but this is usually what I use (in other programs that use floating point) to round the results off.P.S. what version of Excel do you use?Hi thereI suspect your right and it is a floating point issue. We are using Excel 2003 (11.82211.8202) SP3. I've used Excel for years and this is the first time I've ever come across this. Some of the spreadsheets I use are very large with hundreds of thousands (if not millions) of iterations...I wonder if and how much this "bug" is affecting the results? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.