+ Reply to Thread
Results 1 to 12 of 12

Formula Randomly Fails

  1. #1
    Forum Contributor
    Join Date
    01-12-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016
    Posts
    152

    Angry Formula Randomly Fails

    Hi all,

    I've got a relatively simple spread sheet which is giving me some problems.

    I have a reference cell and a 25 cell long column of data which all reference the same cell, and yet if i add a particular number (in this case 70) into the reference cell, one line out of 25 throws a really odd result, yet the others are all as expected. It also only seems to occur when the reference cell is this one value (70) and any other value displays the correct result?!

    It seems like a bug but I have no idea how to get around it. It was also working fine earlier today to my knowledge.

    Using Excel 2016

    Any ideas or anyone experienced similar?

    Thanks

    Sam

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: Formula Randomly Fails

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,778

    Re: Formula Randomly Fails

    Could we see the workbook? Desensitize it if necessary.

    BSB

  4. #4
    Forum Contributor
    Join Date
    01-12-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016
    Posts
    152

    Re: Formula Randomly Fails

    Yep, I'll do it now

    Thanks

  5. #5
    Forum Contributor
    Join Date
    01-12-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016
    Posts
    152

    Re: Formula Randomly Fails

    As requested
    Attached Files Attached Files

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,778

    Re: Formula Randomly Fails

    Without knowing more about what this is for and how you'd expect it to react, to me it looks like it's doing exactly what you've asked.
    The formula errors on the part in red below (because J17 is empty) which fires the IFERROR part of the formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you use the evaluate formula option it steps through like this:
    B17-((M17/E17)*(B17-$N$6))
    22-((14406/1329)*(22--0.2))
    22-((10.8397291196388)*(22.2))
    22-240.641986455981

    Which ultimately gives the result:
    -218.641986455982

    What result were you expecting?

    BSB

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,650

    Re: Formula Randomly Fails

    it looks like the correct answer to me. You have =IF(E17="","",E17/VLOOKUP(D17,correction_factor_CIBSE,2)) and E17 is 1329, what is in D17 is 43.0 and across from 43.0 in your correction factor table is 0.822 so 1329/0.822 is 1616.78832... which rounds up to 1617, where is the problem?

    edit, and I only looked at F17.
    Last edited by Sam Capricci; 06-22-2018 at 03:14 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  8. #8
    Forum Contributor
    Join Date
    01-12-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016
    Posts
    152

    Re: Formula Randomly Fails

    Hi,

    Thanks for this

    If you compare the results between 69 and 71 in the reference cell, you will see that it does not follow suit, yet the other cells do?

    I hope that makes sense and thanks for the help

    Sam

  9. #9
    Forum Contributor
    Join Date
    01-12-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016
    Posts
    152

    Re: Formula Randomly Fails

    Quote Originally Posted by Sambo kid View Post
    it looks like the correct answer to me. You have =IF(E17="","",E17/VLOOKUP(D17,correction_factor_CIBSE,2)) and E17 is 1329, what is in D17 is 43.0 and across from 43.0 in your correction factor table is 0.822 so 1329/0.822 is 1616.78832... which rounds up to 1617, where is the problem?

    edit, and I only looked at F17.
    Hi,

    Thanks for the help, did you change the top reference cell to 70?

    If you change from 71 to 70 to 69, you'll see the inconsistency with 70

    Much appreciated

    Sam

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,650

    Re: Formula Randomly Fails

    ok, if I change the reference cell to 70, D17 changes to 38, across from 38 is 7.000 and 1329/7=189.857... or rounded to 190 which is what it shows in F17.
    at 69 D17 changes to 37 and across from 37 is 0.676 and 1329/0.676 = 1965.976... or 1966 which is what shows in F17.
    I must be missing something.

  11. #11
    Forum Contributor
    Join Date
    01-12-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2016
    Posts
    152

    Re: Formula Randomly Fails

    Ah!

    There is an error in the correction factors!

    Doh!

    The 7.000 is what led me to it in your last comment. I've no idea how that happened but really appreciate your help!

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,650

    Re: Formula Randomly Fails

    Hey, don't beat yourself up over it, we all make mistakes and sometimes it takes others to spot the issue.
    Glad I could help.
    AND thank you for the rep!

+ 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. Creating PowerPoint Presentation with VBA Fails Randomly
    By Bobbbo in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-10-2018, 01:58 PM
  2. [SOLVED] Need to Randomly pick numbers in column with a formula
    By pradeepdeepu_001 in forum Excel General
    Replies: 6
    Last Post: 05-18-2017, 02:39 AM
  3. [SOLVED] Formula to randomly assign one of four values to a cell
    By harryadkins in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-23-2016, 04:37 AM
  4. Can some one please point me where this formula fails?
    By flanza in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-29-2013, 11:09 AM
  5. [SOLVED] If formula randomly not working
    By 3smees23 in forum Excel General
    Replies: 7
    Last Post: 05-23-2012, 09:55 AM
  6. Replies: 5
    Last Post: 04-28-2010, 11:29 AM
  7. cell.formula fails
    By Garry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2006, 10:40 PM

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