Coffeehouse Thread

44 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

850 * 77.1 is 65535... unless you're Microsoft Excel.

Back to Forum: Coffeehouse
  • User profile image
    Simo

    JonesJ wrote:
    As a front office developer at an investment bank this scares the crap out of me.

    MS fix this and fix it VERY quickly!!!



    tribalcactus wrote:
    There has already been a bug filed on this.  My guess is that it will be addressed with SP1.


    I've been sent the Google groups link discussing this link twice today from two seperate sources at two seperate investment banks.

    I'd suggest they fix it damn damn quick.

  • User profile image
    JChung2006

    65535 is 2^16-1.

    According to Excel 2007

    85000*.771 = 65535
    8500*7.71 = 65535
    850*77.1 = 100000
    85*771 = 65535
    8.5*7710 = 65535
    (850*7.71)*10 = 65535
    850*(7.71*10) = 100000

    Something stinks in the state of Washington (Redmond area).

  • User profile image
    PerfectPhase

    It amusses me that you can do

    A1 => 850*77.1    = 100000
    A2 => A1 - 1      = 65534
    A3 => A1 + 1      = 100001

    Smiley

    EDIT: opps, see this is posted right at the top of the thread...

  • User profile image
    evildictait​or

    What I don't get is why it's base 10 "100000". It's clear that the problem is that the floating point multiplication has overflown the two-byte box that the number has, but it's bizarre to say the least that it chose to overflow to 100000. I could even understand it going to 0x100000 (overwrite on the buffer - potential hackable hazard).

    Back in yonder days of old, when I was less lazy than I am now, I'd attack Excel with the stick of debug and give you an actual answer, but Excel is big and I am lazy, so I can't be bothered.

  • User profile image
    Wil

    At this site an investigator performed a simple loop to calculate (65535/x)*x for x = 1, ..., 65535.  For over 10,000 values of x, it gave 100,000 rather than 65,535 as the answer.

    If you have a stock whose value is fluctuating close to 77, keep watching the ticker to see when to sell 850 shares of it!

  • User profile image
    evildictait​or

    Wil wrote:
    At this site an investigator performed a simple loop to calculate (65535/x)*x for x = 1, ..., 65535.  For over 10,000 values of x, it gave 100,000 rather than 65,535 as the answer.

    If you have a stock whose value is fluctuating close to 77, keep watching the ticker to see when to sell 850 shares of it!


    Sadly progrommatic access through excel shows 65535 when it autocasts to a float, double or int, and most of the big financial companies are more prudent than to upgrade their software if the old one works.

    That being said, it's unacceptable since grandpa joe might be paying for a bottom-line that has been incorrectly computed by davy-smith and sons local accountants firm ltd.

  • User profile image
    jvs_ca

    For n = 1 to 1000, there are 137 "bugs":

    n  65535/n n*(65535/n)
    13 5041.153846153850 100000
    26 2520.576923076920 100000
    49 1337.448979591840 100000
    52 1260.288461538460 100000
    81 809.074074074074 100000
    87 753.275862068965 100000
    93 704.677419354839 100000
    98 668.724489795918 100000
    104 630.144230769231 100000
    107 612.476635514019 100000
    115 569.869565217391 100000
    117 560.128205128205 100000
    119 550.714285714286 100000
    123 532.804878048780 100000
    162 404.537037037037 100000
    169 387.781065088757 100000
    173 378.815028901734 100000
    174 376.637931034483 100000
    186 352.338709677419 100000
    196 334.362244897959 100000
    208 315.072115384615 100000
    214 306.238317757009 100000
    230 284.934782608696 100000
    233 281.266094420601 100000
    234 280.064102564103 100000
    235 278.872340425532 100000
    238 275.357142857143 100000
    243 269.691358024691 100000
    246 266.402439024390 100000
    249 263.192771084337 100000
    324 202.268518518519 100000
    338 193.890532544379 100000
    339 193.318584070796 100000
    346 189.407514450867 100000
    348 188.318965517241 100000
    349 187.779369627507 100000
    372 176.169354838710 100000
    379 172.915567282322 100000
    385 170.220779220779 100000
    392 167.181122448980 100000
    393 166.755725190840 100000
    395 165.911392405063 100000
    415 157.915662650602 100000
    416 157.536057692308 100000
    421 155.665083135392 100000
    425 154.200000000000 100000
    428 153.119158878505 100000
    439 149.282460136674 100000
    457 143.402625820569 100000
    460 142.467391304348 100000
    466 140.633047210300 100000
    468 140.032051282051 100000
    470 139.436170212766 100000
    476 137.678571428571 100000
    486 134.845679012346 100000
    489 134.018404907975 100000
    492 133.201219512195 100000
    498 131.596385542169 100000
    499 131.332665330661 100000
    574 114.172473867596 100000
    578 113.382352941176 100000
    587 111.643952299830 100000
    597 109.773869346734 100000
    610 107.434426229508 100000
    617 106.215559157212 100000
    621 105.531400966184 100000
    642 102.079439252336 100000
    648 101.134259259259 100000
    653 100.359877488515 100000
    663 98.846153846154 100000
    671 97.667660208644 100000
    676 96.945266272189 100000
    678 96.659292035398 100000
    685 95.671532846715 100000
    687 95.393013100437 100000
    689 95.116110304790 100000
    692 94.703757225434 100000
    693 94.567099567100 100000
    696 94.159482758621 100000
    698 93.889684813754 100000
    711 92.172995780591 100000
    721 90.894590846047 100000
    723 90.643153526971 100000
    727 90.144429160935 100000
    729 89.897119341564 100000
    731 89.651162790698 100000
    744 88.084677419355 100000
    751 87.263648468708 100000
    758 86.457783641161 100000
    761 86.116951379764 100000
    770 85.110389610390 100000
    783 83.697318007663 100000
    784 83.590561224490 100000
    786 83.377862595420 100000
    789 83.060836501901 100000
    790 82.955696202532 100000
    797 82.227101631117 100000
    807 81.208178438662 100000
    830 78.957831325301 100000
    832 78.768028846154 100000
    837 78.297491039427 100000
    841 77.925089179548 100000
    842 77.832541567696 100000
    845 77.556213017752 100000
    850 77.100000000000 100000
    856 76.559579439252 100000
    865 75.763005780347 100000
    878 74.641230068337 100000
    883 74.218573046433 100000
    885 74.050847457627 100000
    887 73.883878241263 100000
    889 73.717660292463 100000
    897 73.060200668896 100000
    899 72.897664071190 100000
    901 72.735849056604 100000
    914 71.701312910285 100000
    915 71.622950819672 100000
    920 71.233695652174 100000
    921 71.156351791531 100000
    932 70.316523605150 100000
    936 70.016025641026 100000
    939 69.792332268371 100000
    940 69.718085106383 100000
    952 68.839285714286 100000
    955 68.623036649215 100000
    959 68.336809176225 100000
    967 67.771458117890 100000
    971 67.492276004120 100000
    972 67.422839506173 100000
    973 67.353545734841 100000
    977 67.077789150461 100000
    978 67.009202453988 100000
    979 66.940755873340 100000
    984 66.600609756098 100000
    996 65.798192771084 100000
    997 65.732196589769 100000
    998 65.666332665331 100000

    Expressionless

  • User profile image
    j0217995

    I have heard a rumor this is an bug resurfaced again.  As in previous versions of Excel throughout the ages have had this problem and once again it appears to be a valid bug?  Can anyone back this up?h

  • User profile image
    Minh

    j0217995 wrote:
    

    I have heard a rumor this is an bug resurfaced again.  As in previous versions of Excel throughout the ages have had this problem and once again it appears to be a valid bug?  Can anyone back this up?h

    Does not happen in Office 2003.

  • User profile image
    Yggdrasil

    Official Excel Blog comes with an explanation and promise of an upcoming fix:

    Official Excel Blog Thingie wrote:

    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.


  • User profile image
    BlackTiger

    Yggdrasil wrote:
    Official Excel Blog comes with an explanation and promise of an upcoming fix:

    Official Excel Blog Thingie wrote:

    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.




    Perplexed

    1 is between 0.99999999995 and 1
    2 is between 1.99999999995 and 2
    3 is between 2.99999999995 and 3
    etc...

    And he is wrong about "only a display bug"...

    If you stumbled and fell down, it doesn't mean yet, that you're going in the wrong direction.
    Last modified
  • User profile image
    Rossj

    BlackTiger wrote:
    
    And he is wrong about "only a display bug"...


    I think it means that it is post-processing where they decide how to display the value that is causing the problem, not the calculation of the actual value - I could be (and probably am) wrong.  I don't however think that saying "This'll only happen with a few thousand number" really mitigates the situation.

    I'm taking bets on how long before-
    1. A lawsuit
    2. An update

  • User profile image
    BlackTiger

    Hmmm... Interesting...

    A1: =850*77.1
    A2: =A1+1
    A3: =A2+1

    Result in A3 = 65537

    A1: =850*77.1
    A2: =ROUND(A1,1)+1
    A3: =A2+1

    Result in A3 = 100002

    A1: =850*77.1
    A2: =A1+1
    A3: =A2*2

    Result in A3 = 131072


    Crazy stuff... Perplexed

    If you stumbled and fell down, it doesn't mean yet, that you're going in the wrong direction.
    Last modified
  • User profile image
    Tensor

    Rossj wrote:
    
    BlackTiger wrote:
    
    And he is wrong about "only a display bug"...


    I think it means that it is post-processing where they decide how to display the value that is causing the problem, not the calculation of the actual value - I could be (and probably am) wrong.  I don't however think that saying "This'll only happen with a few thousand number" really mitigates the situation.

    I'm taking bets on how long before-
    1. A lawsuit
    2. An update


    Do Northern Rock use Office 2007?

  • User profile image
    Dodo

    LOL Big Smile

    Are you sure it's not some puzzling with , (comma) and . (dot)?

  • User profile image
    evildictait​or

    BlackTiger wrote:
    
    And he is wrong about "only a display bug"...


    Errr.. he has access to the code and debugger. I'm gonna go with his version of events, rather than your "superior knowledge" on the subject.

  • User profile image
    Minh

    evildictaitor wrote:
    
    BlackTiger wrote:
    
    And he is wrong about "only a display bug"...


    Errr.. he has access to the code and debugger. I'm gonna go with his version of events, rather than your "superior knowledge" on the subject.

    It's actually provable that it's not just a display bug:
    A1: =850*77.1
    A2: =ROUND(A1,1)+1
    A3: =A2+1

    Result in A3 = 100002

  • User profile image
    AndyC

    Minh wrote:
    
    It's actually provable that it's not just a display bug:
    A1: =850*77.1
    A2: =ROUND(A1,1)+1
    A3: =A2+1

    Result in A3 = 100002


    Since that only seems to happen when rounding, it suggests there is another subtle bug in ROUND that only surfaces when the displayed value doesn't match the stored value.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.