Forum Discussion

adnwilson's avatar
adnwilson
Copper Contributor
Mar 05, 2020

Incorrect 15th decimal causing rounding errors

I have a spreadsheet to create Golf Handicap Tables.  The first column ranges from +5.0 to 54.0 in steps of  0.1  for a total of 591 rows.  The column is displayed to 1 decimal.  But if the same values are displayed to 16 decimals, an extraneous digit is appearing in some rows in the 15th decimal place.  Calculations done on the values in that column can produce results like 7.49999999999999  instead of 7.5000000 - that causes rounding down to 7.4 instead of 7.5.  It's easy enough to fix, but if you don't know it's happening, you can get incorrect results as I did.  Attached spreadsheet demonstrates the problem.  I can see no pattern in the way the unwanted digits appear.

3 Replies

  • adnwilson's avatar
    adnwilson
    Copper Contributor
    Thanks, understood. Though 'seemingly wrong answer' ducks the issue a bit - the answers I got, as displayed, were definitely wrong, and if I hadn't been able to compare them with results from a different source I'd never have known they were wrong. But I guess there's nothing to be done about it, which seems a shame.
  • PReagan's avatar
    PReagan
    Bronze Contributor

    Hello adnwilson,

     

    You have discovered an example of floating point precision. Learn more here:

    https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/

Resources