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 3. Total comments: 231

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

  2. 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?

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

  4. 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?

  5. 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!

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

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

  8. "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,

  9. 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"?

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

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

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

  13. 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",""))

  14. =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.

  15. 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?

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

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

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

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

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

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

  22. =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

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

  24. 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?

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

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

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

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

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

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

  31. Hello Bharath
    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

  32. E4 is either blank or contains a date
    I'm trying to get A4 to: 1) to be blank if E4 is blank or 2) place an X in A4 if e4=<today().
    Everything I have tried fails to produce the desired results. I have tried choose functions, if and nested ifs but nothing I'm doing is working.
    Thanks for any help.

    1. Hello Ray!
      If I understand your task correctly, please try the following formula:

      =IF(E4=TODAY(),"x", IF(E4="","","not today"))

      I hope it’ll be helpful.

  33. I need a formula if A1 is greater the or equal to 15 December 2019 or lesser then 14 March 2020 and if A2 says listed then say Yes if not say no or if A1 is greater then or equal to 15 March 2020 then say Yes.

    1. Hello Bharath!
      If I understand your task correctly, please try the following formula:

      =IF(AND(A1 > = DATE(2019,12,15),A1 < = DATE(2020,3,14),A2="listed"),"YES",IF(A1 > = DATE(2020,3,15),"YES","NO"))

      I hope it’ll be helpful.

  34. 1)If= first date of period all floor commission 2%
    2)If=second date of period
    lower floor commission 3% ,
    middle floor commission 3.5%
    Higher floor commission 4%
    3)If = third date of period
    Lower floor commission 2%
    Middle floor commission 2.5%
    Higher floor commission 3%

    *(Date of periods
    (1) 29-09-2019 to 02-02-2020
    (2) 03-02-2020 to 29-02-2020
    (3) 01-03-2020 to 30-03-2021)*

    Can you please help any formula to get correct commission %

    1. Please help it's very urgent....

  35. Please help me!!!

    I want to use an IF Command, but I want it in such a way that it fetches another IF Statement from a totally different Cell...

    How do I do it?

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

      =IF(C1,1,0)

      in cell C1 write down the formula

      =IF(A1>0,TRUE,FALSE)

      I hope it’ll be helpful.

  36. Hello,

    I would ask for your advice in choosing the easiest procedure for determining ratings testing physical ability to take data from two different cells that are related to gender (male or female) and age group (is different for men and women, and can be easily determined on the basis of age using nested IF functions). Therefore, it is necessary to include data relating to gender and age category in the test results to obtain a score which is also categorized as laid down norms. Pre grateful!

  37. My apologies... it sent before I finished my question.
    Over Amt Short Amt Retailer ID
    351.20 25862
    10.00 37586
    10.00 67952
    351.20 25862
    I would like to have the formula highlight amounts in red that are the same between Over Amt (col C) and short Amt (col D), but ONLY if the Retailer IDs (col E) are the same. As you can see here, the 351.20 amounts have the same retailer ID, but the 10.00 amounts do not. Any help you can give would be appreciated.
    Thank you!
    Chris

  38. Good morning. I'm trying to figure out how to do the following:
    Over Amt Short Amt Retailer ID
    351.20

  39. I NEED A ONE FORMULA FOR THIS:
    I WANT TO WRITE "A" IN 2 CELLS AUTOMATICALLY IF WE BOOK AN APPOINTMENT FOR SERVICE "A"- 1 HOUR
    AND TO WRITE "B" IN 2 CELLS AUTOMATICALLY IF WE BOOK AN APPOINTMENT FOR SERVICE "B" - 1 HOUR
    AND TO WRITE "C" IN 4 CELLS AUTOMATICALLY IF WE BOOK AN APPOINTMENT FOR SERVICE "C" - 2 HOURS
    AND TO WRITE "D" IN 3 CELLS AUTOMATICALLY IF WE BOOK AN APPOINTMENT FOR SERVICE "D" - 1.5 HOURS
    PLEASE NOTE THAT THE ONE CELL MEAN 30 MINS.

  40. I have a table of information. In the D column I have multiple products with various units of measure (g, mm, ml, kg, etc) I am attempting to build a formula that searches for the specific unit of measure and populates a new column with only that unit. Eg:

    Column B3 information : DEN BRAVEN ACRYLIC BEECH/ OAK 280ml
    Information I want the formula to find and place into column D "ml"
    I have tried building a multiple IF but I it only identifies the first range of data successfully. The moment the Formula gets to a different unit of measure then it returns a #VALUE! issue.

    Column B4 Data that the formula has issues with : ALCOLIN WOODFILLER OREGAN PINE 200g

    Here is a copy of the IF:

    =IF(FIND("ml";B3;1);"ml";IF(FIND("g";B3;1);"g";IF(FIND("m";B3;1);"m";"no")))
    Could anyone give me some pointers?

  41. A1 value is Male or Female
    B2 Value is 10000 or 5000 or 15000 or 30000
    in C2 result want if male is greater than 10000 then 200 or greater than 7500 then 175 or 0
    or C2 result want if Female is greater than 10000 then 200 or 0

  42. SOS, I am totally lost here.
    I have 5 colons I need to take in my formula, with a total of 4 conditions and I need to calculate the following:
    IF and and Then
    condition1 D1460 G14=0 P14=0 =D14*$L$10
    condition3 D14+G140 G14=<60 P14=Y D14*$L$10+$M$9
    - and in all other cases it should be D14+G14
    How do I get them all in one field and make excel calculate the result with all those parameters? Is it possible at all?
    I tried with: IF((AND(D1460, G14=0), D14*$L$10, IF(D14+G14<=59, $M$9, IF((AND(G14=<60, P14=J), D14*$L$10+$M$9, D14+G14))))
    But the formula is obviously wrong :-(
    Since I am a linguist and the last time I had maths was in 1983, you can understand my confusion...
    Many thanks!

  43. Hi
    I have to update the three different states professional tax values (PT) in column B based on the Column A (Salary) & Column C (states). state wise & Salary wise PT slab given below. I tried with if condition. It is throwing me an error. Can you help
    Col A Col B Col C
    Salary PT State TN PT Slab
    1800 KL 12501 208.00
    5500 KL
    6000 KL KA PT Slab
    6500 TN 0-15000 0
    7500 TN >150001 200
    7600 KL
    8600 TN KL PT Slab
    9000 TN 20834 208
    25000 KA

  44. I have a table with values, no text. The data look something like this
    0.00 0.51 1.01 0.00 1.43
    0.82 2.48 5.40 1.96 7.75
    0.39 0.00 0.00 0.93 0.00

    I need to present these values in four categories:
    0 [presenting as 0]
    >0 and <0.05 [presenting as =0.05 and =1 [presenting as the actual value]

    I have not found a way for IFS to test against a range of values within a single unit of the function, as the old AND function used to enable in IF statements. Is this possible?

    1. A chunk of the four categories got erased. Here are the categories, again:
      0 [presenting as 0]
      >0 and <0.05 [presenting as 0.04999 and 0.99999 [presenting as the actual value]

  45. Thank you for better understand the if forula

  46. I can’t figure out how to write this...the cell that I’m evaluating is a %
    The formula I’ve tried: IF(B17≥65,"THRIVING",IF(B17≥50,"Ahead Of The Curve",IF(B17>35,"TURBULANT","Making Ends Meet")))
    Criteria: 66+% = Thriving,
    51-65% = Ahead Of The Curve,
    36-50% = Making Ends Meet,
    0-35% = Turbulent
    One cell I’m evaluating has a value of 45%, another of 88%...the formula says both are Making Ends Meet
    Can you help me?

    1. HI Donna,
      I thing this will you.
      IF(A11>=66%,"THRIVING","")&IF(A11<=65%,"Ahead Of The Curve","")&IF(A11<=50%,"Making Ends Meet","")&IF(A11<=35%,"TURBULENT","")

  47. 169 #N/A
    169 #N/A
    169 #N/A
    169 Punjab National Bank
    169 #N/A
    169 #N/A

    i want to have punjab national bank in all the other places where it is #N/A. please share logic to write in other column

  48. I need a formula for excel -
    If (Salary<=13000) Then 9617 Else 12022 Elseif(Salary<=24000) Then 15100 ElseiF(salary<=30000) Then (Basic*0.45)

    Help me

    1. Sub Value()
      Dim Salary As Integer
      Dim Basic As Double
      Basic = Cells(?, "?")
      Salary = Cells(?, "?")
      If Salary < 13001 Then
      Cells(?, "?") = 9617
      ElseIf Salary < 24001 Then
      Cells(?, "?") = 15100
      ElseIf Salary < 30001 Then
      Cells(?, "?") = Basic * 0.45
      Else
      Cells(?, "?") = 12022
      End If
      End Sub

  49. I am trying to find a max value from a list which belongs to another range of data. For example, when I have a data: A=1, B=2, C=3, D=4, E=5. If the list contains A, C, D, the output should be 4 (the maximum value).
    I made a formula that works (F column: A,B,C,D,E; G column: 1,2,3,4,5; J column: list)as below: =MAX(IF(F22:F29=J21,G22:G29),IF(F22:F29=J22,G22:G29),IF(F22:F29=J23,G22:G29),IF(F22:F29=J24,G22:G29))
    But I wonder, if there is any way to make the formula simpler.

  50. Please help. My Formula mentioned below is working correctly.
    =IF(I4>=9000, 2%, IF(I4>7500,1.75%, IF(I4>6000, "1.5%", IF(I4>4000, "1%", IF(I4=9000, 2%, IF(I4>7500,1.75%, IF(I4>6000, "1.5%", IF(I4>4000, "1%", IF(I4<4000, "0")))))), "0")

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