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

[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 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 varies +/- 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 the attached 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?

[Kyle123]

Can't you just round the numbers? If you need that level of accuracy, you need to have a good grasp of floating point issues.

[RingoHere]

Someone else just suggested ROUND too. It's a viable workaround I think as I only need 0.1 grid accuracy, and the Z value is what matters. Thanks!

That said, I can't see how such small single decimal numbers would trigger floating point issues, particularly within in less than 40 cells. Microsoft didn't raise it either.

So you wouldn't see it as a bug, but rather an undocumented feature?

[Kyle123]

The problem (if you see it that way) with excel is that it tries to mask floating point issues. The size of the decimal is irrelevant for example in floating point 0.1 + 0.2 != 0.3 however most people expect it to, therefore that's what excel presents. However the implicit conversion/presentation can be inconsistent with different formulas exhibiting different behaviour.

In general, you are probably better using the round function when comparing absolutes.

[RingoHere]

The problem (if you see it that way) with excel is that it tries to mask floating point issues. The size of the decimal is irrelevant for example in floating point 0.1 + 0.2 != 0.3 however most people expect it to, therefore that's what excel presents. However the implicit conversion/presentation can be inconsistent with different formulas exhibiting different behaviour.

In general, you are probably better using the round function when comparing absolutes.

For a rookie on a crash course I certainly didn't expect FP (which I'd heard a bit about previously) to manifest so early. I know what to do now, thanks!

The most frustrating for me part is spending 7 days, 8 hours, 25 points of contact, and 12 people analysing my system with Microsoft, and still didn't turn up the answer this forum got within 10 minutes. And even after a management blowup they didn't give an explanation as simple as this which would have been more than satisfactory. Great help here, though the issue shouldn't have made it's way to spamming your pages.

All the best, and I'm glad to be in the community here now

[tim201110]

this behavior occurs because the IEEE

[Kyle123]

Your post does not comply with Rule 3 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Please make sure to include the full link, so that other members will be permitted to continue helping you

Note that if you have fewer than 10 posts, you may need to type the link, or past it in pieces (or ask a mod to do that for you)

[Pepe Le Mokko]

If you had taken a couple of minutes to search the Web or any XL forum, you would have found your answer without wasting so much time. This is a well known and documented problem

[RingoHere]

Thanks Kyle, Pepe. Guilty, though not through malice, but frustration and desperation. Cross posting now understood and won't happen again. If it matters, I did search for the problem online quite hard, though as I suspected install corruption faults due to interpolation libraries installed, then excel bugs, I didn't see any answer like this. Likely my search terms. Sorry to waste your time (and I'm now escalating with Microsoft so they add this to their knowledgebases).