Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 19. Total comments: 2534

  1. Good afternoon,

    how could i round down a number when its decimals are smaller than 0,5 and if not it stays the same?

    1. Hi Bocirnea,

      Please use this function,

      =ROUNDDOWN(B3,1)

      Thanks,
      Regards,
      Prabath

  2. Hi Svetlana,

    Need your help
    I want a formula for these conditions

    If the labour take leave more than 5 days the value should be "no. of leave days".
    If the labour take leave less than 5 days the value should be "0".

    ex.

    8 days leave means ans should be "8".
    4 days leave means ans should be "0".

    1. Hi Saravana,

      You can use a formula similar to this:

      =IF(A1>=5, A1, 0)

      where A1 is the cell containing the number of leave days.

  3. Hi Svetlana,

    Need your help

    i was trying the formula unfortunately something wrong can you pls help

    Name Start time log in time
    12:30 12:40 late login
    12:30 12:30 on time
    12:30
    =IF(C2-B2>0,"late login","on time")

    when the third column is nil i have put the fourmula
    =IF(C4-B4>0,"late login","on time",if(c4="","leave")).

    Thanks and regards
    Giriraj

    1. Hi Giriraj,

      Try the following syntax:

      =IF(C2="","leave", IF(C2-B2>0,"late login","on time"))

  4. Hi,
    Can you help figure out what formula i'm going to use? i want to get the sum of the whole column with the same equivalent in other column. for example a1 to A10 has different value let say 2,3,4,5,6 and in B1 to B10 has its equivalent value like 100,123,121,333 etc. i want to get the sum of row with 2 value in B column and its total sum.

    1. Hello Manny,

      If my understanding is correct, you need to sum only those cells in column B, that have 2 in the same row in column A. If so, you can use the following SUMIF formula:

      =SUMIF(A1:A10, 2, B1:B10)

  5. Hello,
    I am having trouble displaying multiple matches from nested IF statements.
    As an example:
    I have a meal planner with QUICK, CASUAL, EXTRAVAGANT. QUICK = =15 but 30.
    Now say meal 1 takes 13-18 minutes, which puts it in Quick and Casual, but the validation stops after it meets the <15 logic, how can I get it to check the rest of the specifications to check the Casual block also?

    Thanks for any help.
    Sam

    1. Something didn't post right.
      Quick: first IF is less than or equal to 15, "QUICK" next IF equal or greater than 15 but less than equal to 30, "CASUAL", third IF equal to or greater than 30 "EXTRAVAGANT". The rest is correct as I put in a meal that takes 13-18 minutes, how to I have the formula block identify it as a Quick Casual Meal.

      Thanks

      1. Hi Sam,

        You can use the following nested If functions:

        =IF(A1>=30, "EXTRAVAGANT", IF(A1>=15, "CASUAL", "QUICK"))

  6. Hi

    I have 4 cells in a row, 1 has a date in and the other 3 are blank.

    eg

    A1 - Blank
    B1 - Blank
    C1 - 01/04/2016
    D1 - Blank

    Therfore I want the answer in E1 with the date in.

    The next row may be

    A2 - 05/04/2016
    B2 - Blank
    B3 - Blank
    C$ - Blank

    I want column E1 to always give me the date from whichever column has a date in, can you help please? Ive tried alsorts of formulas which either come up with an error or TRUE.

    1. Hi KD,

      Please try the following formula:

      =IF(NOT(ISBLANK(A1)), A1, IF(NOT(ISBLANK(B1)), B1, IF(NOT(ISBLANK(C1)), C1,
      IF(NOT(ISBLANK(D1)), D1, "blank"))))

  7. Hi,
    I would like to know if you could advice me on this, I have two numbers in different cells, what I want is that, if these numbers are similar, on a third cell obtain the text "OK" and "Error" if they're different; the detail is that the data isn't precise, I mean one cell has 1.2 and the other one has 1.3, and they are similar but not equal, so what I want to know, is it possible to use an IF with a certain percent of tolerance?

    1. Hi Victor,

      I think you can use a formula similar to this, where 0.1 is the "tolerance":

      =IF(ABS(A1-B1)<=0.1, "OK", "Error")

      1. Yes thats the same method I used, thanks! it work perfectly!

  8. I have a document with multiple sheets. I need to use one for a "master" entry form to keep continuous record. A second sheet "Current" would record the most recent data entered pertaining to an exact unit. In this case lockers assigned to employees. When a locker is reassigned I would like the spreadsheet to transfer the data to the "Current" spreadsheet for an updated active list. Is this possible?

    1. Well you could use a VLOOKUP or if you don't edit the "Current" sheet you could manage it as a Pivottable instead of a separate SHEET you would only have to press the update on the pivottable.
      If this was helpful or if you want to know something you can always ask c:
      Hope this was useful.

  9. Hi

    I am trying to get this formula to work but it keeps on coming up with #NAME when there is no data. If there is a “P” in either bb7 or bc7 then it works.

    Basically I am trying to work out whether a pupil has a unit pass. Not sure if you will be able to work out from the formula what I am looking for but here is the explanation:
    There must be a “P” in either BB7 or BC7 AND the following before a unit pass is awarded. (BK7 or bf7), (Bl7 or bg7), (bm7 or bh7).

    Here is the formula:

    =IF(BB7=”p”,”P”,AND(IF(OR(BK7=”p”,BF7=”p”),IF(OR(BL7=”p”,BG7=”p”),IF(OR(BM7=”P”,BH7=”P”),”P”,””)))))

    1. Sorry - Wrong formula above. This is the one.

      =IF(OR(BB8="p",BC8="p"),"P",AND(IF(OR(BK8="p",BF8="p"),IF(OR(BL8="p",BG8="p"),IF(OR(BM8="P",BH8="P"),"P","")))))

  10. Hello,

    i need to put the ending filter to IF function. How can i write the formula?

    Ex:- if this ending with "I" (AL-FRE-EXP-I), True = Invoice, False = Credit Note

    Thanks,
    Prabath

  11. If I want to use the IF/OR function, but use multiple lines of text (ie, a column), can that be done?

    For example: =IF(OR(B3=O$2:O$360),"TRUE","FALSE")

    1. Hi RCL,

      If I understand you correctly you should use the following formula:
      =IF(COUNTIF(O$2:O$360, B3) > 0,"TRUE","FALSE")

  12. i am trying this formula but return by you enter more than no of nested permited by this formula is there any other way

    =IF(I37<50,VLOOKUP(C31,mainrates,7,0),IF(I37<100,VLOOKUP(C31,mainrates,8,0),IF(I37<250,VLOOKUP(C31,mainrates,9,0),IF(I37<500,VLOOKUP(C31,mainrates,11,0),IF(I37<1000,VLOOKUP(C31,mainrates,12,0),IF(I37<2000,VLOOKUP(C31,mainrates,13,0),IF(I37=5000,VLOOKUP(C31,mainrates,15,0)))))))))

    thanks

  13. i have 5 cell like:
    5
    4
    6
    7
    8
    10
    14
    when use if(a2<=5,3,if(a2<=8,5,if(a2<=10,8,if(a2<=14,10)))
    give me embty cell

  14. Trying to do a formula to calculate the following:
    Service Year is in Column A
    IF A>5 then (A-5)*2+5
    IF A<2 then "2"
    IF A is less than 5 but greater than 2 then = A

  15. Dear
    I have to use a formula that can divide the resultant column into three levels (Low, medium, and high). Suppose some values “X” is present in cell “C”. The formula should be like this
    If value in C is less than 50, result should be Low level
    If value in C is greater than and equal to 50 and less then 75, result should be Medium level
    If value in C is greater than and equal to 75, result should be Low level
    Please help me how can I develop this formula in excel?

    Thanks

    1. Hi Babar,

      Please try the following formula:
      =IF(C1<50, "Low", IF(AND(C1>=50, C1<75), "Medium", "High"))

  16. I am attempting to create a column that will assign a number (1-5) dependent upon the combination of the contents of two separate cells. These cells contain either "NONE", "no", or "yes"

    =IF(AS2=”NONE”,5,IF(AND(AS2=”no”,AT2=”no”),0,IF(AND(AS2=”yes”,AT2=”no”),1,IF(AND(AS2=”no”,AT2=”yes”),3,IF(AND(AS2=”yes”,AT2=”yes”),4)))))

    As it stands I am receiving a #NAME? error.

    1. Did you ever receive feedback or know any more about this? I'm attempting something similar and I am struggling!!

  17. How to formula this:

    Deadline of submission is 10:00am but he is delayed in submitting the reports by 10:05 am so i want to get the difference in time also and in other column i want to appear the words advance or delay i want to be in this format:

    10:00am - 10:05 am = -5minutes(color red) = delay (color red) if on time or advance like this

    10:00am - 08:00 am = 2 hours (color green) = advance (color green)

    10:00am - 10:00 am = 0:00:00 (color green) = on time (color green)

    thank you

    1. Jan,
      Post #312 above is what you want to do with your formulas and it refers to what is called Conditional Formatting. In the above Post the cells are colored but in your case the text would be colored instead. Play around with the info but where it indicate "Fill" use the "Font" to change colors.
      I will play around with the formula using the time format but if anyone can also help you sooner with your formula above I can help with providing addition help on the conditional formatting of the text.

  18. Can I use this formula for text? I am trying to locate a specific word in a cell (in column A) and assign a value to that cell in column B. Here is my formula, but the error message says I have too many arguments entered. Please help!

    =IF(ISNUMBER(SEARCH("LABEL",A7)),3919.9,"",
    IF(ISNUMBER(SEARCH("CONTAINER",A7)),4901.10,"",
    IF(ISNUMBER(SEARCH("INSTRUCTIONS",A7)),4901.10,"",
    IF(ISNUMBER(SEARCH("TAGS",A7)),4821.10,"",
    IF(ISNUMBER(SEARCH("NAME PLATES",A7)),8310.00,"",)))))

    1. Remove the "", at the end of the first four lines, leaving it at the end of the last part - should work.

  19. Hi thank you for these blogs, I noticed above in this formula, =IF((C2+D2)>=60, "Good", IF((C2+D2)>=>40, "Satisfactory", "Poor ")) there is an extra >
    If I could request your next lesson, it would be in arrays and tables within formulas. Thanks

  20. Hi,

    Im trying to use the formula IF to do the following:

    If in Cell P9 = PASS then Cell Q9 displays a value i have logged in cell P2 (which I have named the cell scpass) in same respect if P9 = FAIL then the value shown in Q9 displays a value I have logged in P3 (again I have named this cell SCFAIL)

    I have used this formula =IF(P9="PASS","SCPASS","")&IF(P9="FAIL","SCFAIL","")

    It works but rather than show the value logged in these cells it just displays the name I have put in the formula...

    Please help!!

    1. Adam,
      On the formula you used when you break it down:
      =IF(P9="PASS","SCPASS"," "") - When you type the word PASS on cell P9 then Excel goes to the next string which is the true statement and you have indicated to be true is the actual word "SCPASS". What you want to do is to reference the cells P2,Q2 in the formula for your true statement. If you change the words in cell P2 or Q2 then if would reflect in the formula what you typed on the cell.

      Do this instead:

      =IF(P9="PASS",P2,"")&IF(P9="FAIL",Q2,"")

  21. I'm looking for a formula so I can make the text in one cell appear in another, within my IF statement.

    Eg:

    A B C

    Anna 90 This is an excellent score, (Anna) should be very proud of herself

    How can I tell excel to write the text in (e.g.) cell A1 into (e.g.) cell C1, while still using the IF statement for cell (e.g.) B1?

    1. Katherine,
      Below is I think what you were looking for. This is a simple one with cell A1= "A" and cell A2 would be Anna. The formula can be located in any cell. Would get intensive if you have multiple names and grades if this is what you are trying to accomplish.

      =IF(A1="A",A2 &" " &B2 &" This is an excellent score, (" &A2 &" ) should be very proud of herself."," ")

  22. =IF(Q4=1,"A",IF(Q4=2,"B",IF(Q4=3,"C",IF(Q4=4,"D",IF(Q4=5,"E",IF(Q4=6,"F",IF(Q4=7,"Provisional")))))))

    Hi ive used the above formula to return a text response to a number result but its not working consistently. The number can be from 1-8 and there are corresponding text responses for each. What can i do?
    Cheers
    L

    1. Lorraine,
      Not sure what you are trying to accomplish but if cell Q4 is your input value and the formula that you have done is located on another cell. The formula works as long as Q4 is where you type your input numbers.
      The number can only be from 1-7. If you type 8 or any other number the cell in your formula would indicate "FALSE" due to the "If statement" only has 7 conditions to check starting from 1 and end in 7.

      1. =IF(Q43>=8,"Probationary",IF(Q43>6,"Provisional",IF(Q43>5,"F",IF(Q43>4,"E",IF(Q43>3,"D",IF(Q43>2,"C",IF(Q43>1,"B","A")))))))

        Hi Pete, thanks for your response. I've changed the formula to be whats above which appeared to work but again its not consistent. I have 2 examples where the number in the target cell, in this case Q43, has been 6 and the response returned in the destination cell was "provisional" whilst other cells have shown the correct response of 'F". The numbers 1-8 are results of a median formula in the adjacent column.

        Any further thoughts?

        1. Hello again, ive looked at the entries again and there appears to be something in the array of numbers that may explain whats happening... (then again i might just have come across a coincidence!)
          Examples are two median results of '5' but one is showing an 'E' response and the other an 'F'
          The median of the number sets when written out are almost identical:
          1st one: 4,4,5,5,5,6,6,6,7
          2nd one: 4,4,5,5,5,6,6,7,7
          both median numbers are 5 with a 5 and a 6 on either side.

          I also considered using the mean value (which could work with these examples as there are no outliers) and when you look at the mean of these number sets the 1st is 4.2 (rounded to 4) and the latter 4.9 (rounded to 5). I need the median when i consider the purpose of this exercise and other examples - there are 87 rows of data) and in some examples the mean is not reflective.

          Could the use of the median be the issue?

          1. Lorraine,
            Based on what you are testing to get the results.

            Using the mean can give an erroneous result which may not be what you want when using your new "If" statements with the greater than test. Sometimes excel may show a number but the calculation may not reflect the result.

            Median seam to work with your test to get the calculations and result that you would be expecting.

  23. Hi Svetlana Cheusheva,

    =IF(E142000,E143500,E145000,E147500,"-$3.00","")

    My formula as above, why my as above value never deduct inside the SUM total?

    Any formula i missing out in above formula?

    1. Hello May,

      The syntax of this formula is wrong (or maybe it was broken by our blog engine when posting, sorry if it's the case). Anyway, you should never use any currency signs like $ in Excel formulas because it's only a visual representation of the underlying value. Also, surrounding a number in double quotes turns it into a text string, and as the result Excel cannot perform any calculations on it.

      If you can explain what the formula is supposed to do, I will try to help you make it right.

  24. what is the expected result of following formula?
    =IF(2<3,IF(4<3,1+1,2+2),3+3)

  25. I need to know what is the right way to have both AND OR in one statement.
    A and B have 3 status C, N, or O;
    If (A=C or A=N) AND (B=C or B=N), output should be C, otherwise is O
    Thank you.

    1. Hi Frieda,

      Here you go:

      =IF(AND(OR(A1=C1, A1=N1), OR(B1=C1, B1=N1)), C1, 0)

  26. I can't figure out why this formula isn't working:

    =IF(e2="AB", (F2="Cash", J2, (J2*40%)), IF(E2="LSI", (F2="Cash", J2, (J2*60%)))

    1. Christie,
      This is a logical function where the "IF" and "AND" operators need to be involved. Is this what you were looking for on the formula to do.Try this revision to your formula above.
      You can also use a drop down on cell E2 instead of typing the two words.

      =IF(AND(E2="AB",F2="Cash"),J2*0.4,IF(AND(E2="LSI",F2="Cash"),J2*0.6))

  27. Hi Svetlana,
    Pls what formula do I use to calculate, lets say, the number of "A"s in a group of grades?

    example,

    Final Grade
    74.1 A
    52.2 C
    69.6 B
    32 F
    70.2 A
    45.6 D
    47.1 D
    53 C
    41.4 D
    59.3 C
    30 F
    45 D
    63.7 B
    73.2 A
    71.2 A

    GRADE NUMBER
    A ??
    B ??
    C ??
    D ??
    F ??

    Pls help. Thanks

    1. Hi Usman,

      You can use the COUNTIF function for this. For example:

      =COUNTIF(B:B, "A")

      Where B is the column containing grades.

  28. Hi Savetlana,

    when I input 17 or 20 digits in Cell F8 the result should be "Pass" otherwise "Fail"

    also note that I am checking the length of digits in the cell.

    below condition is not showing positive result.

    =IF(OR(LEN(F8)=17, LEN(F8)=20), "Pass", "Fail")

    Regards,
    Khalid

  29. I am using below

    =IF(OR(AND(LEN(TRIM(F8 = 17)), AND(LEN(TRIM(F8 = 20))))), "Pass", "Fail")

    1. Hello Khalid,

      From your formula, it's difficult to understand what result you are trying to achieve. I have the following suggestions:

      Show "Pass" if cell F8 contains 17 or 20 characters, "Fail" otherwise:
      =IF(OR(LEN(F8)=17, LEN(F8)=20), "Pass", "Fail")

      Show "Pass" if the value in cell F8 is either 17 or 20, "Fail" otherwise:
      =IF(OR(F8=17, F8=20), "Pass", "Fail")

      If you are looking for something different, please clarify.

  30. i am trying to output a letter code base on ranges for example if a cell has numbers from:

    -1 to -26 should output text "A"
    -27 to -54 "B"
    55 TO 25 "C"
    24 TO 6 "D'
    5 TO 1 "E"

    for example if a cell has -23 output would be A

    i tried some of the above but often i get EBC various letters together

    1. Hello Joe,

      Try the following formula:

      =IF(AND(A1>=25, A1<=55), "C", IF(A1>=6, "D", IF(A1>=1, "E", IF(A1>=-26, "A", IF(A1>=-54, "B", "")))))

  31. Hi
    I'm trying to create a formula to total a cell based on two conditions
    Column D is titled Activity: Running, Cycling (Condition 1)
    Column K is titled Distance: 5, 10
    Column S is titled Company: Paul, John (Condition 2)

    I can calculate the total amount of running and cycling by using this formula =SUMIF(D3:D57,"Running",I3:I57)
    What I want to do is expand this formula to calculate the amount of running with John or Paul only based on Column S.

    Can you help?

    Thanks Paul

    1. Hi Paul,

      To sum cells with several conditions, you need to use the SUMIFS function rather than SUMIF. For example:

      =SUMIFS(I3:I57, D3:D57,"Running", C3:C57,"Paul")

      1. That worked perfectly.. I just copied in your formula :-)
        Thank you so very much you are exceptional.
        Paul

  32. I am working on the excel sheet in which i have to put the following conditions like
    if gpa is in the range of 3.81- 4.00 then assign the marks 20
    if the gpa in the range of 3.61- 3.80 then assign the marks 10 and so on for the other gpa what is the solution to this kindly any one can help me
    Thanks

    1. Hi Nayab,

      You can write a nested If formula similar to this:

      =IF(A1>3.8, 20, IF(A1>3.6, 10, IF(...)))

  33. There are 1000 values in one column (C). I need to populate the value '1', if the value in column (C) is equal to 'A','B','C','D' or 'E'. Else, I need it to populate zero. Basically, my simple formula of =if(C1="A",1,0) needs to be extended to A or B or C or D or E - all in one cell formula itself.

    1. Hi Gurgaa,

      Simply embed the OR function in your formula, like this:

      =if(OR(C1="A", C1="B", C1="C", C1="D"), 1, 0)

  34. Hello dear Svetlana,
    If add one more part that is, if cell5 (total) be between 50 and 59 grade C, between 60 and 79 B, between 80 and 100 grade A (for level1,level2 and level3). if cell5(total be between 60 and 65 grade C, between 65 and 79 grade B, between 80 and 100 grade A.
    your kindness will be highly appreciated.

    1. Hi Gul Mohammad,

      You can use the following nested If functions to display grades in a separate cell:

      =IF(AND(OR(A4="level1", A4="level2", A4="level3", A4="level4", A4="level5"), A5>=80), "A", IF(AND(OR(A4="level1", A4="level2", A4="level3"), A5>=60), "B", IF(AND(OR(A4="level1", A4="level2", A4="level3"), A5>=50), "C", IF(AND(OR(A4="level4", A4="level5"), A5>=65), "B", IF(AND(OR(A4="level4", A4="level5"), A5>=60), "C", "")))))

      1. Hi Svetlana:
        I think you missed one and that is,you have not specified the Grade A for level four and level five. Now how do i continue?
        should I just add like the others?

        1. Hi Gul Mohammad,

          Because Grade A is identical (between 80 and 100) for all the levels, I added 5 levels in the first logical test to make the formula a bit more compact.

  35. Thank you too much for helping us. I will appreciate if you could help me with this function.Suppose if cell4= level1,level2,level3 and at the same time cell5(total score) be greater than 50,the student is pass otherwise fail.
    And again if cell4= level4,level5 and at the same time cell5(total score) be greater than 60, the student is pass otherwise fail.
    It will be your kindness if you could help to handle it.

    1. Hello Gul Mohammad,

      Try the following formula:

      =IF(OR(AND(OR(A4="level1", A4="level2", A4="level3"), A5>50), AND(OR(A4="level4", A4="level5"), A5>60)), "pass", "fail")

      1. Thank you so much dear Svetlana Cheusheva.
        It really helps me.
        I appreciate your helping and also your talent.
        best wishes

  36. Is there any formula for the condition where have to use "OR" more than twice.
    ex:
    A B C D Y=YES
    Y N N Y N=NO

    I want a condition : =if(A OR B OR C OR D ="Y","AGREE","NA")

    i want to use "or" more than twice. Is it possible?

    1. Hello Naveen,

      Of course, it's possible. Like in any other Excel function, you can include up to 255 arguments in an OR statement as long as the total length of your formula does not exceed 8,192 characters :)

      =IF(OR(A1="Y", B1="Y", C1="Y", D1="Y"),"AGREE","NA")

      1. Hi Svetlana,

        For Naveen's query we could also use this formula

        =IF(B1="A","Y",IF(B1="B","Y",IF(B1="C","Y",IF(B1="D","Y",IF(B1="Y","N",IF(B1="N","N"""))))))

        Substituting any other text for "Y" or "N" within the brackets.

        But don't you think that a Table with the two columns - one with the condition and other with the output/result, will help this problem with a Vlookup formula. The table can be expanded without having to expand the formula or make it more complex.
        I have been using this method with excellent results and as an Excel professional I would be glad to know your views.
        Regards,
        Ramki

  37. its related to end of service calculation

    as per our policy, first 5 years of service, gratuity will be 1 month for each year( ie 30 days X 5) and next 5 years, it is 1.5 month (5and remaining service period it is 2 months.

    what should be formula which will check 4 year service, 8 year service and 12 year service.

    kindly help me.
    thanks

    1. Hi Krishna,

      Based on the information provided in your post I give below the formula which should work for you:
      A B C D
      2 Years worked Months earned
      3 6 6.5
      4 Cross check calculation
      5 Year split Entitled Months
      6 5 1 5
      7 1 1.5 1.5
      8 0 2 0
      9 Total 6.5

      IF(B25,IF(B210,(5*1)+(5*1.5)+(B2-10)*2,0))))

      Let me know if this worked for you,

      Regards,
      Ramki

  38. I need help in formulating a complicated IF statement.

    So I want a spreadsheet that has one cell that will calculate the correct percentage that is marked with an "X". (However there are five different percentage choices that can be marked are 2%,3%,4%,5% and 6%.)

    I started the following Nested IF formula but I'm screwing it up, maybe I need to use an IF/OR formula but can you please help me?

    =IF(E4="X",(D4*0.02),0,IF(F4="X",(D4*0.03),0,IF(G4="X",(D4*0.04),0,IF(H4="X",(D4*.05),0,if(G4="X",(D4*.06))))

    1. Hi Gabriel,

      I think a nested If formula is the right approach. Try this one:

      =IF(E4="X", D4*0.02, IF(F4="X", D4*0.03, IF(G4="X", D4*0.04, IF(H4="X", D4*0.05, IF(G4="X", D4*0.06, "")))))

  39. Hi Rebecca,

    Try the following nested If's:

    =IF(A1>4999, A1*15%, IF(A1>2999, A1*12%, IF(A1>594, A1*10%, "")))

    1. Thank you so much for your help!

  40. i have a question regarding the first example: IF/AND formula: =IF((AND(C2>=20, D2>=30)), "Pass", "Fail")

    Does the order in the "AND" segment matter? I.E. if it was IF((AND(D2>=30, C2>=20)), "Pass", "Fail"), would that change the result?

    1. Hi Kevin,

      The order of arguments in AND and OR statements does not matter at all. You can put them in any order and the result will be the same.

  41. Hi, I want to achieve a dollar value and I have created this =IF(F9>2888, "100", IF(F9>1588, "50")). But I have getting 50 and 100 as a text even I try to convert it into number.

    Can help?

    1. Hi Kerine,

      The point is that Excel interprets any value enclosed in double quotes as a text value. So, simply remove the double quotes surrounding 100 and 50, and your formula will work fine:
      =IF(F9>2888, 100, IF(F9>1588, 50))

  42. I want to use If A1 = "82041100 / 82041200 / 82042000" and A2 = "China" then A3 = 150 otherwise 200. Could any one tell me what would be the syntax of the that formula in excel.

    1. Hi Muddassir,

      If you mean A1 contains exactly that text string, use this formula:

      =IF(AND(A1="82041100 / 82041200 / 82042000", A2="China"), 150, 200)

      If you mean A1 contains any of those 3 numbers, use this one:

      =IF(AND(OR(A1=82041100, A1=82041200, A1=82042000), A2="China"), 150, 200)

  43. Hello,

    I am trying to write a IF function in excel but not successful :(

    I want to write a formula for percentage calculation IF the column is an integer. No percentage calculation if the column zero and not an integer.

    Hope to receive your reply. Thanks.

    1. Hi Terrance,

      You can use a formula similar to this (just replace A1*10% with the percentage calculation you want):

      =IF(AND(A1<>0, INT(A1)=A1), A1*10%, "")

  44. Hi,
    I want to use the following function, can I?
    IF(A1=0;B2=A1*S3;B2=A8*S6)

    Regards,

    1. Hi Shaher,

      If you are writing a formula for B2, you don't need to reference it in the formula:

      =IF(A1=0; A1*S3; A8*S6)

  45. i have problem in IF condition in Excel. B1 is drop down i select one value the automatically how to change in B2. like this........

    B1=dropdown 1,2,3
    B2=values like 1=6.2
    2=5.6
    3=6.6
    how can do this problem please tel me or send answer my mail
    thanking you

    1. =IF(H17=1,6.2,IF(H17=2,5.6,IF(H17=3,6.6)))

      thank you

  46. =IF(N15=4,0%))))

    I wrote the formula above, but it is returning "False" insteady of an actual number, anybody can help to identify how I can correct this?

    1. this formula was treated =0 why because u r typing in cell N15 some other vales give like 3 or 10. that is not correct vales. the correct vale is only 4. but why u r give IF condition like Cell N15=0% after give in cell N15.
      =IF(N15=4,0%)

  47. Hi,
    Here is my formula. its not working.
    =IF(AND(AR2>=0.9, AR2<=0.7), "C", IF(AND(AR2=0.5), "B",IF(AND(AR2=0.1), "A", "D")))

    I am setting up KPI metrics, so based on the data, i have named each value in categories.
    If X is between 0.9 to 0.7 = C,0.6 to 0.5=B, 0.4 to 0.1=A, then if is over 0.9=D

    1. Hi
      Sri

      =IF(AR2>0.9,"D",IF(AR2<=0.4,"A",IF(AR2<=0.6,"B",IF(AR2<=0.9,"C"))))

      Regards
      Manish

  48. I need an IF formula to reflect this information:
    If cell A1 equals:
    Weighted % Points
    20%+=3
    10.1-20%=2
    5.1-10%=1
    0-5%=0

    1. IF(A1<5%,0,IF(A1<=10%,1,IF(A1<=20%,2,3)))

  49. I think I have a simple request but have been unable to figure this out.
    Cell A1 is used for a price to be input.
    Cell B1 needs to output a fee based on the price input into cell A1

    The parameters would be as follows:
    In A1 $1999 and below should equal $125 in B1
    In A1 $2000-$4999 should equal $225 in B1
    $4999 and greater should equal A1*.05

    Ive tried IF, AND, & OR functions and I can't get it. Please help.

    1. Hi Terrence,

      You can use the following nested IF's:

      =IF(A1>4999, A1*0.05, IF(A1>1999, 225, 125))

      1. Thanks!

  50. I am stuck! I have tried all the formulas I could find on conditional formatting to try to get the following result:

    If C2<10 and W2=0 Fill background Red

    Hope someone can help:)

    1. Heather,
      Go to the cell C2 or W2 you want the the conditional formatting to occur.
      Click on the Conditional Formatting Icon and select Manage Rule. You will see a pop-up window the Conditional Formatting Rule Manager.

      Click New Rule.. and select "Use a formula to determine which cells to Format" on the Rule Type.
      On the empty line "Format values where this formula is true:" enter the formula =c2<10 or click the box on the end of the line which will allow you to pick the cell C2.

      Now select the "Format.." button and there are tabs "Number", "Font", "Border" and "Fill". Select the "Fill" tab and under the "Background Color: select the Red color and the Sample window should show red. Click "O.K" to finish up.
      Once you type numbers on the cell the formatting should occur if the condition is true then the cell will highlight red.

      Hope this helps.

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