Coffeehouse Thread

44 posts

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

Back to Forum: Coffeehouse
  • User profile image
    Cannot​Resolve​Symbol

    AndyC wrote:
    
    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.


    Or that the display bug is actually a result of a bug in their rounding routine (as displayed values are always rounded to the 15th decimal place).

  • User profile image
    stevo_

    Maybe you guys aren't thinking the bug isn't in the calculation as such, its in the cell being able to show the result of the value correctly.. the +1 thing is just +1 to whatever the REAL value of the 100000 cell is..

    The whole concept of decimal in binary systems is tainted, its just ~how~ tainted it is..

    Still, pretty bad bug to release..

  • User profile image
    Minh

    AndyC wrote:
    
    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.

    ROUND() is burden by how it's usually used, that is to represent something to the nearest dollars, for example:

    A1 = 0.95   B1 = ROUND(A1) = 1
    A2 = 0.95 B2 = ROUND(A2) = 1
    B3 = SUM(B1:B2) = 2

    In B3, people don't want to see 1.9 -- and even though, you could as easily ROUND(B3)... people want the SUM of B1 : B2 to be the natural SUM of what they see.

    So, somehow, ROUND bakes the translated value into the cell... in this case, the value is translated incorrectly.

    So, any function that requires baking the translated value into the cell, I think would be subject to this bug.

    How embarassing.

  • User profile image
    Escamillo

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


    Even before the Excel blog was posted, some slashdotters had already demonstrated that this is a display-only bug .  See this slashdot sub-thread:
    And yet it works FINE when you GRAPH it...

    Most of the time people don't read cells themselves, rather automated processes perform spreadsheet calculations, update database with the results, update spreadsheets, etc.  Humans normally look at prepared graphs, which apparently isn't affected by this problem.  But this is still very bad for cases where human eyes are looking directly at the cells to read data.

  • User profile image
    BlackTiger

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


    Even before the Excel blog was posted, some slashdotters had already demonstrated that this is a display-only bug .  See this slashdot sub-thread:
    And yet it works FINE when you GRAPH it...

    Most of the time people don't read cells themselves, rather automated processes perform spreadsheet calculations, update database with the results, update spreadsheets, etc.  Humans normally look at prepared graphs, which apparently isn't affected by this problem.  But this is still very bad for cases where human eyes are looking directly at the cells to read data.


    Is it your religion? [6] ("Doctor Who (2005)", season 2, "Satan's pit")

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

    WHERE IS FIX?!?!?

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

    Still no fixes for this "display only bug"? Not quite "display"? Or "by design"?

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

    UPDATE RELEASED!

    - Excel 2007
    - 64-bit Excel Services 2007
    - 32-bit Excel Services 2007

    KB Articles have been posted as well:

    - Excel 2007
    - Excel Services 2007

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

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.