Comments on: Excel Nested IF statement: examples, best practices and alternatives

Though very popular, the nested IF statement is not the only way to check multiple conditions in Excel. In this tutorial, you will find a handful of alternatives that are definitely worth exploring. Continue reading →

Comments page 2. Total comments: 231

  1. I am struggling to get result based on these criteria table:

    Age: Earning Point
    = 45 $11800 10
    = 45 $21000 20

  2. I am struggling to get result based on these criteria table:

    Age 45 Point
    $5600 $11800 10

    $8800 $21000 20

    Please help. Tried multiple ways - If(and) / If. Dont seem to work completely.

  3. Hello ,
    Please help to write an IFS statement to yield these result.

    If employees score between 0 and 15 there get the full payout of $3500.00(100%)
    If employees scores between 15 and 30 the amount is prorate and if 30 is score they 50% of the payout.
    Score >30 will get Zero payout. Can Someone assist?

  4. Respected Sir,

    Instead of Numbers, I want to use characters and compare it like high, medium, low. Like below, Which functions are best to use.

    RISK SCORE 1 SCORE 2 SCORE 3 SCORE 4 RESULT

    MINOR INJURY MEDIUM LOW LOW LOW NO CHANGE
    NEGLIGIBLE INJURY MEDIUM LOW LOW LOW UPGRADE TO MINOR INJURY
    MAJOR INJURY LOW MEDIUM HIGH LOW REARRANGE HIGH TO FIRST CELL
    MAJOR INJURY LOW LOW LOW LOW DOWNGRADE TO NEGLIGIBLE
    MAJOR INJURY HIGH LOW MEDIUM MEDIUM NO CHANGE

    conditions:
    MAJOR = ATLEAST ONE HIGH AT FIRST CELL
    MINOR = NO HIGH, ATLEAST ONE MEDIUM
    NEGLIGIBLE = NO HIGH OR MEDIUM, ALL LOW
    HIGH = IF HIGH IS AT SECOND, THIRD OR FOURTH CELL, THEN ARRANGE IT TO FIRST CELL
    MEDIUM = IF MEDIUM IS AT SECOND, THIRD OR FOURTH CELL, THEN ARRANGE IT TO FIRST CELL

  5. I want to mark "No" if scenario is above target and "Yes" if less or equal to target
    Target of scenario is as below
    AST=4000
    CEL=1500
    SCH=3000

    1. Hi!
      I kindly ask you to have a closer look at the article above. Please describe your task in detail if it doesn’t work for you.

      1. this is the formula i tried to use , but does not work
        =IFS(D3="AST",E3>Standard!$B$4,TRUE,FALSE)*AND(IFS(E3="SCH",E3>Standard!$B$7,TRUE,FALSE)*AND(IFS(D3="PCH",E3>Standard!$B$8,TRUE,FALSE)*AND(IFS(D3="MUL",E3>Standard!$B$6,TRUE,FALSE)*AND(IFS(D3="CEL",E3>Standard!$B$5,TRUE,FALSE)))))

        Standard content

        Genus Content
        AST 4000
        CEL 1500
        MUL 3000
        SCH 3000
        PCH 2500
        SBO 6000
        PBO 6000

  6. Hi, I need to add a margin percentage based on a formula where our margin decreases for more expensive products, how do I do that.

    i.E. a product is 5000 to 5999 = 32% margin and product that 6000 to 6999 is 31% margin?

    from to gp
    0 4999 33
    5000 5999 32
    6000 6999 31
    7000 7999 30
    8000 9999 29
    10000 29999 28
    30000 100000 27

  7. Cell C9 contains a number whose value depends on the contents of cell B9. The inputter can either put a number in cell B9 or they can write the word "none" (without quotes)
    None should be interpreted as 0 (in cell C9). If they write any other word cell C9 should display "check value, it must be a number or write the word none"
    If cell B9 has a number less than 40 then c9 =B9*4 but if B9>40 then c9 = B9*50.

    When I only use numbers it all works out OK. If I am only looking at text strings, everything works ok but the moment I combine them in an IF statement it fails with #Value!

    I would be grateful for your advice.

    2. I meant >= 40 for the second part
      so B9 =40 the c9=b9*50

  8. I'm struggling with a complex formula and would appreciate someone's expertise on this. This is the formula that I created for my worksheet and I know the first portion is correct, however, it is the last piece regarding Medely that is not correct.

    =IF(A21="NurseDash",A21="ShiftKey",G21*H21),IF(A21="Medely",(G218),(G21*H21)+(H21*1.5)*(G21-8))

    I'm attempting to have the worksheet calculate the correct bill rate based on overtime paid to an agency staff.
    I need it to calculate like this:
    If A21 = Medely
    Then look at the totals hours worked in G21.
    If they are 8 hours or less, then multiply G21 by H21.
    If they are more than 8 hours, then multiply the first 8 hours by H21, then multiply the hourly rate in H21 by 1.5 then sum these two products.

    1. Hi!
      I don't think the first part of your formula is correct. But I don't have information to fix it. The calculation will be like this -

      =MIN(G21,8)*H21+MAX(G21-8,0)*H21*1.5

      If one of the conditions must be met, then pay attention to this article: Excel IF OR statement.

  9. Hello,

    I'm struggling with two nested IF AND ORs, each works on its own but putting them together fails. I've tried all sort of combinations, IF AND, IF AND OR, no dice.

    Need to check if cell A1 has value of 2 and if cell B1 is greater than or equal to 85, if yes "Good" if no "bad"
    also need to check if A1 has value of 4 and cell B1 is greater than or equal to 65, if yes "Good" if no "Bad"

    =IF(AND(A1=2,B1 > = 85),"Good","Bad"),IF(AND(A1=4,B1 > = 65),"Good","Bad"))

    1. Try:

      =IF(OR(AND(A1=2,B1 > = 85),AND(A1=4,B1 > = 65)),"Good","Bad")

    2. Hi!
      It is impossible to combine your conditions in one formula, as they contradict each other. If the second condition returns Yes, then for the same values the first condition returns No.

  10. Hi,
    I have this question on nested if, if the net cost is less than $0, performance should be "BAD". Otherwise, if the net cost is less than $150 the performance should be "ACCEPTABLE". Otherwise, the performance should be "GOOD"
    Net Income is in H14:H44

    I used =IF(H14<0, "BAD", IF(H14<150, "ACCEPTABLE", " GOOD"))

  11. Hi, sir thank you for the information you give us about excel. I have a question.

    Develop only one formula (drag & drop) starting from cell C8 until cell C13.
    The conditions are as the followings
    1) if the last cell for each row is greater or equal to the value of the previous cell (the result is Yes)
    2) if the last cell for each row is less than the value of the previous cell; then, (the result is No)

    In row 1, cell K1 has the value 3, cell J1 has 1
    In row 2, cell J2 has the value 2, cell I2 has 6
    In row 3, cell I3 has the value 8, cell H3 has 8
    In row 4, cell H4 has the value 1, cell G4 has 2
    In row 5, cell F5 has the value 2, cell E5 has 3
    In row 2, cell I6 has the value 8, cell H6 has 6

    The above mentioned is a table. The values are in multiple columns. I need the answer in a single column.

    Thanks
    Regard

    1. Hi!
      To find the last number in a row, use the INDEX+MATCH functions:

      =INDEX(A1:M1,0,MATCH(E1+306,A1:M1,1))

      You can get the number in the cell on the left with the formula:

      =INDEX(A1:M1,0,MATCH(E1+306,A1:M1,1)-1)

      Use the IF function to compare these values and return "Yes" or "No".

  12. Ablebits Team, thank you for the information you have on here, so wonderful. I'm looking for a formula which would match a person to a scheduled job at a particular time. The conditions for a person to be assigned to the job are; the person has to have a particular rating in order to do the job, the named person should be available for the job for the time slot (not blocked) and one person can do multiple jobs so long as he has the correct rating and he's available. This is a sports official kind of job assignment. I'm basically trying to match about fifty officials to about one hundred games (and all of the officials should get at least one game) and I wondered if excel would be able to help me by cutting down on the amount of time.

    Thank you and I look forward to your response.

    1. Hi!
      This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

      1. Many thanks and I really appreciate the response.

  13. Hello. I am working to find a formula like these 12 subjects e.g (English, Mathematics, Civic education, computer........)
    I want to pick English >=50% & Mathematic >=50% and other six subjects>=50% for promotion
    How can I do sir/ma.
    Thank you.

  14. Hi,

    You have been so helpful with one of my previous questions. Can you tell me why this formula isn't working please? =if(OR(H2="GSD",H2="GR x L"), K2+N3, K2+N4). In the column H there are lots of variations of words, I'd like the formula to check for 3 variations of these words e.g. GSD or GR x L and if it finds them to then add time in N3 to a date held in K2. If it doesn't find that combination of words I really just want it to just show the same information in K2 without adding anything on (but have added a zero in n4 so it does something. Hope that makes sense! Thank you

      1. Hi Alexander, What I want to happen is....If the cell H2 contains 3 possible selection of words eg 'GSD', 'GSD xL' that the cell with the formula in looks at a date in K2 and and adds on time which is held in N3. If it doesn't find those words, it doesn't add time to the date and just shows the same date as already shown in K2. I've tried to account for the 'if not' part by adding on 0 which is held in N4 but not sure I need that. Hope that makes more sense.

        1. Hi!
          The formula checks the 2 conditions you specified. She works correctly. What don't you like about her work?

          1. Thanks Alexander, the formula doesn't seem to be working. as nothing changes. Could it be because I'm trying to look for 3 different sets of words? So it would be 'GSD' or 'GSD x L' not all of them?

            1. Hi!
              I think we are talking about different things. Write some examples of source data and the desired result.

              1. Source data
                in column H it could read "GSD", or "GSD x L", or " L x GSD", or "GR"
                in column K there are a load of dates e.g. 16/06/21, or 05/03/21
                in cell N3 there is the number 429 and in N4 there is the number 0
                I would like a formula that looks at the text in column H and if it finds the text "GSD", "GSD xL", or "L X GSD" then it adds time on (N3 = 429) to the date held in column K and displays this as a new date. If it doesn't find that text it adds on nothing (n4 - 0) to the date - so just replicates the date in already in column K.
                Sorry, I'm probably not being very clear. I really appreciate your time!

              2. Hi!
                If I understand correctly, one more condition needs to be added to the IF formula with OR conditions.

                =IF(OR(H2="GSD",H2="GR x L",H2="L X GSD"), K2+$N$3, K2+$N$4)

                If you are looking for this text as part of the text in a cell, then use the SEARCH function.

                =IF(SUM(--ISNUMBER(SEARCH({"GSD","GR x L","L X GSD"},H2)))>0, K2+$N$3, K2+$N$4)

                I hope it’ll be helpful.

  15. Hi again,

    Disregard my message above, I worked it out:

    =IF(AND(Q30="DNQ"),"DNQ",IF(AND(Q30="INTO GRAND FINAL",R30>S30),"PREMIERS",IF(AND(Q30="PLAY FOR THIRD",R30>S30),"THIRD",IF(AND(Q30="PLAY FOR THIRD",R30<S30),"FOURTH","RUNNERS UP"))))

    Thanks for the information above, I read it properly and it worked.

  16. Hi there :)

    I am having problems using the IF statement not sure if I should be using the AND or the OR function in it, or possibly the IFS would help.

    Perhaps you could help me?

    I work for a sporting club and what I'm trying to do is have an outcome of "Premiers/Runners Up, Third or Fourth, based on the position of the column prior - SEMI's Outcome - (which shows either "DNQ" - Did not qualify, "Play for Third" or "Into Grand Final".

    K Column is Ladder Position at end of Season
    L Column is No. of teams in Grade
    M Column is DNQ or FINALS (based on <4 being in Finals)
    N Column is Home team Score in Semi Final
    O Column is Away team score in Semi Final
    T Column is whether it means we Won or Lost
    Q Column is Semi's outcome (either DNQ, Play for Third, or Into GF)
    R Column is Home team Score in Grand Final
    S Column is Away team score in Grand Final
    T Column is whether it means we Won or Lost
    U Column shows either "Premiers", Runners Up, Third or Fourth.

    Basically, I need to have four outcomes of a possible equation for the end result in Column U.
    If our team wins the grand final and we were "INTO GF" in Column Q, then Premiers, otherwise "Runners Up. If Q showed Play for Third, and our team won, then it should show THIRD, otherwise FOURTH.
    I'd also like if our team didn't qualify at all for the Grand Final that it would show DNQ.

    I hope that make sense ;)

    Thank you for your help

    Sue

    1. Hi!
      Unfortunately, I don't see how all of your conditions can be combined in one formula and shown in one cell. Each of your conditions has a response of TRUE and FALSE. Therefore, they can all be performed simultaneously. Please re-check the article above.

  17. I want to apply all of the follow scenarios in one statement, but no matter what I try I am clearly missing something!

    I have 3 fields that matter: Product Code(E3), Order Date(K3) and Ship Date(L3)

    Test 1: If the product code (E3) = "HV" AND the Ship Date (L3) is > Order Date (K3) + 2, I want to use the Ship Date (L3), otherwise I want to use Order Date (K3) + 2 workdays

    Test 2: If the product code(E3) = "AF" AND the Ship Date (L3) is > Order Date(K3) + 5, I want to use the Ship Date (L3), otherwise I want to use Order Date (K3) + 5 workdays

    Test 3: if it is any other product code (E3) AND the Ship Date (L3) is > Order Date (K3) +1, I want to use this Ship Date (L3), otherwise I want to use the Order Date (K3) + 1 workday

    Any help would be appreciated!

    1. Hi!
      Have you tried the ways described in this blog post?

      =IF(AND(E3="NV",L3 > K3+2),K3+2,IF(AND(E3="AF",L3 > K3+5),K3+5,IF(L3 > K3+1,L3,K3+1)))

      1. I appreciate your quick feedback! While your answer didn't actually perform what I wanted it to, it actually gave me the idea to adjust my thinking and I was able to make it work afterall. I changed to a "less than" approach from a "more than" approach and BINGO!! I think I have my answer! Thanks again!

        1. Or using your "Verbose Description" that might help a bit more

          =IF(Product Code (E3) = "HV", IF(Ship Date (L3) is > Order Date (K3) + 2, Ship Date (L3), Order Date (K3) + 2 workdays), IF(E3 = "AF", IF(L3 > K3 + 5, Ship Date (L3), Order Date (K3) + 5 workdays), IF(L3>K3 + 1, Ship Date (L3), Order Date (K3) + 1 workdays)))

          1. OOPS !! Missed some

            =IF(Product Code (E3) = "HV", IF(Ship Date (L3) is > Order Date (K3) + 2, Ship Date (L3), Order Date (K3) + 2 workdays), IF(Product Code (E3) = "AF", IF(Ship Date (L3) is > Order Date (K3) + 5, Ship Date (L3), Order Date (K3) + 5 workdays), IF(Ship Date (L3) is > Order Date (K3) + 1, Ship Date (L3), Order Date (K3) + 1 workdays)))

        2. =IF(E3 = "HV", IF(L3 >K3 + 2, L3, K3+ 2), IF(E3 = "AF", IF(L3 > K3 + 5, L3, K3 + 5), IF(L3>K3 + 1, L3, K3 + 1)))

  18. Thanks for the info,

    However, in your first illustration, what do we do if, for instance; in the sales column B5 and B8 are blank cells. And we want to return the commisionas unknown

    1. Hi!
      I don't really understand where is the problem here. If the sales are zero, then the commission is also zero.

  19. Hi,
    I would really appreciate assistance with this formula.

    I have a cell A51 that can contain one of three words "receipt", "contract", "invoice"

    If cell A51 says receipt I need cell L51/0,8775
    If cell A51 says contract I need cell L51*38%
    If cell A51 says invoice I need cell L51*0,19

    This is the formula I have tried (amongst numerous attempts)
    =IF(I51="receipt",L51/0,8775,IF(I51="contract",L51*38%,IF(I51="invoice",L51*0,19,)))

    1. Hi!
      Use the correct separators in the formula

      =IF(I51="receipt",L51/0.8775,IF(I51="contract",L51*38%,IF(I51="invoice",L51*0.19,"")))

      1. Thank you,
        I realised I also had to change the comas to semicolons because it's a Spanish/French template

  20. Respected Sir,
    I have learned from your above blog regarding nested if function in excel. In your first example the formula shown in screenshot doesn't meet the criteria condition mentioned in blog. Instead the formula should be like "=IF($B2>=151, 10%, IF($B2>=101, 7%, IF($B2>51, 5%, IF($B2>=1, 3%, 0%))))". Secondly, I realized it doesn't make any difference in result whether you chose High to Low in your formula or Low to High. Excel never make any error in calculation or your required results. Only required condition is that you needed to write your formula correctly. I did for you like this "=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<151, 7%, 10%))))". English is not my first language so I might made some errors in text. If anything written by me hurts the feelings of anybody I do apologize with all.
    Sincere regards,
    Rehmatullah

    1. Hello Rehmatullah,

      Thank you for your feedback.

      You are right about the first formula. To meet the stated conditions precisely, the first logical test should be $B2>=151 or $B2>150.

      As for the order of conditions, we are actually talking about the same thing but in different words :) Whichever direction you choose, it is important to place the nested functions in the right order. In case of "high to low" arrangement, you use the "greater than" operator and check the highest condition first, then the second highest, and so on. In case of "low to high", you use the "less than" operator and start with testing the lowest condition, then the second lowest, etc.

  21. hi the first ie increases by .1 works ,but in the second if it does not go down by .2 per g2-36 (g2 is 40)and i also need to decrease by .3 .4 .5 per differance.

    ps number of brackets at the end may be incorrect as i shortened the formula for this question.

    =IF(AND(F2>=0;F2<=4.4;G236;(F2-(G2-36)*0.1);IF(AND(F2>4.4;F2<=11.4;G236;(F2-(G2-36)*0.2)))

    1. Hello!
      I’m not sure I got you right since the description you provided is not entirely clear.
      I assume that TRUE and FALSE are mixed up in the formula. Try to swap them

      IF(AND(F2>=0;F24.4;F2<=11.4);(F2-(G2-36)*0.2), .......))

      Observe the correct syntax of the IF function.

  22. Hello,

    I am looking to perform a nested IF which looks a the value of two cells (column v and column an) and returns values as follows:

    If cell V2 ="Pass" show the text "Proposed" else "Requires Exam" then as part of the same if statement:
    If cell AN2 =text "Licenced" else the result of the earlier V2 result

    I am sure I am missing something obvious as so far I have tried the following:

    =IF(V2="Pass","Proposed","Requires Exam"(IF(ISTEXT(AN2),"Licenced","Result of V2="Pass","Proposed","Requires Exam"

    1. =IF(AND(V2="Pass",AN2="Licensed"),"Licensed",
      IF(AND(V2="Pass",AN2=0,"Proposed"),
      IF(AND(V2=0,AN2=0,"Requires Exam"),"")))

      1. Hi Sir, Why this if condition is not working =IF(AF="FJ",AND(AP$16>=$AG21,AP$16<=$AI21))

        1. Hello!
          The formula does not work because the conditions are written incorrectly. Check out the guidelines in this article.

    3. Can this be done with formulas or do I need to go down the VBA route?

  23. i am trying to add to the if(and function to display information from other cells after it is through as shown below
    column info
    A-their first name
    B-their last name
    D their sex "M" or "F"
    E- client or staff

    =IF(E3="staff", "Dr.", IF(AND(E3="client", D3="M"), "Mr.", IF(AND(E3="client", D3="F"), "Ms.",)))

    I am trying to get the above formula to display (Dr./Mr./Ms.) (First Name from column A) (Last Name from column B) in one cell "Dr. Gregory House"

    1. Hello!
      Please try the following formula:

      =IF(E3="staff","Dr.",IF(AND(E3="client",D3="M"),"Mr.",IF(AND(E3="client",D3="F"),"Ms.",)))&A3&" "&B3

      Hope this is what you need.

  24. Hello,
    I am attempting and if and or statement, but having trouble getting to the correct result. part I
    If(AJ11="US","TRUE",IF(AJ11="",IF(AG11="US","TRUE",IF(AG11="",IF(AI11="US","TRUE","review"))))), returning false instead of True or review.
    there are 3 col with data points, if no US or blank = true, if any us, blank and non us, test

    if result is to test, the 2 part is to test for 2 letter code to either test or not test.

    suggestions?

  25. Hi there,

    I am struggling with my formula I'd love some help:
    I have 8 data values validated in cell I2, and will be entering a manual number in cell J2 and believe I need an IF formula in column K to easily calculate the following scenario.

    If the frequency = weekly (cell I2), and the number of hours =x, then the monthly hours when annualised would be (J2*52)/12).

    The other values in I2 are:
    Fortnightly
    2 x Weekly (needs annualising then multiplying by 2)
    3 x Weekly (needs annualising then multiplying by 3)
    4 x Weekly (needs annualising then multiplying by 4)
    5 x Weekly (needs annualising then multiplying by 5)
    6 x Weekly (needs annualising then multiplying by 6)
    7 x Weekly (needs annualising then multiplying by 7)
    One-Off (does not need annualising)

    The current formula I have is:

    =IF(I2="Weekly",((J2*52)/12),IF(I2="Fortnightly",((J2*26)/12),IF(I2="3 x Weekly",((J2*52)/12)*3),IF(I2="2 x Weekly",((J2*52)/12)*2""""))))

    It worked until I put the last IF combination in

    I'd really appreciate some help. Thank-you :-)

    1. Hello!
      Please check the formula below, it should work for you:

      =IF(I2="Weekly",J2*52/12,IF(I2="Fortnightly",J2*26/12,IF(I2="3 x Weekly",J2*52/12*3,IF(I2="2 x Weekly",J2*52/12*2,""))))

      1. That worked. Thanks very much!

  26. Hi!

    Needed your expertise to correct my formula:

    Column A: Interval: 1,2,3,4,5,6,7,8,9,10
    Column B: P/MP/OP: OP,OP,OP,OP,MP,MP,MP,P,P,P
    Column C: Dispatch: 1.29,0.45,1.29,1.24,0.26,1.51,1.31,0.14,1.25,0.25
    Column D2: 1.30

    Formula: =IF((OR(A2="MP,A2="OP")),AND(IF(C2>=0.5,$D$2,C2)))

    The result must be: if A2 is MP or OP, and the value of C2 is greater than O.5, it will give me 1.30 answer, if C2 is below 0.5, it will give me the value of that cell. If A2 is P and C2 is lower or higher than 0.5, it will give me 0 answer. I only needed to capture the value of MP and OP.

    Thank you.

    1. Hello!
      Your task is not completely clear to me.
      The condition "C2 is lower or higher than 0.5" does not make sense as it is any number. Action is not specified if no condition is met. There is a reference to D2 in the formula. Your description doesn't say anything about D2.
      Clarify.

      1. Column D2: Firm Dispatch: 1.30 (any value in column C at a minimum requirement dispatch criteria of 0.5, it will still deliver(choose) 1.30 firm. If column C dispatch is lower than 0.5 dispatch criteria, it will choose the value of that cell in column C. Another criteria is for MP and OP only, if B2 is "P" = 0

        Column F2: My initial formula: =IF((OR(B2="MP",B2="OP")),AND(C2>0.5,$D$2,C2)))

        Sorry for the confusion, I am referring to "B2" and not "A2":

        The result must be: if B2 (not A2) is MP or OP, and the value of C2 is greater than O.5, it will give me 1.30 answer, if C2 is below 0.5, it will give me the value of that cell. If B2 is P, result must be 0. I only needed to capture the value of MP and OP.

        Hope this helps.

        1. I have achieved the result I have been looking for. Thank you very much!

        2. Hello!
          If I understand your task correctly, the following formula should work for you:

          =IF(OR(B2="OP",B2="MP"), IF(C2>=0.5,1.3,C2),IF(B2="P",0,C2))

  27. Hello,

    =IF(D5>75,16,IF(D5>70,15,IF(D5>65,14,IF(D5>60,13,IF(D5>55,12,IF(D5>50,11,IF(D5>45,10,IF(D5>40,9,IF(D5>35,8,IF(D5>30,7,IF(D5>25,6,IF(D5>20,5,IF(D5>15,4,IF(D5>10,3,IF(D5>5,2,IF(D5<=5,1))))))))))))))))

    Using above formula to show 1 manager for every 5 employees - (ie. 6-10 employees requires 2 managers, 11-15 employees would require 3 managers, 16-20 employees would require 4 managers)

    Is there a more efficient/condensed way to accomplish this?

  28. "IF(R10>1,Q10*5,IF(R10<0,Q10*7))" Is tje formula is right or contains any error, because i am getting only Q*5 result where it should be Q*7,

  29. Need help to have text & number sequencing based on a drop down list value (to confirm document type and number)

    Column C lists document type from a 3-choice drop down list "Policy","Standard", or "Other"
    Columns E: G contain sequential values for each of the document types.
    Column E "Policy" list of sequential values 0001-PL (continues as 0002-PL, etc. starting in row 2)
    Column F "Standard" list of sequential values 0001-ST (continues as 0002-ST, etc. starting in row 2)
    Column G "Other" list of sequential values 0001-OT (continues as 0002-OT, etc. starting in row 2)

    Since Column C would have a list that is not sequential, how can I ensure column D sequences document number correctly?
    User enters in Column C Row 2 "Policy", Column D will populate as "0001-PL"
    User enters Column C Row 3 "Standard", Column D populates as "0001-ST"
    User enters Column C Row 4 "Policy", Column D populates as "0002-PL"

    How can I make sure that when someone enters one of the three document types, column D picks up last sequence of "xxx-PL"?

  30. Hi
    I have doubt
    Please help to formula below:
    If A1 "-", B1 "-", answer "ok"
    If A1 "", B1 "", answer "ok"
    If A1 "", B1 "-", answer "check"
    Thanks

  31. Please I have a question that I need to solve in nested if c ++

  32. How do I combine =IFERROR(AVERAGE(E6:E8),"") with =AVERAGEIF(E6:E8,"0")

    1. Hi,
      The formula AVERAGEIF (E6: E8, ”0 ″) and AVERAGEIF (E6: E8,” ″) means that you are calculating the average over blank and text cells. It doesn't make sense as it will result in an error. Explain what you want to calculate.

  33. SOMEONE PLEASE HELP:
    So I am working on jasperactive, one of the projects has me come up with a function that will display Two different texts and if it doesn't match either, it will display a blank
    -the original function is: IF(AND(G2>F2,F2>E2),"Growing energy source","")
    -Then it asks for you to add in a function that will display "Shrinking energy source" is G2<F2 and F2<E2
    -i have been trying to make a nested formula for it that will work for hours with no luck. if anyone can help that would be amazing

    1. Hi,
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question

      =IF(AND(G2 > F2,F2 > E2),"Growing energy source", IF(AND(G2 < F2,F2 < E2),"Shrinking energy source",""))

  34. =IF(G17>1.63,12CFW, IF(G17>1.3,10CFW,IF(G17>0.978,8CFW,IF(G17>0,6CFW,""))))

    excel says there is a problem with this formula? help would be much appreciated, thank you.

    1. Hi,
      Text values in formulas must be enclosed in quotation marks.

      =IF(G17>1.63,"12CFW", IF(G17>1.3,"10CFW",IF(G17>0.978,"8CFW",IF(G17>0,"6CFW",""))))

      I hope it’ll be helpful.

  35. demurrage charges (free time = 3 days) 1-4/1/2021) free time
    demurrage charges = 18 days
    4-6 days = usd25
    7-9 days = usd40
    10-12days = usd60
    Thereafter = usd75

    what is if?

    1. Hello!
      Your task is not completely clear to me.
      Explain: 4-6 days=usd25. 25 - is it in one day or all the time?

  36. If cell A1 =family and cell A2=1 A3 =A7
    if cell A1 =family and cell A2=2 A3 =A8
    if cell A1 =family and cell A2=3 A3 =A9
    if cell A1 =Single and cell A2=1 A3 =B7
    if cell A1 =Single and cell A2=2 A3 =B8
    if cell A1 =Single and cell A2=3 A3 =B9

  37. Could someone help me to figure out one If formula that involve multiple conditions.

    For example, I have 7 different project code name in G2, and G3 is associated project name.
    and when I clicked project code in G2, and G3 will auto pop out the right project name.
    I know how to compare within two code. but not sure how to do with multiple different ones.
    Below is an example that i made it up for comparing two projects.

    =IF(TASK_CODE="123456-789","Bill","Cheques")

    Thank you so much

    1. Hello!
      I recommend using the VLOOKUP function to select the desired value.
      Please check out the following article on our blog, it’ll be sure to help you with your task: How to do Vlookup in Excel
      I hope I answered your question. If something is still unclear, please feel free to ask.

  38. Hello- I am trying put a formula together for the below conditions

    For Example
    IF C2=7.5 Then it should RANK 5 similarly IF C2=8.8 Then it should RANK 4

    Below Scale for Each RANK
    >11 = RANK 1
    <10 - 9- 8-<=9 = RANK 4
    <8 = RANK 5

    Please help.

  39. Hi, I'm hoping that I'm not too far off the mark in this area :-).

    I have a register of risks where I want to flag as overdue. A critical must be attended to within 7 days, High 14, Med 30 and Low 60. My stab at it as below fails with an error. Any ideas please?

    X and F contain the age and severity data.

    =
    IF(AND(X10="Critical",F10<7),OK,
    IF(AND(X10="High",F10<14),OK,
    IF(AND(X10="Medium",F1060),OK,
    Overdue))))

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(AND(X10="Critical",F10<7),"OK", IF(AND(X10="High",F10<14),"OK", IF(AND(X10="Medium",F10<60),"OK", "Overdue")))

      I hope my advice will help you solve your task.

  40. I have three options...3 or less yes ...less committed, 4 yes...average commited. More than 4 yes... hifhly committed for E5 to K5...please help

    1. Hello!
      The description of your conditions is not very clear. Please reread the article above, it covers your case completely. Thank you.

  41. i am looking fro a formula for below problem.

    We have 3 cells a1,b1&c1.
    in cells d1-->if out of 3 cells only single cell contain value >0 then it will show Ok otherwise not ok.

    FOr EX:
    a b c d
    0 0 0 OK
    1 0 0 OK
    1 1 0 not ok
    0 1 0 ok

    1. Hello!
      The formula below will do the trick for you:

      =IF(COUNTIF(A1:C1,">0")=1,"OK","Not OK")

      Hope this is what you need.

  42. =IF(P7="A",IF(Q715000,Q750000,Q7*60%/30*O7,0)))),IF(P7="B",IF(Q715000,Q750000,Q7*60%/30*O7,0))))

    the above formula not run please give the solution to me

  43. Thanks Svetlana. Your article above was really helpful.

  44. i want to do below type formula in column K
    column I is numeric 23.45

    and i want to do is if column L will -23.45 so "SL", if column L will subtract with 2 is equal to 23.45 then "1:2" and last if column L will subtract with 3 is equal to 23.45 then "1:3"
    =IF(L2=-I2,"SL",IF(L2=I2,"1:1",IF(L2/2=I2,"1:2",IF(L2/3=I2,"1:3"))))
    i get only "SL" , "1:1" so
    can you help to resolve?

  45. for some reason did not show in the formula
    h40
    g40

  46. I have 4 budget/accounting columns
    F4 = Budget Transfers (can be +or-)
    G4 = Purchase Order Amount Allowed
    H4 = amount paid OR "closed"
    I3 = Beginning balance (which could be zero)
    =if(H4="closed",I3+F4,IF(H40,I3+F4-H4,IF(G40,I3+F4-G4,I3+F4)))
    Please assist:)

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

  47. I have actually sorted this formula out now so no help needed. The formula I ended up with was

    =IF(AND($A$4=$A$6,ISBLANK(J13),ISBLANK(K13),ISBLANK(L13)),"",IF(AND($A$4=$A$7,ISBLANK(M13),ISBLANK(N13),ISBLANK(O13)),"",IF(AND($A$4=$A$8,ISBLANK(P13),ISBLANK(Q13),ISBLANK(R13)),"",IF(AND($A$4=$A$9,ISBLANK(S13),ISBLANK(T13),ISBLANK(U13)),"",CONCATENATE(F13,G13)))))

  48. Hi
    I am trying to put a formula together and cant seem to get the nesting correct. What i am trying to do is get a concatenate value if the IF and AND are true based on another cell.
    My data:
    cell A1= Qtr 1
    cell J10 = Jan
    cell K10 = Feb
    cell L10 = Mar
    cell M10 = April
    cell N10= May
    cell L10= June
    cell F10 = project name
    cell G10 = project number
    What I am after is a formula that will look at cell A1 and if its equal to Qtr 1, and if cells J10(Jan), K10 (Feb) and L10 (Mar) are blank, then i want it to return blank, otherwise concatenate the project name and project number. But if A1= Qtr 2, and if M10, N10 and L10 are blank then return blank,otherwise concatenate project name and project number. And so on for quarters 3 and 4. Is this possible or is there a simpler way to do this?

    Appreciate your assistance.

    1. Hello Charlie!
      If I understand your task correctly, the following formula should work for you

      =IF(AND(A1="Qtr 1",J10="",K10="",L10=""),"", IF(AND(A1="Qtr 2",M10="",N10="",O10=""),"", IF(AND(A1="Qtr 3",P10="",Q10="",R10=""),"", IF(AND(A1="Qtr 4",S10="",T10="",U10=""),"",F10&G10 ) ) ) )

      I hope this will help

  49. Hello. I am working to find a formula in a column that will have 1 of 4 outcomes; N/A, Not Started, Active, & Expired.
    Column A - # of Warranty Years
    Column B - Start Date of Warranty
    Column C - End Date of Warranty
    Column D - Status (N/A, Not Started, Active, or Expired)
    There are times when column A is 0, so that status is N/A.
    There are times when column A has a value, but column B & C are empty, so that status is Not Started.
    I am trying to use the TODAY() function as well so that whenever the sheet is opened it is current for expired warranties.
    Thank you for your help.

    1. Hello Denise!
      I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. In that case I will try to help you.

      1. Here are some of the formulas I have tried, none of them work, and most only account for 3 of the 4 outcomes.
        =IF(AND(A1=0,"N/A","Not Started"),IF(C1>TODAY(),"Active",IF(C1TODAY(),"Active"),IF($C10,$D1=""),"Not Started"),IF($C1>TODAY(),"Active"))
        =IF(A1=0,"N/A",IF(C1>TODAY(),"Active",IF(C1<TODAY(),"Expired")))

        1. Hello Denise!
          If I understand your task correctly, the following formula should work for you:

          =IF(A1 > 0,IF(AND(B1 <> "",C1 <> ""),IF(C1 < TODAY(),"Expired","Active"),"Not Started"),"N/A")

          I hope this will help, otherwise please do not hesitate to contact me anytime.

          1. Thank you for your help. It worked perfectly. Take care.

  50. Hello Alexander Trifuntov
    I was hoping you could help with my excel function have being having trouble with.
    Am trying to archive below multiple query.
    =IF(AND (A4 = "WEEK 1"(OR( D9=({"E", "EOC1", "EOC2", "L"})) & ( D10=({"E", "EOC1", "EOC2", "L"})), "WORKING", "NOT WORKING"))).
    I can also shortened it by saying----
    =IF(AND (A4 = "WEEK 1"(OR( D9=({"E*","L"})) & ( D10=({"E*","L"})), "WORKING", "NOT WORKING"))) using a wildcard for the E's.
    so if both cells contain the range values is TRUE else FALSE.
    The formula work for single cell like this
    =IF(OR(D9=({"E";"L";"EOC1";"EOC2"})),"WORKING","NOT-WORKING" ) but am having trouble with 2 cells and concatenating it.

    Thanks in advance

    1. Hello Leks!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(A4="WEEK 1", OR(D9={"E","EOC1","EOC2","L"}, D10={"E","EOC1","EOC2","L"})), "WORKING","NOT WORKING")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Hi Alex
        That work perfectly for me. Thank you so much, i really appreciate your quick response.

        Many Thanks

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)