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 46. Total comments: 4557

  1. Hi there
    I need assistance with the following formula please:
    =IF(AND(K11=0),(OR(ISNUMBER(SEARCH("8000000",C11)),ISNUMBER(SEARCH("9000000",C11)),ISNUMBER(SEARCH("9100000",C11)),ISNUMBER(SEARCH("9500000",C11)))),"NO","YES")

    I need 2 criteria to be met.
    If colomn K's value is = 0
    AND the text in column C contains 8000000 or 9000000 or 9100000 or 9500000
    Result should be NO
    Otherwise YES
    Thanks for your help

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

      =IF(AND(K11=0,OR(ISNUMBER(SEARCH("8000000",C11)), ISNUMBER(SEARCH("9000000",C11,1)),ISNUMBER(SEARCH("9100000",C11,1)), ISNUMBER(SEARCH("9500000",C11,1)))),"NO","YES")

      I hope this will help

  2. I am trying to use excel to add or subtract a value if it falls into a certain ranges otherwise just place the number in the correct box.
    the ranges are:
    145.1-145.5, 146.6-146.999999,147.6-148.0 subtract .6
    146.0-146.4, 147.0-147.4 add.6
    All other just move the entered number the cell.
    I can get it to do it to one set of ranges, but it fails when I try and use multiple ranges.
    Any help would be greatly appreciated.
    Thank you

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

      =IF(OR(AND(A1>145.1,A1<145.5),AND(A1>146.6,A1<146.9999),AND(A1>147.6,A1<148)),A1-0.6,IF(OR(AND(A1>146,A1<146.4),AND(A1>147,A1<147.4)),A1+0.6,A1))

      Hope this is what you need.

    2. This is the formula that I am currently trying to use, but numbers outside the ranges are subtracted.
      =IF(OR(B2>=145.09999,B2=146.599999,B2=147.5999999,B2<=148.00001),B2-0.6,B2)

      Example. If I enter 144.100 I still get the answer of 143.500

  3. How do i do an IF formula similar to below that actually works

    =IF(A5>1=(B5*120,IF(A5<1=B5*40)))

    Please help!

  4. I was confused in formula can you please help me out
    1840 P
    1841 A
    1842 P
    1843 P
    1844 P
    1845
    I need to mark "P"(present) and "A"(absent) all numbers but i have only list of numbers which are "P" (present)
    I need to mark both P and A
    will be very thankful if you reply :)

    1. Hello Jamin!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  5. 1.) IF B1=C1 AND D1=TRUE => calculate A1*0.1
    2.) IF B1=C1 AND D1=FALSE => calculate A1*0.2
    3.) IF B1C1 => 0

  6. Anubody can help to solve this?:
    I have following conditions
    Cell A1 = value $1000
    Cell B1 = text A
    Cell C1 = text B
    Cell D1 = cell condition TRUE or FALSE (always on condition is set)

    Now I want to do following in cell E1

    1.) IF B1=C1 AND D1=TRUE => calculate A1*0.1
    2.) IF B1=C1 AND D1=FALSE => calculate A1*0.2
    3.) IF B1C1 => 0

  7. Hi, please I can't make the mega formula for the followings, please help me out:-

    Section Chapters
    I 1 to 5
    II 6 to 14
    III 15
    IV 16 to 24
    V 25 to 27
    VI 28 to 38
    VII 39 to 40
    VIII 41 to 43
    IX 44 to 46
    X 47 to 49
    XI 50 to 63
    XII 64 to 67
    XIII 68 to 70
    XIV 71
    XV 72 to 83
    XVI 84 to 85
    XVII 86 to 89
    XVIII 90 to 92
    XIX 93
    XX 94 to 96
    XXI 97

    For above infomation, how can a cell returns 'Section' in roman letters if fall in the right chapter number?

  8. Hi, I'm trying to figure out how to create a certain formula and am hoping someone can help. Here's what I want to do:

    Ex. If cells F7, F8, and F9 are NOT blank, return a value of "YES" to cell G7.

    Basically, the criteria in F7, F8, and F9 has to be checked off before G7 can be marked off as complete. I can do a "normal" If/then function referencing 1 cell, but am having trouble figuring out how to set a "True" value when referencing multiple cells.

    Thank you!!

    1. Hello Joanne!
      I hope you have studied the recommendations in the above tutorial.
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(F7<>"",F8<>"",F9<>""),"Complete","")

  9. Hi question is there is Incentive which I have to pay to my employees according to their collection please refer below and I would like to calculate their incentive " % " based on their collection. please send me formula for the same.
    Amount Percentage
    25000-30000 10%
    30001-40000 20%
    40001-50000 30%

    Name of the employees Amount collected Incentive
    John 25000
    Paul 32000
    Peter 32500
    Isaac 48010
    Lemuel 32180

  10. 9873424761 6700670610
    9873424761
    9873424761 9873424761
    6700670610
    If first and second column is different then print both
    if first column is blank and second column is number then print number
    if first and second column are same then print any one
    if first column number and second is bland then print first column

  11. Hello,
    Can you please help me how to how to get exact formula of binary computation in excel. if C5 is less than to D5 or D5 is less than C5 and multiply to 20%. Thank you

    1. Hello Johnzin!
      Your conditions "if C5 is less than to D5 or D5 is less than C5" contradict each other. One of them will always be executed. Therefore, your formula does not make sense.

  12. What is the formula

    If below 1,000, the rate is 2.00
    If 1,000 and above, the rate is 2.20

    1. Hello Marvin!
      I hope you have studied the recommendations in the above tutorial.
      =IF(A1<1000, 2 , 2.2)

  13. Hi I'm trying to figure out a if statement for my "total add-on price" column. I want my formula to be if the customer purchased three or more add-on options,(which prices are listed under the different add on options) they receive a 15% discount on all add on options?

    1. Hello Courtney!
      I hope you have studied the recommendations in the above tutorial. Please specify what what formula you used and what problem or error occurred. Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.

  14. I'm trying to use the following formula but it's not giving the correct results.

    =IF(OR(AND(J137="ASSEMBLY",K137"N"), OR(J137="ASSEMBLY",K137"Y")),G137,C136)

    What I'm looking for is if J137 = Assembly and K137 is not N or Y display G137, else display C136. I'm not sure what I'm missing here.

    1. Formula should have read
      =IF(OR(AND(J137="ASSEMBLY",K137"N"), OR(J137="ASSEMBLY",K137"Y")),G137,C136)

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

        =IF(AND(J137="ASSEMBLY",OR(K137<>"N",K137<>"Y")),G137,C136)

        I hope it’ll be helpful.

  15. Hi,

    I need to apply two condition in my excel column which IFERROR function (since if divided by 0 the value will return to "-") and second one is IF function (when the divided value become -1 and the value will return to "-").
    very appreciate if you can help me.

    thanks

    1. Hello Saleh!
      Unfortunately, without seeing your data it hard to give you advice.
      I hope you have studied the recommendations in the above tutorial.

      I recommend that you study this article on using the IFERROR function.

      1. Scenario No. 1
        Col.1 Col.2 Col.3
        100 0 x
        Scenario No. 2
        Col.1 Col.2 Col.3
        0 100 x
        Col.3 (x) is to find the percentage diff. between Col.1 and Col.2
        Scenario 1. ((Col.2-Col.1)/Col.1)=-1
        Scenario 2, ((Col.2-Col.1)/Col.1)=Div/0
        so for both scenario i need to return as "-"
        Scenario. 1 Scenario 2
        +IFS(IFERROR((Col.2-Col.1)/Col.1,"-"),"-",(G84-$D84)/$D84=-1,"-")

        Can you advise.

        Thanks
        Saleh

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

          =IFS(ISERROR((B1-A31)/A1),"-", ISERROR((F1-E1)/E1),"-")

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

  16. Hi, I'm trying to do an "if" or change an "if" statement. Right now it says
    =IF(W5=5000,W5-5000))
    When figures are put in column Z if there is nothing over 5000 then it puts (5000) in column AG and I want it to read 0. I've tried everything to make it work even reducing the formula to:
    =IF(W5>5000,W5-5000,"0") but it still puts (5000).
    So not sure how to fix this issue just want column AG to read 0 if there is any figure or a zero in column Z.
    Thanks Kathi

    1. the first formula is =IF(W5=5000,W5-5000))

      1. cut and paste is removing some of the formula: =if (W5=5000, W5-5000))
        Hope this works

        1. Hello Kathi!
          I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail.
          You put numbers in column Z, and the formula refers to column W. Is that correct? What does the AG column have to do with this?
          Give an example of the source data and the expected result.
          It’ll help me understand it better and find a solution for you. Thank you.

          1. Hi,
            Column W = Taxable Amount
            Column Z = Non-Taxable Amount
            Column AG = Results
            Example: if Column W has taxable amount less than or equal to 5000 then the result in Column AG reads zero which is correct; however, if Column Z (non-taxable) has an amount and Column W (taxable) has no amount or zero then Column AG (results) puts -5000 and the result should be zero. The only time Column AG would have an actual amount would be if Column W (taxable) is more than 5000 and then any amount over 5000 would be what shows in Column AG (results). It's where I track sales tax for revenue and to get what I report to the state. We have a DOS based accounting system that is very antiquated. I hope this helps with the explanation.
            Kathi

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

              =IF(W1<=5000,0,IF(AND(Z1>0,W1=0),0, W1-5000 ))

  17. Good day, I am trying to write a formula that calculates the accumulation of days for every certain amount of days worked, for example. for every 16 days worked, 1.25 accumulates in the next column.

  18. please coach me on the Nested functions on between and reduction % of the following:
    Between 5,001 and 7,500 - reduction 2.5%
    Between 7,501 and 10,000 - reduction 5%
    Between 10,001 and 20,000 - reduction 10%
    Between 20,001 and 30,000 - reduction 20%
    More than 30,000 - reduction 30%

  19. I Want to put condition like if value of E1 is less than 100 multiply E1 with X1 and if value of E1 is between 101 to 300 multiply E1 with X2. Please help.
    +if(E1<100,100<E1<301),"E1*X1","E1*X2")

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

      =IF(E1<100,E1*X1,IF(E1<301,E1*X2,""))

      I hope it’ll be helpful.

  20. How can I use this condition in an if statement. For example. If CA is between 20 and 25

  21. Hi,

    I am trying to put in a formula that will have 6 awnsers depending on a drop down which has 3 choices and another drop down which is a yes/No drop down. But i cant get it to work im using the IF And fuctions. I you can help it would be much appreciated.

    =IF(AND($P$4="YES",H4="STANDARD"),'Support Item Name'!C2:C8,IF(AND($P$4="YES",H4="INTENSITY 2"),'Support Item Name'!C9:C15,IF(AND($P$4="YES",H4="INTENSITY 3"),'Support Item Name'!C16:C22,IF(AND($P$4="NO",H4="STANDARD"),'Support Item Name'!C23:C29,IF(AND($P$4="NO",H4="INTENSITY 2"),'Support Item Name'!C30:C36,IF(AND($P$4="NO",H4="INTENSITY 3"),'Support Item Name'!C37:C43))))))

    1. Hello Donald!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Describe in detail all the conditions that you use. What result corresponds to each of the conditions? It’ll help me understand it better and find a solution for you. Thank you.

  22. I want to calculate a Reorder Level for Inventory.
    The Formula in text is:
    [Opening Inventory + Material Received - Material Sold = Closing Inventory]
    Closing Inventory cannot be negative and a particular material should be ordered only if
    the Closing Inventory falls below a particular level, say below 5kg.

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

      =IF(Closing Inventory<5,"Reorder",Closing Inventory)

  23. =IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]="",[@[BL NO.]]="",[@[Doc''s Sub HSBC]]=""),"NEED GSP SUBMIT",IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]"",[@[BL NO.]]"",[@[Doc''s Sub HSBC]]=""),"BANK DOCS PENDING",IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]"",[@[BL NO.]]"",[@[Doc''s Sub HSBC]]""),"PROCESS OK","WARNING")))

    Please clarify above formula logic I do not understand

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice.
      The formula uses named ranges and references to an Excel spreadsheet.
      In many cases, it’s easier to write your own formula than to try to understand someone else’s formula.

  24. Hello, thank you and Please help:

    =IF(R3219="Shopify Payments",(N3219*0.965-0.3), OR(R3219="Stripe Connects",(N3219*0.971-0.3)))

    If shopify payment on Cell R3219, multiple N3219 by 0.965 minus 0.3 cents, but if R3219 is stripe connects, multiple N3219 by 0.971 and minus 0.3 instead.

    Thank you

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

      =IF(R3219="Shopify Payments",N3219*0.965-0.3, IF(R3219="Stripe Connects",N3219*0.971-0.3,0))

      I hope this will help

  25. Hello, I am trying to combine some if statements with index match (Exact Formula Below) and have a mostly working formula except I would like to return a value if there is no value in the returning cell. Basically I am getting "1/0/1900" or "0" when the formula finds an empty cell. I am not sure if the if(len( can be added to the below? If so I am not sure where to place it :)

    =IFERROR(IF($A2="","ADD SERIAL",INDEX('RACS 06-15-2020'!CS:CS,MATCH($A2,'RACS 06-15-2020'!$M:$M,0))),"NOT IN RACS")

    1. Hello Josh!
      Unfortunately, without seeing your data it hard to give you advice.
      Perhaps this IF formula will replace 0 with another value.
      =IF(IFERROR(IF($A2=””,”ADD SERIAL”,INDEX(‘RACS 06-15-2020′!CS:CS, MATCH($A2,’RACS 06-15-2020’!$M:$M,0))),”NOT IN RACS”)<> 0,IFERROR(IF($A2=””,”ADD SERIAL”,INDEX(‘RACS 06-15-2020′!CS:CS, MATCH($A2,’RACS 06-15-2020’!$M:$M,0))),”NOT IN RACS”),"Value")
      I hope it’ll be helpful.

  26. 5. Brittany wants to identify employees who are eligible to take a CPR course at the clubs' expense. Employees who can work as camp counselors are eligible for the course. In cell M3, enter a formula using a nested IF function as follows to determine first if an employee has already been trained in CPR, and if not, whether that employee meets the qualifications to take the course:
    a. If the value in the CPR Trained column is equal to the text "Yes", the formula should display Trained as the text.
    b. Otherwise, the formula should determine if the value in the Camp Counselor column is equal to the text "Yes" and return the text Yes if true and No if false.

    1. Hello!
      Read the article above carefully. It has all the necessary information on using the IF function. You will be able to complete your task.

  27. I need to create a formula that will determine the % Error based on a low and high range for the % of error. In my spreadsheet, I have a table (shown below) that has the % of Error and ranges assigned as follows. If I want to determine the % of error on a cell value that is 29, I need to create a formula that will check all of the potential ranges in the table to find that the right answer is 20%. I have tried to create a formula using IF and AND, but can't quite get it to work. Any help is appreciated!
    % Error Low High
    10% 36 45
    20% 27 35
    40% 18 26
    60% 9 17
    80% 5 8

  28. If cell A value >50000,1% rebate is obtained. If cell A value >100,000,2% rebate , >200,000,2.5% Rebate , >300,000 3% rebate is obtained

  29. I have two columns. If there are duplicates in column A of a certain namex, column B MUST be the same for each duplicate with a true/false as the output. Example
    A1 B1
    123 t2
    123 t2
    I have over 6000 rows and have tried to use two if statements but this entails searching for the duplicates of each name which is tedious as there could be human errors and I could miss one. There is not an equal amount of duplicates for each input in column A either. I also don't know what column B should be. All i know is that they should be equal for the each duplicate of that type in column A. Let me know if you have questions

  30. I want a function that work like this
    I have this options (Invoice, Payment, Credit note, Expense) as a drop-down-list in Cell A1
    entering an Amount on Cell B1 and expect the change in Cell C1 where the balance will change when an option is selected at cell A1
    1. Invoice, the amount in cell B1 will add up to the balance Amount in cell C1
    2. if other options are selected the Amount in B1 will reduce the value in Cell C1

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

      =IF(A1="Invoice",B1+C1,C1-B1)

      The formula can be written in D1

  31. D71521151 22/05/2020 0.02
    D71519430 20/05/2020 0.27
    D71520950 22/05/2020 0.15
    D71520985 22/05/2020 0.40
    D71522327 28/05/2020 0.32

    Third coloumn value is weight and if i want to get result in 4th coloumn with conditions that if weight is <.1 then 50, If it is between .1 to .25 then 100 and if it is .25 to .5 then 150.

    How we can do this???

    Please suggest.

    1. Hello!
      To select the third value from a cell, use the formula

      =--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),20))

      Then use the recommendations from this article to create the IF formula

  32. for every full R100 due(amount after discount),the manager gives R12 to a charity fund .
    form an if statement ..

    I need help

    1. Hello!
      If I understand your task correctly, to calculate the amount of contributions to a charity fund, use the formula

      =INT(Q100/R100)*R12

      Q100 - Sales Amount
      Hope this is what you need.

  33. Greetings,
    i seeking you kindly support and assistance on below table range, how can i use the IF Function on below.
    if a transaction value 40000, the first 2999 to be rewarded @ 0.50@ and 3,000 -9,999 @ 1% and so on as on below table.

    Spend Range Domestic Cashback % International Cashback %
    0-2,999 0.50% 1.00%
    3,000 -9,999 1.00% 1.50%
    10,000-19,999 1.25% 2.00%
    20,000-39,999 1.50% 2.50%
    >=40,000 3.00% 5.00%

  34. I have multiple products in column b. What I want to do is in Column A - I need to be able to have only 3 product labels namely Alpha, beta and charlie.So for example all products in column b having the word alpha in their product name will be labeled as alpha. Then for all products with beta labeled as beta and for the rest which does not meet the criterias will be labeled as charlie..checking for your asssistance and thanks in advance.

    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.

  35. I have a nominal concentration with +- 15% acceptance criteria.
    Some data generated (at least 500), in which some are within acceptance criteria and some are out of acceptance criteria.
    I need to calculate, mean, SD, CV and nominal after including and excluding out of acceptance criteria values.

  36. 1 2 3 = =IF(A2,NUMBERVALUE(A2),B2) = Pass
    2 3 = =IF(A3,NUMBERVALUE(A3),B3) = Pass
    3 = =IF(A4,NUMBERVALUE(A4),B4)*(IF(B4,NUMBERVALUE(B4),C4)) - Fail

    Can you please let me know why 3rd formula is not working.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? In your data, B4 = 0? C4 = 0? Formula 3 returns 0. What value should formula 3 return? What result would you like to get with these formulas?

  37. Please be so kind and support me with the formula:
    =IF(AND($K13;"";$N13;"";$O13;"");"No Risk";IF(AND($K13;"";$N13;"";O13;"");"Middle Risk"))
    Somehow it doesn't work and I get only "No Risk" based on first logical test.
    Thank you

    1. Hello Olya!
      You used the same formulas for different IF conditions. Maybe you should use something like this formula

      =IF(AND($K13="",$N13="",$O13=""),"No Risk", IF(AND($K13<>"",$N13<>"",O13<>""),"Middle Risk",""))

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

  38. Hi
    I am trying to get multiple areas to display based on 2 or 3 chars. Tried this below formula but they are not returning the right Areas.Column F2 has a about 7-8 chars and i want to search the first 2 and 3 chars. Can you please help?
    Hope this makes sense. Thank you!

    =LOOKUP( LEFT(F2, 3), {"AL,N1,N2,N3,NW,EN","SL9,SL0,WD,HP","SG,LU,CM,CB,RM","GU,TW,KT,SL5,SL4","SL3,HA,UB"}, {"Area 1","Area 2","Area 3","Area 4","Area 5"})

    Below is the criteria
    GU Area 4
    TW Area 4
    KT Area 4
    SL9 Area 2
    SL3 Area 5
    SL0 Area 2
    SL2 Area 5
    SL5 Area 4
    SL4 Area 4
    SG Area 3
    LU Area 3
    CM Area 3
    CB Area 3
    RM Area 3
    WD Area 2
    HA Area 5
    UB Area 5
    HP Area 2
    EN Area 1
    NW Area 1
    N1 Area 1
    N2 Area 1
    N3 Area 1
    AL Area 1

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

      =CHOOSE(MATCH("*"&LEFT(F2, 3)&"*", {"AL ,N1 ,N2 ,N3 ,NW ,EN ","SL9,SL0,WD ,HP ","SG ,LU ,CM ,CB ,RM ","GU ,TW ,KT ,SL5,SL4","SL3,HA ,UB "},0), "Area 1","Area 2","Area 3","Area 4","Area 5")

      I hope this will help

      1. This worked. I just had to change the F2,2 and will just have to ignore the 3 chars as this will complicate allot of it more. Thanks you again Alex.

  39. I am trying to make a column populate a specific rate based on criteria in the two columns before it.

    Column 1 is their name
    Column C is their insurance (BLTC or MCLTC)
    Column D is the level assigned (Level 1, Level 2, level 3)
    Column E is the rate per day we receive based on insurance and level as they are distinct. For example if you have BLTC and are a level 2 it would be $207.31. If you are MCLTC and level 2 is would be $200.07. I would like column 4 to auto populate based on insurance and level as the rates are specific by insurance and level.

    Can anyone help me with this? Thanks in advance!

  40. Hi I'm having troubles nesting all 4 formulas together. Is it possible? Please help!
    1) =IF(AND(BF2>=62,BH2>=5),"ELIGIBLE TO RETIRE", "not eligible to retire")
    2) =IF(AND(BF2>=60,BH2>=20),"ELIGIBLE TO RETIRE", "not eligible to retire")
    3) =IF(AND(BF2>=55,BH2>=30),"ELIGIBLE TO RETIRE", "not eligible to retire")
    4) =IF(AND(BF2>=55,BH2>=10),"ELIGIBLE TO RETIRE", "not eligible to retire")
    Example #1 (BF=Age)Age 63 with BH=yrs of svc)15 years of service - should be true for both 1 & 2 arguments
    Example #1 (BF=Age)Age 57 with BH=yrs of svc)15 years of service - should be true for argument 4

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

      =IF(OR(AND(BF2>=62,BH2>=5,AND(BF2>=60,BH2>=20),AND(BF2>=55,BH2>=10))),"ELIGIBLE TO RETIRE", "not eligible to retire")

      I hope it’ll be helpful.

    2. I figured it out. Just needed to read an earlier post you provided to someone else!! Thank you for all you do! All of you Excel gurus!! Have a great day!

      =IF(AND(BF2>=62,BH2>=5),"ELIGIBLE TO RETIRE",IF(AND(BF2>=60,BH2>=20),"ELIGIBLE TO RETIRE",IF(AND(BF2>=55,BH2>=30),"ELIGIBLE TO RETIRE",IF(AND(BF2>=55,BH2>=10),"ELIGIBLE TO RETIRE","not eligible to retire"))))

  41. Hi,
    I really need your help, identifying the proble with my formula.

    if value range of is as bollow,
    For income range 20,000 to 29,999 ratio is 35% for type A, 30% for type B.
    and income range 30,000 to 49,999 ratio is 40% for type A, 35% for type B.
    Here,
    D9 = location of the cell which is a dropdown menue whether to choose A/ B
    G22 = location of the cell which is a used for input ranging from 0 - 50000
    H127 =

    =IF(AND(G22<30000,D9="A"),.35,IF(AND(30000<=G22,G22<50000,D9="A"),.40,IF(AND(G22<30000,D10="B"),.30,IF(AND(30000<=G22,G22<50000,D10="B"),.35,))))

    Thanks
    Nizam

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

      =IF(AND(G22 > 20000,G22 < 30000),IF(D9="A",0.35,IF(D9="B",0.3,0)),IF(AND(G22 > 30000,G22 < 50000), IF(D9="A",0.4,IF(D9="B",0.35,0)),0))

      I hope it’ll be helpful.

  42. Hi,
    I need your help with the below condition.
    If Cell B7=30000 then B9 should be 4(this value is in the cell H10)
    If Cell B7=50000 then B9 should be 4(this value is in the cell H11)
    If Cell B7=100000 then B8 should be 4(this value is in the cell H12)
    If Cell B7=150000 then B8 should be 5(this value is in the cell H13)
    If Cell B7=200000 then B8 should be 5(this value is in the cell H14)
    If Cell B7=330000 then B8 should be 4(this value is in the cell H15)
    If Cell B7=500000 then B8 should be 4(this value is in the cell H16)

    Kind regards
    Mohsin

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

      =IF(B7=30000,H10, IF(B7=50000,H11,IF(B7=100000,H12,IF(B7=150000,H13,IF(B7=200000,H14, IF(B7=330000,H15, IF(B7=500000,H16,"")))))))

      I hope it’ll be helpful.

  43. Hello Alexander,
    I want to decide the candidate is eligible or not, if the cell is contain NA or date ..how to write formula

  44. I am trying to calculate commission income. If gross commission is >220,000 then the net amount of commission they earn goes up. Here are my conditions:
    0-220,000 - .60
    220,001-440,000 - .65
    440,001-660,000 - .70
    660,001-880,000 - .75
    880,001+ - .80
    This is the formula I'm working with but it keeps coming back as #VALUE! =IF(OR(BR125>220001,BR125440001,BR125<=660000),BR8*BR4,"")
    What am I doing wrong?
    Thank you so much for your help, I'm ready to pull my hair out.
    Megan

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

      =IF(A1>880000,B1*0.8, IF(A1>660000,B1*0.75, IF(A1>440000,B1*0.7, IF(A1>220000,B1*0.65, B1*0.6))))

      Hope this is what you need.

      1. You are a life saver! Guess I was making this harder than it needed to be. Thanks for getting me on the right track and thank you for taking the time to help all of us!

  45. Hi Can someone help me please?

    I am trying to put a formula for example if the Price is let's say £100 to be split into three columns like column one will be 0-£50
    Column two will be for anything that is above £50 but less or equal to £60
    Column three anything that is above £60 but less or equal to £70.
    Column four anything about £70.

    If any of the conditions don't meet let's say if column two is less than £50 to bring 0.
    Any help is much appreciated.
    Mita.

    1. Hello Mita!
      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.

  46. Hello, can you kindly help me with this formula? Been stuck on this for hours.

    Total amount: Cell E29
    Discount: >3000 -10% >5000 -15% >10000 -20%

    Formula written: =IF(E29>=3000,"E29*-0.1", IF(AND(E29>=5000,"E29*-0.15", IF(AND(E29>10000,"E29*-0.2","0")))))

    Thankyou for helping! Your help is very much appreciated!

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

      =-IF(E29>=10000,E29*0.2, IF(E29>=5000,E29*0.15, IF(E29>=3000,E29*0.1,0)))

      I hope this will help

  47. Hello, I am trying to accomplish a formula that is evaluating multiple cells to return a specific answer. I am using if/and but I cannot seem to get a does not contain to work? Here is my formula, the AE2 part is where it is failing. Any ideas?

    =IF(AND(K2="Parent",AQ2="Chassis",AE2"*DECOMM*"),"Chassis/Parent","NOT Chassis/Parent")

    Supposed to work a.... K2 = Parent and AQ2 = Chassis and AE2 does not contain DECOMM then return the false/positive value.

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

      =IF(AND(K2="Parent",AQ2="Chassis", ISERROR(FIND("DECOMM",AE2,1))),"Chassis/Parent","NOT Chassis/Parent")

      I hope it’ll be helpful.

      1. Alexander, Thank you so much this worked perfectly!

  48. If column A is 1 then cell X If Column b is 1 then Cell Y otherwise C*D.
    Any help?

    1. Hello Sean!
      Your conditions contradict each other. What if both column A = 1 and column B = 1? If both conditions are met? Read carefully the IF function with multiple conditions above.

  49. =IF(E2>=70, "Excellent", IF(E2>=60, "Good", IF(E2>40, "Satisfactory", "Poor ")))
    What are the best alternatives for this formula?
    I tried = If(AND...), / If(OR...) but couldn't get the expected result.
    Little help will be well appreciated.

  50. Hi, I really need help to add a final condition to this formula below, I have tried a few different things and just keep getting errors so any input would be great if it is possible
    =IF(AND(C3=$A$3,I3<1,"Y","N") BUT IF M3="Transfer Debit"=N

    1. Hello Sabina!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail?
      What does it mean "IF M3="Transfer Debit"=N"??
      Thank you!

      1. Hi Alexander, thanks for your quick response so basically I need to add an extra criteria to the formula. The first part is fine but the extra condition is basically but if cell M3 contains the text Transfer Debit change it to N... is this even possible? Or would it be a completely different formula?
        (summary of formula - If column C = A and column I < 1 = Y but if M3 = Transfer Debit N and everything else N).. hope this makes sense, sorry I'm not great at excel so might be why i am not very clear.

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

          =IF(M3="Transfer Debit","N", IF(AND(C3=$A$3,I3 < 1),"Y","N"))

          I hope this will help

          1. Thank you so much, it worked!!!

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