Poll: Do you see strange cell values when you run the test?

Be advised that this is a public poll: other users can see the choice(s) you selected.

+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    09-08-2018
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2016 (in Office 365)
    Posts
    4

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

    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?

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,144

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

    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.

  3. #3
    Registered User
    Join Date
    09-08-2018
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2016 (in Office 365)
    Posts
    4

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

    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?

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,144

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

    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.

  5. #5
    Registered User
    Join Date
    09-08-2018
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2016 (in Office 365)
    Posts
    4

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

    Quote Originally Posted by Kyle123 View Post
    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

  6. #6
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

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


  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,144

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

    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)

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,981

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

    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
    Last edited by Pepe Le Mokko; 09-09-2018 at 03:14 AM.
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  9. #9
    Registered User
    Join Date
    09-08-2018
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2016 (in Office 365)
    Posts
    4

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 08-29-2017, 04:42 PM
  2. Microsoft Excel is waiting for another application to complete an OLE action
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2013, 07:13 AM
  3. Microsoft Excel is waiting for another application to complete OLE action
    By LisaPatch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2011, 02:52 AM
  4. Microsoft Excel 2010 Doesn't Support Microsoft Excel 2007
    By Orange.CL in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-01-2010, 03:21 AM
  5. why doesn't microsoft excel support inheritance
    By A. Gilligan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2005, 09:55 PM
  6. Replies: 0
    Last Post: 05-04-2005, 12:06 PM
  7. [SOLVED] why Microsoft Excel can't support if() multiple choices more then.
    By reneabesmer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2005, 04:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1