Forum Discussion

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    Excel:

    Google Sheet:

    Zoho:

    OnlyOffice:

    I guess same story will be with other spreadsheet software, at least for majority of it.

    Actually =50*2.05 is bit less than 102.5. We may check by

    =(50*2.05 -102.5)*1e10

    which returns -0.000142109 both for Excel and Google Sheet.

    If unzip Excel, above number is kept as

    <f>50*2.05</f><v>102.49999999999999</v>

    Power Query also return number less that 102.5

    but Power Query uses banking rounding, i.e. to nearest even.  If we add 1 in formula the number will be rounded to 104, not 103

    MROUND rounds up if value is equal or greater than half of multiplier, otherwise rounds down.

    • _jr's avatar
      _jr
      Copper Contributor

      But then why ROUND rounds correctly to nearest one and interestingly my google sheet shows correct result

  • m_tarler's avatar
    m_tarler
    Silver Contributor

    Yes I'm sure it is a floating point issue but to make things more interesting I repeated your example on row 15 but then the Function ROUND() worked fine (see row 16).  I further used the Excel Labs in grid tool to show the calculations and in cell B15 it shows different result than the grid (see yellow notification and how it shows the correct 103.  But in cell D15 I included the calculation directly inside the MROUND() function and both the grid and Excel Labs agree on the wrong result:

    I know this doesn't help you but I thought it was interesting.