Excel says: 850 * 77.1 is 100000



  • Just read an interesting article on Slashdot - apparently, Excel 2007 reckons that 850 * 77.1 is 100000, not 65535 like everyone else. Unfortunately, I'm still using 2003, so I can't test it myself.



  • Tested it, and it's true

    It's also true the if that's in A1, then =A1-1 =65534
     



  • The bug seems to exist, if a multiplication result is 65535 and one of the multiplicators? is a float with an infinite binary expression (according to IEE 754).
    ie. 212.5308.4 will produce the bug but 252621.4 will not, since 2621.4 has a finite expression.



  • This IS worse than failure!



  • @Thief^ said:

    Tested it, and it's true

    It's also true the if that's in A1, then =A1-1 =65534
     

    And =A1+1 =100001 



  • Digg claims this to cripple users. http://www.digg.com/microsoft/Critical_Excel_2007_bug_cripples_users

    It may be a lapse, and an oversight, but I thought it was a bit inflamatory that a bug that occurs very rarely, in an app with low uptake, and low crticial value can be considered to cripple users. It's not like banks are using Excel for their transactions.... are they?

     Just don't buy anything that costs 77.10$ until they fixed it, ok?



  • @yet another Matt said:

    Digg claims this to cripple users. http://www.digg.com/microsoft/Critical_Excel_2007_bug_cripples_users

    It may be a lapse, and an oversight, but I thought it was a bit inflamatory that a bug that occurs very rarely, in an app with low uptake, and low crticial value can be considered to cripple users. It's not like banks are using Excel for their transactions.... are they?

     Just don't buy anything that costs 77.10$ until they fixed it, ok?

    I've worked on Wall Street for ~20 years, and have frequently seen end users (traders, brokers, and yes, bankers) create horrifically complex excel spreadsheets to maintain their data (including real time pricing feeds, trades,etc). Then they save it to their local disk and complain when it can't be restored when they accidentally delete the spreadsheet file. I've also seen them load the entire DB into their spreadsheet, perform transactions all day, and save it back to the DB at the end of the day. "Don't worry, I don't need you to build me an application; we have Excel!"

    Be afraid.

     



  • @snoofle said:

    @yet another Matt said:

    Digg claims this to cripple users. http://www.digg.com/microsoft/Critical_Excel_2007_bug_cripples_users

    It may be a lapse, and an oversight, but I thought it was a bit inflamatory that a bug that occurs very rarely, in an app with low uptake, and low crticial value can be considered to cripple users. It's not like banks are using Excel for their transactions.... are they?

     Just don't buy anything that costs 77.10$ until they fixed it, ok?

    I've worked on Wall Street for ~20 years, and have frequently seen end users (traders, brokers, and yes, bankers) create horrifically complex excel spreadsheets to maintain their data (including real time pricing feeds, trades,etc). Then they save it to their local disk and complain when it can't be restored when they accidentally delete the spreadsheet file. I've also seen them load the entire DB into their spreadsheet, perform transactions all day, and save it back to the DB at the end of the day. "Don't worry, I don't need you to build me an application; we have Excel!"

    Be afraid.

     

    Personally, I'm going to ask for a pay raise to $65.535/year.



  • @XIU said:

    @Thief^ said:

    Tested it, and it's true

    It's also true the if that's in A1, then =A1-1 =65534
     

    And =A1+1 =100001 

     Ah... but =A1+2  =65537 which is also disturbing.



  • I love this, the programes main reason for being is to add up, and it can't!



  • @yet another Matt said:

    Digg claims this to cripple users. http://www.digg.com/microsoft/Critical_Excel_2007_bug_cripples_users

    It may be a lapse, and an oversight, but I thought it was a bit inflamatory that a bug that occurs very rarely, in an app with low uptake, and low crticial value can be considered to cripple users. It's not like banks are using Excel for their transactions.... are they?

     Just don't buy anything that costs 77.10$ until they fixed it, ok?

    I work for a company that creates an Excel add-in for pricing financial derivatives.  Many banks, corporations, and governments are making real, financially important decisions based on Excel spreadsheets.  Many of those spreadsheets are listed in 1000's of $ or even millions, so 77.1 might refer to $77,100 or even $77.1 million.

     Yes, this is pretty damned serious.
     



  • The result of =850*7.71 [by whatever floating point mechanism excel uses] is actually exactly 65534.9999999999927240423858165740966796875.

    or, rather, 65535−2−27. This value is evidently somehow mapped to 100000 by whatever excel 2007 is using to convert values to strings.



  • @Grimoire said:

    I work for a company that creates an Excel add-in for pricing financial derivatives.  Many banks, corporations, and governments are making real, financially important decisions based on Excel spreadsheets.  Many of those spreadsheets are listed in 1000's of $ or even millions, so 77.1 might refer to $77,100 or even $77.1 million.

    Yes, this is pretty damned serious.

    Um, having a spreadsheet that's in millions is pretty stupid. You can have it display in millions while having the actual number represent dollars by adding two commas after the zeroes in the number format string

    (of course, this is a formatting bug, so it may still be a problem)



  • @Random832 said:

    @Grimoire said:

    I work for a company that creates an Excel add-in for pricing financial derivatives.  Many banks, corporations, and governments are making real, financially important decisions based on Excel spreadsheets.  Many of those spreadsheets are listed in 1000's of $ or even millions, so 77.1 might refer to $77,100 or even $77.1 million.

    Yes, this is pretty damned serious.

    Um, having a spreadsheet that's in millions is pretty stupid. You can have it display in millions while having the actual number represent dollars by adding two commas after the zeroes in the number format string

    (of course, this is a formatting bug, so it may still be a problem)

    I've seen some pretty stupid spreadsheets.  Having millions may be stupid to you, but compared to some of the crap I've seen, it's very minor.  I've seen real time trading systems designed using Excel spreadsheets.

    Regardless of the intelligence of calculating in millions of $ instead of $, 850 * 77.1 should never be shown as 100000.
     



  • I hate to say it, but this is extremely corner case-ish,

     checking on the .Text of the cell when there is a multiplication of a float to get a number close to blah blah blah, and It's not amazing that a test didn't exist for it.  It's unfortunate and funny, but I can't really fault MS QA for missing it.
     



  • Very strange indeed.


    The real WTF is that it even gets =65535*1 wrong :P



  • @tster said:

    I hate to say it, but this is extremely corner case-ish,

     checking on the .Text of the cell when there is a multiplication of a float to get a number close to blah blah blah, and It's not amazing that a test didn't exist for it.  It's unfortunate and funny, but I can't really fault MS QA for missing it.
     

    It doesn't break in Excel 2000. It (presumably) doesn't break in 2003. WTF happened that they needed to change the code for general formatting of numbers? 



  • Ad placement of the day

    Spotted on Slashdot just now... 

    Excel bug vs. Windows Stock Market ad

    BTW first post, hello all. 



  • I gave this a try on Excel 2007 at home and I found out something pretty neat:

    If you set A1 =850*77.1 and B1 = 100000, then set a cell to =(A1 = B1). It returns TRUE. If it were just a formatting bug I wouldn't expect this to be the case. I bet you could get similarly strange results with other comparison operations.



  • @Welbog said:

    I gave this a try on Excel 2007 at home and I found out something pretty neat:

    If you set A1 =850*77.1 and B1 = 100000, then set a cell to =(A1 = B1). It returns TRUE. If it were just a formatting bug I wouldn't expect this to be the case. I bet you could get similarly strange results with other comparison operations.

    Unless it formats and then compares.

    What's  850*76.1 + 850?



  • Subject du jour

    All of the actual math is fine. It's the function that translates from the binary values (which are correct, check it out with VBA) to strings to display in the cells. Apparently there are only 6 values between between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause it to display 100000 or 100001. Think of it as a broken ftoi() but I really can't figure out how screwed up it would have to be for this to happen.

     

    The Excel and Excel Services team blog explains it in a little more detail. 



  • One of the comments reveals an interesting insight into the excel rounding function:

    [quote user="cmart02"]

    David,

    You said "Any calculations based off that cell will be accurate too.  Hope that helps."

    But that's not entirely correct. At least not from what I have seen. If you happen to be rounding your calculations (=ROUND(850*77.1,2)*2), it also rounds it to 100K making that permanent.

    So, while it is true that most cases Excel treats the value as correct except for the visual side of it, in others it actually DOES treat it as 100K.

    Rob

    [/quote]


  • This is also confirmed on http://www.appscout.com/2007/09/excel_cant_multiply.php which PCMag at http://www.pcmag.com/article2/0,1895,2188504,00.asp references. Nice "feature".

    I have tried this on Excel XP (2002) and this version of Excel gets the correct result, for anyone who is curious.



  • It's already been stated that this bug is ONLY in excel 2007.


Log in to reply