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

  1. I have a sheet with 2 tabs. in the first sheet i have 4 different scores. 1, 0,5, 0 and blank. In the next tab I need a formula that return 1 and blank as 0 and the o and 0,5 as 1.

    How do I do the formula

    1. Hello!
      Please use the following formula

      =IF(OR(A1=1,A1=""),0,IF(A1=0.5,1,""))

      or

      =IFS(A1=1,0,A1="",0,A1=0.5,1)

      I hope it’ll be helpful.

  2. Thank you so much, Alexander, for such lucid explanation of these really useful Excel functions. All of these were a great help!

  3. If the decimal number is lower than .5 then round down
    If the decimal number is higher than .5 then round UP
    if the decimal number is equal to 0.5 & if the number is odd then ROUND the int number to same odd number only
    If the decimal number is equal to 0.5 & if the number is even ,then round UP the int number to nearest odd number

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

      =IF((A2-INT(A2))=0.5, IF(MOD(INT(A2),2)=1, INT(A2), ROUND(A2,0)), ROUND(A2,0))

    2. Okay I got the answer No need to reply!
      Thanks

  4. Hello

    I'm trying to combine a IF and SEARCH function in a formula where if it finds in S1 "WSPD" If true "WSPD" if false Search in S1 "WSTM" if True "WSTM" , if false Search "WSAC" in S1 if true "WSAC" if False Search in S1 for "WSAD" if true "WSAD" .... If any of does not contain WSPD or WSTM or WSAC or WSAD.... then "WSSC"

    I've put this formula
    =IF(SEARCH("WSPD",S4),"WSPD",IF(SEARCH("WSTM",S4),"WSTM",IF(SEARCH("WSAC",S4),"WSAC",IF(SEARCH("WSAD",S4),"WSAD",""))))

    It work to detect WSPD, but doesn't work for all other condition it just puts #VALUE

    Thanks for your help to find where I did wrong

    1. Hello!
      Please use the following formula

      =IF(ISNUMBER(SEARCH("WSPD",S4)),"WSPD", IF(ISNUMBER(SEARCH("WSTM",S4)),"WSTM", IF(ISNUMBER(SEARCH("WSAC",S4)),"WSAC", IF(ISNUMBER(SEARCH("WSAD",S4)),"WSAD",""))))

      Hope this is what you need.

      1. Yes it worked perfectly fine many thanks

  5. Is there a way to include multiple AND statements in the IF formula? I am trying to populate response in Col A based on amounts in Col B and Col C.

    - If Col B and Col C are both less than 0, then response 'Loss'
    - If Col B and Col C are both greater than 0, then response 'Income'
    - If Col B less than 0 and Col C greater than 0, then response 'Loss / Income'
    - If Col B greater than 0 and Col C less than 0, then response 'Income / Loss'

    Currently excel is allowing only two conditions. When I add the third it says "You have entered too many arguments for this function".

    This works =IF(AND(B1<0,C10,C1>0),"Income"))

    This gives error message =IF(AND(B1<0,C10,C1>0),"Income"),IF(AND(B10),"Loss / Income"),IF(AND(B1>0,C1<0),"Income / Loss"))

    Thanks!

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

      =IF(AND(B1 > 0,C1 > 0),"Income",IF(AND(B1 < 0,C1 > 0),"Loss / Income",IF(AND(B1 > 0,C1 < 0),"Income / Loss","")))

      Hope this is what you need.

  6. I am wondering if the formula has to include the absolute value, or if it can be a range. For example: I am trying to use the formula if the value is between 5.8-6.1 it will read as "Good". Is this possible to achieve, or can it only ever be = to one absolute value? This is the formula I have tried to do this: =If(C:C>=6.2,"Excellent",if(C:C=5.8-6.1,"Good",if(C:C=5.1-5.7,"Fair","Poor")

    1. Hello!
      If you use a range in the IF function, then its result will also be a range (array) of values. You must have a lot of free space on the sheet to display this array. If there is not enough space, you will get a #SPILL error.
      Expression C:C = 5.1-5.7 is incorrect. Read about using multiple conditions in an IF function in this article.

  7. I need all 3 of these variables to play nice together in one formula and I've been trying for 3 days to get it to work.
    HELP is greatly appreciated so that my brain can stop hurting.

    Cell A3 will always be either "D, "N" or "U"
    Cell E3 will always be a different value

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

    Thanks in advance

  8. Hi! I am trying to have a cell return a value based on whether other values in the same row are greater than zero. To give more detail, the data is tracking steps in a hiring process, each column is a step in the process and the users will enter the date when that step is completed. I’d like to have an additional column that will say what step in the process that candidate is currently in. In other words, if for candidate A the most recent date entered is under the “interview complete” column, how do I make another column that will return “interview complete” as a value for candidate A?

  9. Lets say
    I have 1.0 -5.0 then I wan between 50 - 0 so the value of 1.0 is 50 then if it 1.1 that's 49 how would I convrt 2.8 into its specific range

  10. Can I convert 1.0 into 50 then 5.0 into 0?

  11. I am getting the error message too mant arguements. Is there a way to have more than 2 options in an IF statement?

    =IF(I3="USD",$B$1*G3,IF(I3="GBP",$D$1*G3),IF(I3="CAD",$F$1*G3),0)

    1. Hello!
      Please try the following formula:

      =IF(I3="USD",$B$1*G3,IF(I3="GBP",$D$1*G3,IF(I3="CAD",$F$1*G3,0)))

      I hope it’ll be helpful.

    2. This equation should work better for you. I took out your unnecessary brackets that had caused the "too many arguments" notifications. (You had your "false" outside of the conditions.)

      =IF(I3="USD",$B$1*G3,IF(I3="GBP",$D$1*G3,IF(I3="CAD",$F$1*G3,0)

      Hope this works!

  12. Hi
    Im using the following formula but it doesnt seem to be working

    =IF(OR(J1080:J20001="AUD","2021","2020","2019","2018","2017","2016"),'Economic Assumptions - Forex'!$M$9*K1080)=(OR(J1080:J20001="ZAR","2021","2020","2019","2018","2017","2016"),'Economic Assumptions - Forex'!$M$16*K1080)

    Please can you tell me what is wrong

    1. Hello!
      I can’t test your formula as it contains unique references to your data. I do not have this data.
      However, the condition

      (J1080:J20001=”AUD”,”2021″,”2020″,”2019″,”2018″,”2017″,”2016″)

      is incorrect. Can only be used

      (J1080:J20001=”AUD”,J1080:J20001=”2021″, J1080:J20001=”2020″, J1080:J20001=”2019″, J1080:J20001=”2018″, J1080:J20001=”2017″,J1080:J20001=”2016″)

      I hope it’ll be helpful.

  13. I have to come up with a function with the criteria...
    D2>5
    G2= "B" or "C" or "D"
    E2= "marketing" or "finance"

    and then if those are true then C2* $Q$23 otherwise 0 is left in the cell.

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

      1. This is the formula I came up with, can you help me with what I have wrong, thanks!

        =IF(AND(D2>5,OR(G2="B",G2="C",G2="D",OR(E2="Marketing",E2="Finance")))),C2*$Q$23,0)

        1. Hello!
          Please use the following formula

          =IF(AND(D2>5,OR(G2="B",G2="C",G2="D",OR(E2="Marketing",E2="Finance"))),C2*$Q$23,0)

  14. Hi I am a bit of an excel novice and I, like many, I don't know what I don't know

    I am currently using this formula =IF(I14<$G$9,0,IF(I1465,$W$9))) to determine value in a cell for the following conditions
    - if age (F14) is less than planned retirement age (SG$9) then pension income is $0
    - if age (F14) is <=65 but greater than retirement age ($G$9) then pension income is $T$9
    - if age (F14) is greater than 65, then pension income is $W$9
    But; the stated pension incomes ($T$9 and $W$9) are indexed to inflation and I cannot figure out how to accommodate the ever increasing values. Any suggestions or help would be really appreciated

    1. Hello!
      If I understand your task correctly, you can manually change the values in $T$9 and $W$9. Also in these cells it is possible to use a reference to a cell with an actual value or a hyperlink.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  15. I have a range of cells that results in a text of "(All)"...when a selection is made, one of the range of cells will say something else, like, "January"....is there a way to have multiple conditions AND result in any text shown other than "(All)"?
    Multiple conditions, and the "else" statement would be whatever that shows up other than "(All)"?

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

  16. HI, I am trying to create a formula that looks across a range.
    Example is:
    I have to select one of three different results. FAIL, COMPLETE or CUSTOMER REFUND on a daily basis.
    At the end of the week, I need to have a formula to say that if it has COMPLETE in the range (which are the days) then the final result should be COMPLETE. If it has anything else, it should be FAIL.
    My issue is, that during the week, you could have a FAIL on Monday but a COMPLETE on Friday, so How do I get the formula to ignore the FAIL in this instance.

      1. Sorry for misunderstanding.
        In the top columns I have the days, in my sheet it would be C3:H3 (Mon-sat)
        In columns A:B I have the customer name and order number.
        So once I enter the order number and go to that particular day, I want to enter FAIL on whatever day the failed delivery was. Then when the problem is resolved I would enter either COMPLETE or CUSTOMER REFUND.
        So at the end of the week against that order in I4,I should be able to see if the order was COMPLETED OR FAIL. So if they had a FAIL and CUSTOMER REFUND that would be COMPLETE, if they had a FAIL and COMPLETE, that would be COMPLETE but if they had a FAIL then that would be COMPLETE.

        I did think maybe if I concatenated the selections, and then did an IF statement, but this is where I am stuck!

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

          =IF(SUM(--(C3:H3="Complete")) > 0,"Complete","Fail")

          After that you can copy this formula down along the column.
          I hope it’ll be helpful.

          1. Thanks!

  17. I a trying to create an if function in excel 2016 with multiple layers:
    if cell a1>50, cell a1*2,
    if cell a1<50, cell a1*1,
    if cell a1*1<20, must result as 20.

    Please help.

  18. I need formula like ( my last year leave balance has 10 and current year leave 30 and this year consumed 03 now the balance will be 27 ignore balance another way need formula that if my last year leave -10 (negative) current Year 30 Balance = 20 in one cell Please do the needful.???any one

    1. Hi,
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

      1. -----Staff Leaves Record----

        Module 1
        Opening Current Year Availed Leave Balance
        *10 30 -2 28
        *if this number is in positive then ignore in formula
        Module 2
        Opening Current Year Availed Leave Balance
        *-5 30 -5 20
        *If this number is in negative then adjusted and less in balance

        now hope everything is clear

        1. Hi,
          Not sure if I understood your problem correctly. Try this formula

          =IF(A1>=0,B1-C1,B1+A1-C1)

          I hope it’ll be helpful.

          1. Thanks!

  19. =IF(Index!D2="EXISTING",(ROUND(IF(H73250000,H73500000,H731000000,112500+(H73-1000000)*30/100,0)))),0)),(ROUND(IF(H73250000,H73500000,H73750000,H731000000,H731250000,H731500000,112500+(H73-1500000)*30/100,0)))))))

  20. Please let me know how to write following

    1 to 30 = Manager
    31 to 45 = Regional Head
    46 to 60 = Head of Sale
    61 to 90 = Credit Operation Team
    91 and above = Recovery Team and Legal
    1 and below = Before Over Due

  21. Hi ,

    Could you please help me to find formula for below Question ?

    if Value of cell B is greater than and equal to 95 , its cell A*3% .
    If Value of cell B is greater than and equal to 85 & less than 95 , its cell A*2.5% .
    If Value of cell B is greater than and equal to 80 & less than 85 , its cell A*2% .
    If Value of cell B is greater than and equal to 70 & less than 85 , its cell A*1% .

    Please help me out .

  22. Sir I recruiting some employees for my office. I recevied many applications and I have prepared a list in excel there is a condition that the applcant shouldn't be over the 40years of age and not less than 18years. I have entered the date of birth in column b and calculated the age in column c now i have the values some thing like 30year 2months and 4days now I need a formala to be entered in column D to find out that if the applicant is overage, within age limit or is he underage....

  23. I am trying to create an IF/THEN from a drop down list.
    C1 would have the drop down LIst: Hamburgers, hot dogs, nachos, pizza

    So the cell with the formula would need to perform: IF C1 is hamburgers, $5. If C1 is hot dogs, $2. if C1 is nachos, $4. if C1 is Pizza, $10.

    Can anyone help give me a framework?

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

      =IF(C1="hamburgers",5,IF(C1="hot dogs",2,IF(C1="nachos",4,IF(C1="pizza",10,""))))

      In Excel 2016 and above, you can use the IFS function

      =IFS(C1="hamburgers",5,C1="hot dogs",C1="nachos",C1="pizza",10)

      If there are a lot of products and conditions, I recommend searching for the price using the VLOOKUP function.
      I hope my advice will help you solve your task.

      1. Thank you! I will try this out

  24. Hi Everyone
    I am trying to write a formula that has few IF conditions eg:

    if A1>=25 (age), then if B1=>£8.91, B1 otherwise £8.91 (it does work when A1 (age ) is = 25, if I change it to any other age it throws an error. This is one part of a formula

    the full formula should calculate:
    if A1>=25 (age), then if B1=>£8.91, B1 but otherwise £8.91, if A1=21, then if B1>8.36, B1 or £8.36

    is it possible to calculate? I am really struggling with this, any help appreciated

    1. Hello!
      If the number 9 is written in cell B1, then no Excel formula can change the value in B1 to 8.91. This is only possible with a VBA macro.

  25. Our clients have 4 scenarios that produce different outcomes. I am trying to produce a formula for those outcomes -
    Columns I & J are the scenario factors, K is the outcome

    I2=Y J2=N K2=N
    I2=Y J2=Y K2=Y
    I2=N J2=Y K2=Y
    I2=N J2=N K2=N

    I have tried the following, using " " for the Y & N variables, also trying it without the " " and the outcome produces #NAME? with both formulas. I'm sure there is something easy I am missing.

    =IF(OR(AND(I2=Y,J2=N)),N,IF(OR(AND(I2=Y,J2=N)),Y,IF(OR(AND(I2=N,J2=Y)),Y,IF(OR(AND(I2=N,J2=N)),N))))

    1. Hello!
      One simple formula describes your 4 scenarios

      =IF(J2="N","N","Y")

      If you have other questions, don't hesitate to ask.

  26. I'm trying to get two texts with the same number value...
    home=0.5
    office=0.5
    any other entry=0

    any thoughts on how to do that?

    =IF(B5="home"or"office", 0.5,0) didn't work

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

      =IF(OR(B5="home",B5="office"), 0.5,0)

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

    2. Hello!
      Please try the following formula:

      =IF(OR(B5="home",B5="office"),0.5,0)

      I hope it’ll be helpful.

  27. Hi! Can anyone please help me in the following situation:
    Delivery charges are based on two types of services i.e Overnight & Economy
    Barcode of both are like OVN123456 & ECO123456
    If weight is <=1 kg delivery charges for OVN are 100 & For ECO are 70
    If weight is <=3 kg delivery charges for OVN are 175 & For ECO are 150
    I want that Delivery charges cell check if B3 has code OVN and E3 is having weight <=1 then show 100 OR if B3 has code ECO and weight in E3<=1Kg it shows 70.
    I will be very thankful.

  28. I need to return 100% in a cell for cells B2/A2. If B2 = 0, and A2= 0, I want the result to be 100% as a goal attainment returning 100% in C2.

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

      =IF(AND(B2=0,A2=0),100%,B2/A2)

      I hope it’ll be helpful.

  29. A1=B1 B1=48 C1=62
    A2= (very long formula) B2=A5 C2=(A5 in case A1=C1)
    A3= (very long formula)
    A4= (very long formula)
    A5=A1*sum (A2:A4)

    How can I put a formula for C2 since it is so hard to repeat all the formulas in A2 to A4? Please help me with this.

  30. Hi,

    Can someone please help me with this formula.

    =if(F3="emergency loan"=1*D3,if(F3="short loan"=2*D3,if(F3="normal loan"=3*D3,if(F3="development loan"=5*D3))))

    Essentially, if cell f3 reads a certain type of loan, i'm supposed to multiply it with cell d3 and the figure next to it. I'm stuck and frustrated.

    Any help would be appreciated. Thanks in advance

  31. Hi,

    I'm horrible with formulas and I need help. I need to put a dollar amount in a cell based on data on a different cell. This is what I am trying to do;
    If row D = MB,CB.DB I want row I to show $2000, if row D = MB+,CB+,DB+ I want row I to show $3500 and so on. What formula would I need to use? Thanks in advance!

  32. Can someone help me to create a formula for this, please

    Payment Type - FB
    Amount - 100

    I want to get this if the payment type is FB it should be equal to the amount
    if not FB then 0

  33. I can't seem to figure out how to fix this formula. Any thoughts?

    =IF(AND(F6=”N’,G6=”N”,E6/2, IF(AND(F6=”Y”,G6=”N”,(E6/2)*0.75, IF(AND(F6=”N”,G6=”Y”,E6, IF(AND(F6=”Y”,G6=”Y”,E6*0.875,))))

    If F6="N" and G6="N" then E6/2
    If F6="Y" and G6="N" then (E6/2)*0.75
    If F6="N" and G6="Y" then E6
    If F6="Y" and G6="Y" then E6*0.875

    Example:
    e6=3780 f6=n g6=n then answer should be 1890
    e6=3780 f6=y g6=n then answer should be 1417.5
    e6=3780 f6=n g6=y then answer should be 3780
    e6=3780 f6=y g6=y then answer should be 3307.5

  34. HELLO ! Can someone help me PLEASE.
    i have 6 Names in 3 DIFFERENT CELL: in cell A1"Emo/nonEmo", in cell B1"Bio/nonBio", in cell C3"Sio/nonSio". I want in cell A4 to display automaticaly miscellaneous combination between 3.

    For example =IF(a1=bio,b1=nonBio,c1=nonSio) in A4 display"Anorganic". Thk

  35. Hi All,

    can you help me for below.

    i have 3 condition,

    region,

    manager,

    more then 50 days,

    so i have to find

    region : Delhi.

    manager : Alex.
    then less then 50 days how many business done by alex.
    after (region=delhi)(manager=alex)(days=<50)(total business done).

    i tried if and sumif but i didn't get correct answer

  36. Hello,
    I want to copy a formula to downward cells, the first value to be changed cell to cell but the second should copy with the same value to all down cells:
    Formula: B77-C4, to the down cell the formula should change to B78-B4, B79-B4 etc.
    I wish someone can help
    Regards.

  37. I need to compare grades in two cells. like if D6=A+ and D7=A, than "A+". means show higher grade. grades are A+, A, B+, B, C, D and F. to get final grade, I need to compare two grades. if both grades are same, final grade will be same. if grades are different with one step, final grade will be higher one. if grades are different, with two steps, final grade will be in between. if grades are different and apart from each other, final grade will be in between higher one. examples given below:

    example 1 (same)
    A+ and A+ = A+
    A and A = A
    C and C = C

    example 2 ( different/higher)
    A+ and A = A+
    A and B+ = A
    D and C = C

    example 3 (2 step different/ in between)
    A+ and B+ = A
    A and B = B+
    D and B = C

    example 4 ( apart/ in between higher)
    A+ and B = A
    A and C = B+
    D and B+ = B

    Please guide formula

  38. im trying to see if there is a way to detect a zero in cells b2 to b9 if there is i want it to execute one function or the other how can i do this (started self teaching best to over explain thank you)

  39. HOW TO SOLVE THE PROBLEM ?
    20% ANS = 20
    =TEXT(P2,".00") This correct or ......

  40. Hi!

    I am trying to check for a valid response for a variable (P2), the code/conditions I have are meant for EpiInfo but I am having so much trouble getting the formula translated and working in Excel. Any help is greatly appreciated!

    The conditions are:

    P1=1 AND P2>0 AND P2<8
    OR
    P1=2 AND (P2=0 OR P2="." OR P2=99)

    The returned value is '1' and all ELSE is '2'

  41. Hi can u tell me how to use if and or function to get depreciation value with sln, dB and Syd method for the same data in same cell.

  42. if-or(L59>L60,L59>L61,L59>L62),''yes'',''no'' which button to click on the keyboard in order to get to yes or no. i am learning on line.please help

  43. Hi,
    I am trying to create a formula with 3 different conditions.
    If J3<5 and K3<5 it should calculate K3-J3+1
    If J34 it should calculate 4-J3+1
    If neither is true the result is 0

    I have tried different variations o the following but nothing works:
    @IF(OR(AND(J3<5,K3<5),k3-j3+1,AND(J34),=4-j3+1,0))

    Any advice would be greatly appreciated. Thank you

    1. ERROR in 2nd "If" condition I posted.
      Should read: If J34, it should calculate 4-J3+1
      ERROR in formula I posted.
      Should read: @IF(OR(AND(J3<5,K3<5),=k3-j3+1,AND(J34),=4-j3+1,"0"))

      I copied and pasted so I am not sure what happened. So sorry for any confusion!

      1. OK so strange - after hitting Send the text changes in my post!
        Where it says J34 it should say IF J3 is less than 5 and K3>4.
        Hope the 3rd time is a charm! Thanks again.

        1. NEVER MIND - I figured out by studying at least 1,000 questions already answered here over the past few days.

          Correct formula: =IF(AND(J3<=4,K3<=4),K3-J3+1,IF(AND(J34),4-J3+1,0))

          Thanks to everyone who answers strangers very complex problems on this site. AMAZING!

          1. And once again my formula changed upon posting.

            If anyone needs the correct formula - respond to this and I will try to write it again with some other method

  44. HI
    I NEED A FORMULAR THAT WILL HELP CHECK IF A STUDENT HAVE PASS ALL REQUIRED COURSE.
    THANK YOU

  45. Cell G16 shows total.
    I want G17 to display "Level One" if cell G16 falls between B4 on another sheet (number listed in cell B4 is 10-13). Need this nested with C4 (14-16), d4 (17-19), e4 (20-22), f4 (23-27), g4 (28-38)

  46. i have to design a excel sheet for calculating printing cost, which involves multiple conditions
    categories :paper size, quantity, paper quality (100 GSM), Lamination,etc., i have to calculate paper cost, plate cost, labour cost according to the inputs.

    under paper size category there are diff sizes like
    1.a4
    2.a3
    3.crown
    4.double crown
    5.dimmy
    etc

    under paper quality categery it also varies according to gsm like 100gsm,170gsm,300gsm etc

    by looking up and combining paper size and gsm i have to calculate paper cost and remaining calculations labour printing cost, plate charges are also have to be calculated using combined inputs.

    please suggest me formulas with example to use / provide me a model excel.

    Thanksalot,

  47. does any know
    I want to write an argument statement that if a number in a certain column >-999 or<999 then send me an email.
    does anyone know how I can write this?

  48. Hello,

    I'm working on a project with 3 criteria and my formula seems to super long.

    =IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(K2=3,L2=8,M2>25),2,IF(AND(K2=6,L2=8,M2>25),4,IF(AND(K2=6,L2=8,M2>25),4,0)))))))))))))

    1. Column K is fixed with 3 numbers = 3, 6 and 12
      Column L could be between from 1-12
      Column M could be between from 1-31

      This is my formula so far. It's Super long. Maybe you can help me.

      =IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(B2="KSA",K2=3,L2>2,L26,L28,M28,M20,M212,M22,L25,L28,M28,M20,M22,L22,L22,L22,L22,L22,L22,L225),2,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="KSA",K2=3,L2=8,M2>25),2,IF(AND(B2="KSA",K2=6,L2=8,M2>25),4,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="Jordan",K2=6,L2=8,M2>11,M225),4,IF(AND(B2="Jordan",K2=4,L2=8,M2>11),2,0))))))))))))))))))))))))))))))))))))

  49. Hi all looking for help with and IF AND and Or function

    im trying to get it to either times by .25 if both parameters are met or if only one then times by .5

    And if none of the parameters are met then leave as is.

    =IF(AND(J45="Tigress",M45>100000),O45*0.25,O45)*(OR(IF(J45="Tigress",O45*0.5,O45)))

    Any help would be much appreciated

    1. =IF(AND(J45="Tigress",M45>100000),O45*0.25,IF(OR(J45="Tigress",M45>100000),O45*0.50,0))

      This should work

      Explanation on the below

      If J45=Tigress and M45 less than 100000, value in cell O45 will multiply to 0.25
      If J45=Tigress or M45 is less than 100000, value in cell O45 will multiply to 0.50
      If neither of the equation, the answer will be 0 - or you can change it to "Not found" instead of 0

  50. Hello,

    I am trying to use the formula below, but it is not correctly categorizing my data and I cannot figure out why.
    =IF((AND(A2>=TIME(7,0,0),A2<=TIME(18,0,0))),"Diurnal","Nocturnal")

    While some categorizations are correct, others are not. For example one species was caught at 15:36 Army time, but is categorized as nocturnal when it should be diurnal. Any thoughts?

    Also, as a fun bonus question: is there any way I could add a third option for species that come out at dawn out at dawn or dusk?

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