Comments on: Excel nested IF statement - multiple conditions in a single formula

If someone asks you what Excel function you use most often, what would your answer be? In most cases, it's the Excel IF function. A regular If formula is very straightforward and easy to write. But what if your data requires more elaborate logical tests with multiple conditions? Continue reading →

Comments page 3. Total comments: 365

  1. HI,

    I need to calculate the IF statement between two numbers having multiple criteria:

    ex: Criteria
    from 20-30 =200
    from 31-40=300
    from 41-50=500

    B2 = 18
    B3= 25
    B4= 39
    B5=41

    With only one criteria I can calculated: =IF(AND( B2>=31,B2<=40),"300","0"), but when it comes to more I am not sure how to do it.

    Thank you.

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

      =IFERROR(INDEX(B1:B4,MATCH(D1,A1:A4,1)),0)
      A B
      20 200
      31 300
      41 500
      50 500
      D1 --- 18, 25,39 or 41

      I hope my advice will help you solve your task.

  2. Good day Sir

    Can you please help me. I have been struggling with this nested formula.
    I just need someone to please explain it to me cause I just can't grasp this.

    I need a formula which helps me with the following:

    1) If Column B & C has no date populated the Cell E should reflect a " "

    2) IF column B has a date populated and no date populated for Column C then Column E must read "Open"

    3) IF column B & C have a date populated then Column E must read "Finalized"

    I need to have these conditions nested in one in Column E

    Here's the formula I'm using, I think I'm on the right track ;

    =IF(AND(ColumnB>1,ColumC>1),"Finalised", "Open")

    Somewhere in this formula I need amend it to display " " in fields where no Date is captured in Column B

    Column A Column B Column C Column E

    Policy number: Task Start: Task End:
    1234567890 22/02/2021 16:11 22/02/2021 16:14:07

    Please help kind Sir

    1. Hello!
      Please use the following formula

      =IF(B1&C1="","",IF(AND(B1 < > "",C1=""),"Open","Finalized"))

      After that you can copy this formula down along the column.

  3. I am working with data across multiple sheets. I have a dropdown list in one sheet. I need a formula that will take the data from multiple sheets and return it to cells on the first sheet of the book based on data chosen in the drop down menu. All data is text I have tried if statements but can't seem to make it work. Any help in this matter would be greatly appreciated.

  4. Greetings,
    =IF(L16>=1,B16,IF(L15>=1,B15,IF(L14>=1,B14,"""")))
    This is the general idea of the formula I need but the data spans thousands of rows. In column L is numbers, column B is dates in descending order. The purpose is to display the date from the same row as cell value in Column L =>1. This formula works but I obviously cannot type out that many conditions. Any help will be greatly appreciated.
    V/R
    Erik

      1. A B C D E F G H I J K L
        1 1-May-20 FS 1.0 1.0
        2 3-Jun-21 BS 1.0
        3 5-Jul-19 FS 5.0 1.0
        4 3-Nov-21 BS 1.0
        5 2-Feb-21 BS 1.0
        6 3-Feb-21 1.0
        7 4-Feb-21 .7
        I apologize I don't know how to show the gridlines on here but essentially I need to scan column J for the most recent entry greater than 1 & display the date from column A of the same row. The formula would yield, based on the numbers above, 3 Feb 21 as the most recent date column J was >=1. So one result based on multiple conditions I guess. I just don't know how to get it to scan thousands of rows in a column without doing individual IF formulas in descending order. Thank you for the help.

        1. Hello!
          To find the last match of a value in a column, use the formula

          =LOOKUP(2,1/(D2:D8>=1),B2:B8)

          D2:D8 - column in which we are looking for the condition
          B2:B8 - the column from which we show the value

        2. I have looked into the Vlookup option. This function will not work due to the fact that multiple cells may have the same data & it is not in descending order. I need to scan for values >=1.0 & display the date in the same row that is closest to today. So if Cell L14=1.5 the date in B14 would be displayed unless cell L36=1.0 in which case the date in B36 should be displayed. Again, if further down L45=1.0 I want the date in B45 displayed. Thank you again!

  5. Been working at this for 3 days and my Brain is starting to hurt. I have these formulas and need to get it into 1 line formula. Each single line works but I can't get them to play nice together.

    Appreciate the help to make this work

    =if(and(A3="D",E3>2000),E3*0.25,"500")
    or
    =if(and(A3="U",E3>1200),E3*0.25,"300")
    or
    =if(and(A3="N",E3>800),E3*0.25,"200")

    1. Hello!
      Your formulas contradict each other. For example, if A3 = "" and E3 = 0, then the conditions of all formulas will return FALSE. What value do you want to write in the cell - 500, 300 or 200? Therefore, you cannot combine your formulas. Change the conditions.

  6. Hi Guys,

    Am trying to create an if formula but keep getting errors.

    I am looking at four years data and want to compare individual or collective year results to a rating
    Year 1 = 33%
    Year = 60%
    Year 3 =20%
    Year 5 = 1%
    I want to create a formula which says that if the average of year1 to Year 4 is >=60% but =60%,=60%,<=90%),"meet requirements", "not met requirements").

    Any suggestion is appreciated.

    Thanks

    1. Hello!
      Describe the background and conditions more precisely. Which means -
      Year = 60% ??
      average of year1 to Year 4 is >=60% but =60%,=60%,<=90%),"meet requirements", "not met requirements") -??

    2. Should say if Year 1 to 4 is >=60% but <=90%

  7. Can anyone guess why my formula below get error

    =IF(J2>=750000, I2+(J2/2), IF(J2>=250000, I2+(J2/3), IF(J2>=100000, I2+(J2/4), IF(J2>=50000), I2+(J2/5))))

    Thanks Guys

    1. Hello!
      Please try the following formula:

      =IF(J2>=750000,I2+(J2/2),IF(J2>=250000,I2+(J2/3), IF(J2>=100000,I2+(J2/4),IF(J2>=50000,I2+(J2/5),""))))

      I hope it’ll be helpful.

      1. Got formula parse error.. Can i send you Mr Alexander, my screenshot excel, so you can help me analyze it.. thanks a lot mr. alex

        1. Hello!
          This formula

          =IF(J2>=750000,I2+(J2/2),IF(J2>=250000,I2+(J2/3), IF(J2>=100000,I2+(J2/4),IF(J2>=50000,I2+(J2/5),""))))

          works for me. J2 and I2 must have numbers.
          What error are you getting?

          1. yes, my J2 and I2 already numbers but also get #ERROR! sir.. can you help me review my excel?
            thanks mr.alex

  8. 2 worksheets A and B

    B pulls from A

    A has 4 columns that are used to calculate monthly median in B.

    Formula: =IFERROR(MEDIAN(IF('[FY2020 Volumes.xlsx]Transplants'!$E:$E="Adult", IF('[FY2020 Volumes.xlsx]Transplants'!$K:$K="Kidney", IF('[FY2020 Volumes.xlsx]Transplants'!$U:$U>=DATE(2019,10,1), IF('[FY2020 Volumes.xlsx]Transplants'!$U:$U<=DATE(2019,10,31), '[FY2020 Volumes.xlsx]Transplants'!$Y:$Y))))), "N/A")

    This pull from Oct 2019 to Sept 2020 calculates the monthly median just fine.

    When i change the dates to 2020,10,1 and 2020,10,31 all i get is zero from Oct 2020 onwards.

    Any help is appreciated.

    thanks

    1. Forgot to mention, that column Y is the numbers from whihc the monthly median is calculated.

  9. I want to know how to calculate incentive for employees based on the below slab

    Sale Range Incentive
    0 - 2 lakh 0% ( Minimum level )
    2 - 2.5 Lakh 10%
    2.5 - 3 Lakh 15%
    3 - 3.5 Lakh 20%

    If the total sale is 2.75 lakh we have to give the employee an incentive for the amount of 75,000(2.75 lakh - 2.00 lakh ) . Out of the 75000, 50000 will be under 10% and balance 25000 will fall under 15% category. Can you help me to find a formula to calculate the incentive amount.

  10. I am trying to use 2 if statements in 1 cell.

    if H2 = Y THEN I2 WILL = E AND IF H2 = N THEN I2 WILL = F
    How can I write this in a formula. I have tried everything and cant get it to work.

    Thank you

    carissa

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

      =IF(H2="Y","E",IF(H2="N","F",""))

      Hope this is what you need.

  11. I have a data set on multiple row;
    A1 40
    A2 80
    A3 60
    A4 30
    Is there any way I can use if condition to see A1:A4 >=40 in one take, rather that each if condition check for individual row?
    Thank you.

  12. I am trying to enter a code that says if text is in the P column on row 4 then the $ amount entered in Q column row 4 would be automatically entered into a merged cell column K/L lines 4-7. I can't figure it out. Can you help me with that?

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

      =IF(P4<>"",Q4,"")

      Write this formula in merged cell column K/L lines 4-7

  13. I reviewed several formulas, entry level, and couldn't find one to do the following: compare three numbers, select the lowest of the three and once selected to input a text outcome.
    Ex: 115000/Low 125000/Median 130000/High
    They are in different boxes but on same row. G21, J21 & N21 with the text to be written on say P21. Could someone please direct me to a proper formula that I can use. Thank You!!

    1. Hello Eve!
      For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Are only numbers or numbers and text written in the cells? Do you want to display the minimum value in some other cell? Or want to select a cell with a minimum value? What does "text to be written on say P21" mean? Thank you.

  14. dear,
    please solve this,
    if i take some figures total in need how many times coming actual coming
    for ex:
    120+128+126+59+596+1669+1556 = 4254
    now i need how many times coming 600 in 4254 so need formula in excel

    thanks and regards

  15. So I use to work for a company that I helped them out with replacing parts on their machines when it got within a certain date range. I did this using excel and its the only one that I did. I currently working on something similar where lets say there is an end date of 1-6-2020 and I need a formula that gives me a red flag when the actual date gets within 30 days of that June Date. I can't remember how the formula goes. could anyone give me a little bit of advice?

    1. Assuming the end date is entered in A1:
      =IF(TODAY()+30>=A1,"Deadline coming up","Still enough time")

  16. I have an excel sheet in which "C1" column is "MODEL" & "D1" column is "LANDING".
    Another side I have created a list of MODELS in "Q1" column & LANDING PRICE in "R1" column.
    I want a formula...that if I type a model number in column "C2" landing will automatically will put in column "D2"

    1. I have an excel sheet in which "C1" column is "MODEL" & "D1" column is "LANDING".
      Another side I have created a list of MODELS in "Q1" column & LANDING PRICE in "R1" column.
      I want a formula...that if I type a model number in column "C2" landing will automatically will put in column "D2"
      And If I type any model number in the column "C2" landing price automatically will come to the column number "D2"

  17. =IF(H39>99,"5000",IF(H39>89,"4500", IF(H39>79,"4000", IF(H39>69,"3500",IF(H39>59,"3000", IF(H39>49,"2500",IF(H39>39,"2000", IF(H39>29,"1500", IF(H39>19,"1000", IF(H39>9,"500","NOT ELEGIBLE"))))))))))

    1. I have made a formula to conduct screening work from data and that is
      =IF(J13=167.64;30;IF(J13=168.91;32.5;IF(J13=170.18;35;IF(J13=171.45;37.5;IF(J13=172.72;40;IF(J13=173.99;42.5;IF(J13=175.26;45;IF(J13=176.53;47.5;J13>=177.8;50)))))))))
      but it shows that the formula is too large.
      how can I reduce it.
      Seeking help from any one.

      1. I think the issue you are having is you are using ";" to separate you if statement. You should us "," instead. Let me know if this works for you.

        James

  18. I'm trying to set up a nest. One column is why and if order error is chosen, then I'd like the next column to give me a drop down of choices for only that answer. Such as: Customer Service
    Sales, Dealer. Any ideas

    1. Hello, Lynnie,

      Unfortunately, it is not possible to solve your task using standard Excel tools and functions. Most likely you need a special macro. I am really sorry we can’t help you with this.

      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      I wish I could assist you better.

  19. Hi all,
    Need your help and widsom... is it possible to do a nesting if like? I have a raw with a GL account (3500 to 6500) and another column with finish goods & raw part number, which the first 3 numbers of a finish good will give me what color of finish good it is, and on the raw materials it's the 3rd to 5th number will give me the color, I need something like, "if, B2 has 3500, do a mid on D2 for 1-3, and or if B2 has 4000, do a mid on D2 for 3-5, is it possible to do this in excel?
    thank you in advance!

    1. Hello guys
      Rajesh Kumar, here's my example of what I was talking about on combining or nesting an IF formula with a MID at the same time.
      thank you all for your help in advance! :)

      column "E" "N" "Y"
      Product GL# Color
      01016103007 3050 =IF((N2=3500),MID(E2,5,3),IF((N2=3070),MID(E2,3,3)))
      51640200801 3070 =IF((N2=3500),MID(E2,5,3),IF((N2=3070),MID(E2,3,3)))

  20. Hello Mam!
    I have Question. Thats <> how can I do this?

    1. Hello Mam!
      I have Question. Thats If A1 is True then Multiply B2 and C2.
      How can I do this?

      1. Bappa:
        There should be a value in A1 that can be tested for true or false. For example the formula might read, =IF(A1=1,B2*C2,"A1 is not true")
        So the formula says, If A1 equals 1 then multiply the value in B2 times the value in C2 otherwise display A1 is not true.
        A1 can be text like "Yes". If you want Excel to evaluate text enclose the text in double quotes as shown here.

  21. can someone please help me with this If statement,

    so basically I want the cell B1 to do three things :
    1- if date is entered in A1, B1 needs to show that date + years so I used =IF(ISNUMBER(A1), A1+740)

    2- if A1 is empty, B1 will show "Expiry date not found"

    3- if date shown in B1 is expired I want it to Print "expired"

    so basically I have 3 statements but not sure how to arrange them in 1 if statement, can someone please help ?

    thanks

    1. Hi!

      Assuming "date shown in B1 is expired" means the date in B1 is earlier than today's date, the following formula should work a treat:

      =IF(A1="","Expiry date not found", IF(A1+740<TODAY(), "Expired", IF( ISNUMBER(A1), A1+740, "")))

      If "expired" means something different, please clarify.

  22. hey.. can u please help with this one.

    i have to make the bill and it is calculated as follow

    1, select a company.
    2, select an order number.
    3, select a colour(and its price).
    4, multiply it with the colour's price.

    i have to test one cell on four different criteria to get to the billing amount. please help me.

    1. *4, multiply the weight with the colours price.

      1. if its not self explanatory, let me know i'll send you a sample sheet which might help you.

        thanks alot guys. u all are awesome.

  23. I'm trying to create a formula for the following scenario and returning a #value error.

    If
    A = Yes and B = No
    or
    A = Yes and C = error
    then
    "Fail or Pass"

    1. Jan:
      I think this will work:
      =IF(OR(AND(D36="Yes",E36="No"),AND(D36="Yes",F36="Error")), "Fail or Pass",9)

  24. Hi,
    I am looking to create a formula that says that if cell h2 says either 1,2,3,4,5,6,or 7 it would be $287.68, $491.46, $713.82, $917.60, $1121.38, $1343.74 or $1547.52. would it be an if formula or would it be best as a having it pull from another sheet?

    1. Tasha:
      Do you mean IF H2=1 then G2=287.68 or do you mean something else?

  25. Hi there - I am looking to create a formula .... cells A and B will always have data, and I'm looking to populate cell C with a "score" based upon the criteria

    Cell A: (data will either be a W or NW)
    Cell B: (data will either be a Yes or No)

    cell C: (return score based upon one of the 4 scenarios)
    If cell A = W and cell B=Yes - return score of 0
    If cell A = W and cell B=No - return score of 5
    If cell A = NW and cell B=Yes - return score of 0
    If cell A = NW and cell B=No - return score of 10

    I appreciate any help that you can offer!

    1. Kathy B:
      OK, this is what I used to get the results you wanted.
      =IF(AND(A28="W",B28="Yes"),0,IF(AND(A28="W",B28="No"),5,IF(AND(A28="NW",B28="Yes"),"Zero 0",10)))
      Of course, you can change the cell addresses for A and B to whatever suits you.
      I entered the "Zero 0" as way to ensure the logic returned the correct result during my tests. You may want to keep this while you test it. Otherwise you can remove the quotation marks and enter a 0.
      Lastly, I will pass along these thoughts from Microsoft concerning multiple IF statements:
      While Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so. Why?
      1.) Multiple IF statements require a great deal of thought to build correctly and make sure that their logic can calculate correctly through each condition all the way to the end. If you don’t nest your formula 100% accurately, then it might work 75% of the time, but return unexpected results 25% of the time. Unfortunately, the odds of you catching the 25% are slim.
      2.) Multiple IF statements can become incredibly difficult to maintain, especially when you come back some time later and try to figure out what you, or worse someone else, was trying to do.
      3.)If you find yourself with an IF statement that just seems to keep growing with no end in sight, it’s time to put down the mouse and rethink your strategy.
      The formula we have here is relatively safe, but if you want to modify something in it, it could get confusing and error prone. Also, if someone comes behind you and wants to modify a condition or add a condition the same error problem could occur.

  26. Hello,
    Need help with formula with conditional ruling.
    Column C value ("Ship" or "Recv") will determine what formula will be implemented for Column I.
    Example:
    If C3="Ship" then I3=(H3-F3-G3)
    If C3="Recv" then I3=(H3+F3+G3)

    What is the best formula to use?

    1. Given: Column C = data input ("Ship" or "Recv")
      H3, F3, G3 = data to be solve respectively which depends on Column C
      Column I = here lies the result of your condition

      Formula in column I:
      =IF(C3="Ship",($H$3-$G$3-$F$3),IF(C3="Recv",($H$3+$G$3+$F$3),"Please Re-enter code"))

      1. Note:

        If Column C, H, F and G have their own data per column then please delete the "$" in the formula.

        Thanks

  27. SO I am having a hard time with the IF formula, for example Column A has a total number of sales, and if the sales are under 199 the customer would get $0, and if the sales are over 200 but less than 399 it would be Column A * $1, and if the sales are over 400 it would be Column A * $2. Any suggestions?

    0-199=0
    200-399=*$1
    400+= *$2

    1. GIVEN:
      Column A = Total Number of sales
      Column B = result of your condition

      Formula in Column B:
      =IF(A4=200,A4=400,A4*2,"")))

      Note: Just format the cell to accounting or such to indicate $ sign.

      Hope this helps.

  28. I'm looking for some help with this example

    1. Inspect blah for:
    a. Proper Installation
    b. Security
    c. Damage
    2. Inspect blah for:
    a. Wear
    3. Ensure blah is 20-30 lbs greater than blah.

    This is where I am at with my formula
    =IF(F2"",COUNTA($F$2:F2)&".","")
    The data above is column F
    I am trying to get it to count only if column F is not blank, but not Numeric steps if it has sub-steps (a., b., c. etc..)so that I have a column at the end that is a sequence starting at the designated number and increasing by one descending from row 2. Currently it counts each non blank cell without regard for weather or not it is a "end step". Any ideas?

    1. Tony V:
      It might be a question of how you structure the data.
      I believe you'll be able to get what you want if you structure the data like this:
      1 Inspect blah for:
      Proper Installation
      Security
      Damage
      2 Inspect blah for:
      Wear
      3 Ensure blah is 20-30 lbs greater than blah
      Numbers in column A, Inspect in column B sub steps in column C.
      This allows you to put the formula in column D. Of course you can put the data into whatever named columns you want, just be sure they are in separate columns.
      Be sure to reference the proper cell address in the formula.

      1. Thank you. That may work. If my project manager is set in his structure, is there an easy way to write the function so that (in english) if column has first character as a letter or next cell below and current cell are numeri, cout it sequentially?

        1. Tony V:
          The best way to work with data in Excel is to structure it in a manner that Excel can easily analyze. Otherwise, you'll have to ask the software to take apart the data so it can be analyzed. This requires more than just IF/AND/OR statements. In your case I think it could be done using VBA.
          At the end of the day I would recommend structuring the data so that it can be analyzed with Excel's regular functions. Then, if your manager insists on seeing the report in the fashion you first showed, structure it as I recommended, analyze the data and build a report with it. I've taken this approach many times and it works great.

  29. AND THNX DOUG FOR YOUR RESPONSE

    1. FAIQ:
      This looks like it needs an IF OR statement like this:
      =IF(K6="","EMPTY",IF(OR(K6="ABSENT"),"ABSENT",IF(K6<=1,"F",IF(K6<=49,"C",IF(K6=85,"A"))))))
      Notice the check for an empty cell comes first, then the check for the word absent. I typed EMPTY but you can type whatever word suits you. IF statements are not case sensitive so entering "ABSENT","Absent" or "absent" works.
      If you enter any word or character other than "Absent", the formula will return "A". That's as far as I got.
      Also I removed the last "N/A" because after empty and Absent there didn't seem to be any other condition to return.

  30. HI,
    I AM USING IF FORMULA FOR GRIDING I.E
    =IF(K6>=85,"A",IF(K6>=65,"B",IF(K6>=49,"C",IF(K6>=1,"F","N/A")))).
    BUT WHENEVER I WRITE SOME TXT LIKE ABSENT OR SYMBOL LIKE "-",THEN CELL CONTAING FORMULA SHOW IST CONDITION AND MARK IT AS "A".
    CAN SOMEONE HELP ME IN THIS MATTER

    1. Faiq:
      Where do you write this text?

      1. IN CELL (K6).IF SOME ABSENT AND I WRITE ABSENT OR "-" INSTEAD OF SECURING MARKS,THEN SAID FORMULA GARDE HIM AS IST CONDITION OF FORMULA MEANS GRADE HIM AS "A"

  31. Hello,

    Thanks for a very helpful knowledge. I'm stuck with this assignment for hours trying to figure out the formula. Wish u guys can help. I have set of data that needed to put into group. This is how it goes.

    A B C D E
    Item Description From To Category
    1 Cat Fish 1/2011 12/2012 Healthy

    2 Cat Fish 1/2012 12/2013 Healthy

    3 Cat Rice 1/2013 12/2013 Unhealthy

    4 Cat Fish 1/2014 12/2014 Healthy

    Based on the combination of items (Cat) and description (Fish & Rice), I can determine the category (healthy of unhealthy). My objective is to reduce the line item as to combine the range, (period from and to) since the line item 1 & 2 can be combine together from 1/2011 to 12/2013 since it goes tho the same category (Healthy).

    A B C D E
    Item Description From To Category

    1 Cat Fish 1/2011 12/2013 Healthy

    2 Cat Rice 1/2013 12/2013 Unhealthy

    3 Cat Fish 1/2014 12/2014 Healthy

    This is how I would like the end result would be. You guys have any idea how to formulate this?

    1. Sorry, this is how the range should be:

      Upper:
      A B C D E
      Item Description From To Category
      1 Cat Fish 1/2011 12/2012 Healthy

      2 Cat Fish 1/2013 12/2013 Healthy

      3 Cat Rice 1/2014 12/2014 Unhealthy

      4 Cat Fish 1/2015 12/2015 Healthy

      Lower:

      A B C D E
      Item Description From To Category

      1 Cat Fish 1/2011 12/2013 Healthy

      2 Cat Rice 1/2014 12/2014 Unhealthy

      3 Cat Fish 1/2015 12/2015 Healthy

  32. Trying to do a formula. If O10+L11+M11 is greater than 40 this cell equals 40. If O10+L11+M11 is less than 40 us that sum.

    1. A simple method to accomplish what you want is to sum O10+L11+M11 in say cell N11 by entering in N11 =SUM(O10+L11+M11). Then in say cell P11 enter =IF(N11>=40,40,N11). This means that if N11 is equal to or greater than 40 display 40 otherwise display the number in N11.

  33. if I would like to return an amount, that must meet 2 requirements/conditions form 2 other cell, how do I do that.

  34. I'm struggling to combine IF function with an OR while incorporating text values with partial match.
    This is what I've gotten so far, but I'm not sure why it doesn't work.

    =IF((OR((ISNUMBER(SEARCH("SERETIDE",AA2))),(ISNUMBER(SEARCH("SYMBICORT",AA2)))),"YES","NO"))

    Thank you for the help.

    1. Try this instead
      =IF((OR((ISNUMBER(SEARCH("SERETIDE",AA2))),(ISNUMBER(SEARCH("SYMBICORT",AA2))),"YES","NO"))

  35. 95% to 100% Silver
    100% to 102% Gold
    102% to 105% Diamond
    > 105% Platinum

    in slab calculation range for (95% to 100 % )

    how is work

    1. Hello,

      Please try the following formula:

      =IF(A1>105,"Platinum",IF(A1>102,"Diamond",IF(A1>100,"Gold",IF(A1>95,"Silver",""))))

      Hope this will work for you.

  36. Hi, can you have more than one condition to get a same result? I have a range of prices, and essentially if the price is above a certain value, a 0 score, is given. However, I want to also give a 0 score if there is no price, or the price is blank.... =IF(K218.75,"0")))))))))))))
    I want to add that if K2 is a 0 price, or if K2 is blank, my formula must also revert with a 0 value. Thanks for any help

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(OR(K2>18.75,K2=0,ISBLANK(K2)),"0")

      Hope this will help.

  37. I am trying to find a way to indicate if there is >10 consecutive duplicate values in a column and if so have a cell marked as yes but if no then have the column show a no. An example can be seen below of what I want it to look like.
    A1 B1
    Y N
    N N
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    Y Y
    N N
    N N
    N N
    N N
    N N
    N N
    Y N
    Y N

    Is there a way to get an equation that looks at consecutive duplicate values and flags them if there is higher that a set value?

    1. Hello,

      If I understand your task correctly, please try to enter the following formula in cell B1:

      =IFERROR(IF(COUNTIF($A1:$A11,$A1)=11,"Y",IF(AND(OFFSET($B1,-1,0)="Y",OFFSET($A1,-1,0)=$A1),"Y","N")),"N")

      Just select the cell where you've entered the formula and drag the fill handle (a small square at the lower right-hand corner of the selected cell) down.

      Hope this will help.

  38. I really need a summary for 4 conditions but have a problem like this

    if A4C4 or equal D4 then D4
    if A4>D4 or equal E4 then E4
    if A4>F4 then F4

    Help me pls.

    1. I really need a summary for 4 conditions but have a problem like this

      if A4C4 or equal D4 then D4
      if A4>D4 or equal E4 then E4
      if A4>F4 then F4

      Help me pls.

      1. 01 - if A4C4 or equal D4 then D4
        03 - if A4>D4 or equal E4 then E4
        04 - if A4>F4 then F4

        1. Hello,

          If I understand your task correctly, please try the following formula:

          =IF(OR(A4>C4,A4=D4),D4,IF(OR(A4>D4,A4=E4),E4,IF(A4>F4,F4,"")))

          Hope this will help.

  39. Hi there...
    i need some help if u can pls..
    i run a car renter service and i want to find which of my car earn how much in a single day
    i got some sheets in a single file these sheets got my daily sales
    i need to make a sheet which sums all the amount from all the sheets which contain same car num
    the sheets are made by driver that this driver drove this car on this date
    hope am able to convey u my issue

    1. Raza here again sorry i forgot to give an example for this:
      lets say Sheet 1 got data of driver 1 and sheet 2 got data of driver 2
      lets say driver 1 drove car A on 1st dec and drove car B on 2nd dec
      and driver 2 drove car B on 1st dec and car A on 2nd dec
      i want a sheet which just calculate that how much earning was done on that car on that day wether it b driven by more than 2 drivers

  40. HELP ABOUT THIS CONDITION
    I WANT SOLVE THIS CONDITION WITH IF IN ONE CELL

    CONDITION
    1.10000---TO---24999----20%
    2.25000-----------------20%
    3.25001---TO---49999----25%
    4.50000------------------0
    5.50001---TO---99999----30%
    6.100000----------------0
    7.100001 TO ABOVE-------30%

    PLEASE REPLY MY ?

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IFS(A1>100000,"30%",A1=100000,"0",A1>50000,"30%",A1=50000,"0",A1>25000,"25%",A1=25000,"20%",A1>=10000,"20%",A1<10000,"")

      Hope this will help you!

  41. I have a question. I have three separate columns with numbers in each cell. I a fourth column I want to place the lowest number that exist in the prior three columns in the cell. How do I go about doing this? Or is it possible?
    A B C Lowest
    100 150 200 100
    150 50 100 50

    1. formula is
      =min(A1:D1)
      example
      A B C D
      50 100 150 200 =MIN(50:200)
      FORMULA =MIN(A1:D1)
      ANSWER 50

  42. i have two tables. in first table i have to check Regid and second table Same RegID and date . After checking both conditions, i have to get the matching data.

    Ex: I was given a car for rent to different customers in different dates.
    i have customer details in one table and which customer taken vehicle on which date i mentioned in another table.

    Now when i enter customer ID and date i have to get all the details of the customer and that vehicle. can anyone help to write formula for this

    1. It will be a sql statement to join two tables based on RegID.
      Select *
      from table1
      inner join table2 on table1.RegID=table2.RegID

  43. I am working on an assignment and I have been stumped for hours. I don't know what I am doing wrong, and hopefully someone can assist me. Here it is...
    9. Many of the special staff teams require leadership training, which is offered to staff with more than 1 year of service at Camp Bright Firewood. Dean wants to identify the staff members eligible for leadership training in the table.
    In cell M2, enter a formula using a nested IF function and structured references to determine first if a staff member already has completed leadership training, and if not, whether that staff member is eligible for leadership training.
    a. If the value of
    b. the Leadership Training column is equal to the text “Yes”, the formula should return the text Completed. Remember to use a structured reference to the Leadership Training column.
    c. If the value of the Leadership Training column is not equal to yes, the formula should determine if the value in the Service Years column is greater than 1.
    d. The formula should return the text Yes if the staff member’s Service Years value is greater than 1.
    e. The formula should return the text No if the staff member’s Service Years value is not greater than 1.
    HERE IS WHAT I KEEP TRYING TO PUT...
    =if([Leadership Training]="YES","Completed", If([Leadership Training]="no"[Service Years]>1, "Yes","no"))
    I just am not getting it. I've done this problem many different ways and not geting the right response.
    Thanks in advance!!

    1. =IF(AND([Leadership Training]="Yes", [Service Years]>1), "Completed", IF([Service Years]>1,"Yes","No"))

  44. Hi I'm Mijanur Rahman,
    I want to help !!

    Actually I, get a G.P.A with four subject mark.

    Formula1: Suppose (Sub1+sub2+sub3)/3 it's Main subject GPA (3.93)
    Formula2: IF Sub4 point is sub4>4.00 then add 0.50 with main subject GPA.

    How to write two formula in one line....???
    Plz Help

    1. =if (sub4 > 4.00, (sub1+sub2+sub3)/3 + 0.50, (sub1+sub2+sub3)/3)

  45. What if I have multiple overtime pay rates. For example, I want to calculate the total pay for 64 hours worked with these conditions:
    -30 hours or less= $9.50 per hour
    -More than 30 and less than 51=$12.50 per hour
    -51 or more and less than 61=$15.50 per hour
    -61 or more=$18 per hour

    So for hour 1-30, I get paid 285. For hours 31-50, I get paid 237.50, etc. until I'm up to hour 64. I should have a total of 734 but I'm not sure how I would write the formula

    1. If (cell <31,cell*9.85,if (cell<51,285 + ((cell - 30)×12.50), 525.50+((cell - 50)×15.50))).

  46. I have a question
    trying to create a nested a nested formula with 3 arguments
    if A>720, B>16, C"IV" display RUX
    please help

    1. Hi Mohamed,

      If you want to display "RUX" when all 3 cells contain the values you specified, then use IF in combination with the AND function, like this:

      =IF(AND(A1>720, B1>16, C1="IV"), "RUX", "")

  47. How would you explain to someone who is unfamiliar with Excel how to read a nested statement that contains 3 different conditions??

    1. Hi!

      I will try to explain the logic on an example of the first formula in this tutorial:

      =IF(B2>249, "Excellent", IF(B2>=200, "Good", IF(B2>150, "Satisfactory", "Poor")))

      Translated into plain English, the formula does the following:

      1st IF: Evaluates the 1st condition (if B2 is greater than 249). If the condition is met (if B2>249), returns "Excellent", otherwise proceeds to the 2nd condition.

      2nd IF: Checks if B2 is greater than or equal to 200. If it is, returns "Good", otherwise proceeds to the 3rd condition.

      3rd IF: Checks if B2 is greater than 150. If it is, returns "Satisfactory", otherwise returns "Poor".

      In other words, the formula reads as follows:

      If B2>249, return "Excellent", otherwise check if B2>=200
      If B2>=200, return "Good", otherwise check if B2>150
      IF B2>150, return ""Satisfactory", otherwise return "Poor"

  48. I have a spreadsheet that I need help with. I have two columns, one is Regular and one is Overtime. I need the Overtime column to calculate for anything over 40 in the regular, but I need the regular column to change to 40.
    example....
    Employee has a total of 47 regular hours in column H, I need for column I which is the Overtime column to show the sum of H-40=I, but then for column H to show 40.
    Is this possible and if so how.
    basically in my mind it's If H is >40 then I would be equal to anything >40 and H = 40
    Am I crazy or can this be done?

    1. Carolyn Brown:

      IF(sum(B2:G2)<=40,sum(B2:G2),40) 40,sum(B2:G2)-40," ") <---Overtime Hours Column (should be greater than 40)

    2. Carolyn Brown:

      Do you manually put the hours in the regular and overtime columns or are there cell references for each column for each employee that populate automatically in those cells in the given work period?

      I don't remember anything before Excel 2013 so this maybe not work if you use a version older than that. I will also answer this assuming that hours populate automatically with cell references for each employee and cells B through G have the total hours worked each day from Monday to Saturday.

      For the regular hour column you can setup an IF statement.
      =IF(sum(B2:G2)40,sum(B2:G2)-40," ")

      You can also setup a data validation in the regular hours column. You can set it where only numerical values can be entered in that column and the value cannot be greater than 40.

  49. I have a formula that looks like this in a spread sheet +AZ3*IF)'YTD ALL'!$R$^'Overtime FY17'!AZ$1,'YTD ALL'!$R$6, 'YTD ALL'!$D$6))

    What does this mean? I understand that the "YTD ALL' is another sheet being referenced but I can't figure what it is pulling.

    Apparently the person whose position was more well versed on Excel than I am.

    Thank you in advance

    1. Hi Kristi, It is pulling things that are from YTD ALL tab and whatever comes after up to the comma. Example 'YTD ALL!' is the worksheet. $R$ is column R in that worksheet, $R$6 is a cell in that worksheet ect.

  50. Hello, Ms. Svetlana Cheusheva,

    Your IF condition articles are really helpful and much appreciated, I will be so grateful to you if you please help me out to set up this formula…..

    I have several dates on a sheet in a column (e.g. 18/02/2018, 27/02/2018, 29/03/2018) What I want is deduct 10 days from every date and after deducting 10 days I need to check back for the date on Saturday. For example 18/02/2018 Minus (-) 10 days = 08/02/2018, now I check back for the date on Saturday, that is 03/02/2018. 3rd February 2018 will be my result. And another example for the date 29/03/2018 where the result will be 17/03/2018. Can you please help me to build this formula?

    Thank you in advance.

    1. Hi Mr Sarder,

      Try this: ="Date"-10-(WEEKDAY("Date"-10))

      In the "Date" you can sellect a cell, eg. A2.

      1. Mr Alex, kindly help me in excel color formula.

        i wanna ... clom a = 12
        colm b = 6
        colm c= if colm A is greater then or equale to colm b ..then show red colr in ful cell otherwise white.

        siaz ali

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)