+ Reply to Thread
Results 1 to 9 of 9

Alternative to an IF formula

  1. #1
    Registered User
    Join Date
    04-22-2017
    Location
    Kent
    MS-Off Ver
    2016/365
    Posts
    7

    Question Alternative to an IF formula

    Hi All,

    I am setting up a worksheet for work that has variables on values. Currently, i am using the following formula:

    =IF(Q4="R",Q$2,IF(P4="r",P$2,IF(O4="R",O$2,IF(N4="R",N$2,IF(M4="R",M$2,IF(L4="R",L$2,IF(K4="R",K$2,IF(J4="R",J$2,I$2))))))))

    Current.PNG

    However, this is limited in a couple ways that I'd like to improve.

    The first issue is that the IF statement is a limited to nine sections, and three of my Panels have ten, as such, I would like to expand it to cover the full panel.
    The second for improvement is that when blank, I'd like it to say < the lowest value. Therefore, if going by the attached example, if all cells are blank, it should be at a value of <0.015 in the MIC mg/L column.

    The current set up has a formula for 0.3 up to 8, but if the value is 0.03 or below, it highlights red for the user to review the value and ensure it is a true 0.03, or if it is lower and require manual input.

    Many Thanks
    Last edited by JonoRig; 07-04-2017 at 09:38 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Alternative to an IF formula

    In your original formula, you're trying to find the last "r" in row 4 between columns J and Q.

    You can replace the entire formula with this

    =IFERROR(MATCH(LOOKUP(2,1/(J4:Q4="R"),J2:Q2),J2:Q2,0),I2)

    I dont understand your second amendment. What do you expect the result to be?

    You need to relate the columns in your formula to the image you've attached.
    Who know where column I is in that diagram? It should be clearly documented in your description.
    Last edited by Special-K; 07-04-2017 at 10:30 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    04-22-2017
    Location
    Kent
    MS-Off Ver
    2016/365
    Posts
    7

    Re: Alternative to an IF formula

    Hi, here is a larger cut of the sheet (I hope they will all fit): Bigger Example.PNG

    This is how the worksheet currently functions: Bigger Example 2 .PNG

    And here is where I have highlighted and explained (hopefully more clearly) the issue: Bigger Example 3.png

    Thanks for your reply, I will try your formula

    Jon

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Alternative to an IF formula

    Ok so you're now including column H which was not in the original formula.

    So shouldn't this just be

    =IFERROR(MATCH(LOOKUP(2,1/(H4:Q4="R"),H2:Q2),H2:Q2,0),"<"&H2)

  5. #5
    Registered User
    Join Date
    04-22-2017
    Location
    Kent
    MS-Off Ver
    2016/365
    Posts
    7

    Re: Alternative to an IF formula

    Thanks, that helps solve the issue of blank cells, but now instead of displaying the values in the header row, it just gives a value of 1 to 10.

    Additionally, it doesn't seem to like being copied/dragged to the other cells much.

    new formula.PNG

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Alternative to an IF formula

    Sorry forgot to add this is an array formula

    Array formula, use Ctrl-Shift-Enter

  7. #7
    Registered User
    Join Date
    04-22-2017
    Location
    Kent
    MS-Off Ver
    2016/365
    Posts
    7

    Re: Alternative to an IF formula

    Sorry to be a pain in the, but I still cannot get this to function.

    At work we only have Office 2010, so i don't know if that makes a difference.

    I have attached the file as it is right now, if anyone has the time to look at it and provide a suggestion.

    Many Thanks
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Alternative to an IF formula

    As per your original description shouldn't this file have sheets that contain the letter "R" ?
    There aren't any.

  9. #9
    Registered User
    Join Date
    04-22-2017
    Location
    Kent
    MS-Off Ver
    2016/365
    Posts
    7

    Re: Alternative to an IF formula

    This is the template, the R's are results,

    I should probably have explained that. This worksheet is for antimicrobial testing, the MIC values are the concentrations that we grow the samples at. If they grow, we put an R for Resistant, The IF formula included already takes into account of the location of the R and puts the MIC value.

    The formula isn't included on the first page, but on the tabs at the bottom you'll see different antibiotics that we test for.

    Thanks

    J

+ 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. [SOLVED] VBA alternative to the countif formula
    By JonnyEnglish in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-24-2017, 04:39 PM
  2. [SOLVED] alternative of LOOKUP formula
    By iamhsn in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 09-23-2015, 03:06 AM
  3. Alternative to array formula
    By dmschave in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-26-2015, 10:11 AM
  4. [SOLVED] An alternative to the [IF] formula.
    By Aland2929 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2013, 08:12 AM
  5. Alternative Formula for multiples IFs (to make my formula shorter)
    By chico.corrales in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-07-2013, 12:37 AM
  6. Alternative to an if sumproduct formula
    By Trax in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-29-2013, 05:13 AM
  7. alternative formula
    By gerard_gonzales33 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2012, 05:37 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