Incorrect 15th decimal causing rounding errors

[adnwilson]

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.

[#2]

This is a question that has been asked many times before. See this article:

https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu...

[#3]

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...

[#4]

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.