Can you replicate this simple Excel bug? Microsoft support can, but refuse to take action until more people complain

[RingoHere]

The latest Excel release appears to have a very simple yet potentially critical bug with autofill.
Values will lose or gain 0.0000000000001 or <del style="box-sizing: inherit; color: rgba(0, 0, 0, 0.6); border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">more </del>less in a seemingly random but repeatable pattern. This breaks any formula that needs an exact match.

Can you replicate this?

1. Start Excel and open a new blank sheet - no existing data or formulas
2. Enter any number in any cell - this example use 19 in A1
3. On the next cell, enter another number that has decimal places - this example use 18.9 in A2
4. Select both numbers, then drag down to autofill the column - this example fill 200 rows. Excel auto formats to show 1 decimal place. Looks normal, BUT
5. Browse down the filled cells looking at the actual (i.e. not display formatted) values in the formula bar. You'll start seeing inaccuracy by a tiny but important amount! - this example, at 15.7 you'll see the actual value is 15.6999999999999, then correct again at 15.5, and then from 15.2 the actual value is 15.1999999999999, and continues to vary +/- 0.0000000000001 randomly but repeatably as the fill series continues.

Microsoft 3rd line support has replicated this bug in their lab repeatedly (SRX1439102917ID). However, they have refused to take any action as there aren't enough complaints yet so have closed the support ticket!?!

I'm desperate to find a cause and solution, as I'm working on a massive landscape survey data set and need to MATCH values between tables before interpolating for 3d contour maps. While I can type out each X and Y value manually, this will take an incredible amount of time. I also can't trust any results if we can't find a cause for the bug.
Other observations I noted are:

• The issue seems to occur only with decimal numbers (replicated on 1, 2, and 3 decimal place starting numbers)
• I can replicate on ascending or descending series fills, both in rows and columns.
• The error pattern varies depending on numbers chosen, and some numbers don't error at all in 400 filled cells (my test limit so far).
• It's been replicated in Excel 2016 (licensed as Office 365) in 32 and 64-bit, latest patched or with patches rolled back, clean and existing installs, COM components and add-ins disabled, and even replicated in safe mode. Running on Windows 10 with latest release patches.

I have created examples to show the issue in this linked spreadsheet.
Note that you can view the examples in Excel Online, however, the bug is probably only in native Excel software.

• If you can replicate it, please post here, and raise with Microsoft support for action.
• If you can solve it, I can only offer awe, amazement and gratitude, as 14 Microsoft support members have tried and failed
• If you can't replicate it, it's worth knowing about as you troubleshoot other users issues

I can't find a similar issue on forums. Anyone see this before?

 

[mole999]

Unfortunately for you it is not new, and does exist in many places on the net, just one take on it > https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

 

[MARK858]

Surely that is just the old (well documented) floating point precision issue?

https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

https://support.microsoft.com/en-gb...ithmetic-may-give-inaccurate-results-in-excel

https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

https://www.excel-easy.com/examples/floating-point-errors.html

 

[RingoHere]

Surely that is just the old (well documented) floating point precision issue?

https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

https://support.microsoft.com/en-gb...ithmetic-may-give-inaccurate-results-in-excel

https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

https://www.excel-easy.com/examples/floating-point-errors.html

You're probably both right! After 8 hours with Microsoft support up to 3rd line, they didn't mention it. As numbers are so small, and error occurs so soon in a simple function, I wouldn't have imagined FP could cause it. I've just read about ROUND as a workaround.

Would numbers this small and calcs so simple trigger FP size errors?

 

[RingoHere]

Happy to close the thread and just assume it's somehow triggering floating point even with small numbers. Not sure how I can mark as SOLVED? Admins please close thread if you see fit

 

[mole999]

there are no closed threads apart from breaches of policy, sorry it took so long to get an answer, just hope your landscaping dosen't required dates before 1900. as for ROUND, you might also look at ROUNDUP, ROUNDDOWN, MROUND, and financial rounding has its own rules

 

[RingoHere]

there are no closed threads apart from breaches of policy, sorry it took so long to get an answer, just hope your landscaping dosen't required dates before 1900. as for ROUND, you might also look at ROUNDUP, ROUNDDOWN, MROUND, and financial rounding has its own rules

Thank you. I'll check those out now too... really appreciate the help and will spend more time trawling the forums here for answers. Luckily I don't think there's anything as dated as 19th century to address, other than my excel skills. All the best

 

[Kyle123]

Xposted here: https://www.excelforum.com/excel-ge...ft-support-can-but-refuse-to-take-action.html