Comments on: Using logical functions in Excel: AND, OR, XOR and NOT

Last week we tapped into the insight of Excel logical operators that are used to compare data in different cells. Today, you will see how to extend the use of logical operators and construct more elaborate logical tests to perform more complex calculations and more powerful data analysis. Excel logical functions such as AND, OR, XOR and NOT will help you in doing this. Continue reading →

Comments page 14. Total comments: 567

  1. How can i combine these formulas MAX(0,180-D5) and MIN(40,180-D5)

  2. For scenario 1, I have: =IF(AND(AV2="Y",AD235),AI2," ")
    Please help me determine the formula for scenario 3, for AD2 greater than 25 but less than or equal to 35.
    Thanks - and not sure why this isn't posting correctly

    1. Our blog engine often mangles "<" and ">" characters in formulas, sorry for this. You can specify all 3 conditions in the AND statement, like this:

      =IF(AND(AV2="Y", AD2>25, AD2<=35), AI2,"")

  3. I have to make 3 calculations based on two factors:
    1. If a variable 1 (AV) is “Y” and the second variable (AD) is less than or equal to X, then input the third variable (AI) or leave blank.
    2. If a variable 1 (AV) is “Y” and the second variable is greater than X, then input the third variable (AI) or leave blank.
    3. If a variable 1 (AV) is “Y” and the second variable is greater than X, then input the third variable (AI) or leave blank. I need to calculate for AD2 between 25 and 35 (25<AD2<=35)

    For scenario 1, I have: =IF(AND(AV2="Y",AD235),AI2," ")
    Please help me determine the formula for scenario 3.

    Thanks

    1. Hi!

      If you want to handle all 3 scenarios with a single formula, then you have to use nested IFs. To be able to suggest an exact formula, I need to know the actual values for all 3 scenarios because this determines the order of nested IF's.

  4. I have to make 3 calculations based on two factors:
    1. If a variable 1 (AV) is “Y” and the second variable (AD) is less than or equal to X, then input the third variable (AI) or leave blank.
    2. If a variable 1 (AV) is “Y” and the second variable is greater than X, then input the third variable (AI) or leave blank.
    3. If a variable 1 (AV) is “Y” and the second variable is greater than X, then input the third variable (AI) or leave blank. I need to calculate for AD2 between 25 and 35 (25<AD2<=35)

    For scenario 1, I have: =IF(AND(AV2="Y",AD235),AI2," ")
    Please help me determine the formula for scenario 3.

    Thanks

  5. Hi Amanda,
    I want to ask you,How we use AND or OR logical function with IF condition?

    Please Help with atleast two example.

  6. Hi Ma'am,
    I am stuck in if formula as I want to multiple values in True & False both category but not able my formula is

    =IF(BD22="Old",(IF(AND(I22=1,AP22="GBT"),33000,IF(AND(I22=2,AP22="GBT"),31000,IF(AND(I22=3,AP22="GBT"),29000,IF(AND(I22>=4,AP22="GBT"),27000,IF(AND(I22=1,AP22="RTT"),22000,IF(AND(I22=2,AP22="RTT"),21000,IF(AND(I22=3,AP22="RTT"),20000,IF(AND(I22>=4,AP22="RTT"),19000

    False Value is

    ,IF(AND(I22=1,AP22="GBT"),33000-33000*7.5%,IF(AND(I22=2,AP22="GBT"),31000-31000*7.5%,IF(AND(I22=3,AP22="GBT"),29000-29000*7.5%,IF(AND(I22>=4,AP22="GBT"),27000-27000*7.5%,IF(AND(I22=1,AP22="RTT"),22000-20000*7.5%,IF(AND(I22=2,AP22="RTT"),21000-21000*7.5%,IF(AND(I22=3,AP22="RTT"),20000-20000*7.5%,IF(AND(I22>=4,AP22="RTT"),19000-19000*7.5%))))))))))))))))))

  7. In Excel under the head "LOCATION" there are 3 cities namely 'X', 'Y' and 'Z'

    The % age of HRA under the head "HOUSE RENT ALLOWANCE" is assigned to the cities are 'X'=30%, 'Y'=20% and 'Z'=10% respectively.

    I wish to put a condition in the cell under the head "HOUSE RENT ALLOWANCE" that if the location is 'X', it should return the value 30%, If 'Y', it should return the value 20% and if 'Z' it should return 10%

    Please help me to insert appropriate function in the cell, to result the desired value

  8. A=1,2,3,4,5,6,7,8,9 so take value 0
    B=0,2,3,4,5,6,7,8,9 so take value 1
    so which formula use for this help me on this formula.

  9. Hi,

    Here's one for you:
    I've got a column containing the following values: "1", "2", "3", and "-".
    I want to nested If/OR formula to return the follow.
    If Column A contains a "1", return a 1
    If Column A contains a "2", return a 0
    If Column A contains a "-" OR "3", return a 'FALSE'

    What's the best formula?

    1. Hi Liz,

      Try this one:

      =IF(A1=1, 1, IF(A1=2, 0, IF(OR(A1=3, A1="-"), FALSE, "")))

  10. I want to use If formula. Generally we do =if(A2>4, "YES", "No") and i want to use =If(A2>4, "B2", "") but is not working. Any idea for do this formula.

    1. I done it from help by your above comments. Thank You.

  11. PLEASE HOW DO I CREATE THIS FORMULAR. FOR EXAMPLE, IF A3 > B2, SUBTRACT B2 FROM A3 AND IF IT IS REVERSE AS IN IF B2 >A3 , SUBTRACT A3 FROM B2.

    1. Hi Jolly,

      Here you go:

      =IF(A3>B2, A3-B2, B2-A3)

  12. I have a worksheet that I would like to create a formula form based on 2 different conditions on 2 separate cells. Could someone help me?

    I need to write the following:

    IF b10=Rick Otero (names comes from a table list), then I want the return value to be $0.00, otherwise, if not Rick Otero AND b13>499999, then value is $350.00, otherwise $250.00

    Any help would be appreciate it. I am able to write both formulas separately, but unable to tie together. Thanks

  13. =IF(AND(E12>0,"Closed","Open"), OR (B12>0,"Open".""))

  14. Struggling with a formula:

    =IF(AND(E12>0,"Closed","Open"), OR (B12>0,"Open".""))

    What's wrong with my formula

    I'm trying to say if E12 has no date(there is a reference formula in it so I used E12>0) then it equals blank being "", but if it has a date in it, it is open.

    Then if B12 has no date it is open, but if B12 has a date, it is closed. B12 also has a reference formula in it.

  15. =IF(AND(E12>0,"Closed","Open"), OR (B12>0,"Open".""))

    What's wrong with my formula

    I'm trying to say if E12 has no date(there is a reference formula in it so I used E12>0) then it equals blank being "", but if it has a date in it, it is open.

    Then if B12 has no date it is open, but if B12 has a date, it is closed. B12 also has a reference formula in it.

    1. Are these two different formulas you need or one?

      =if(e12=0," ","Open")
      =if(b12=0,"Open","Closed")

      Your 2nd formula has a period instead of a comma.

  16. I'm tracking recommendations that have been given to my employees on a monthly basis. The data is the employee name and what type of recommendation they received (from colleague, stakeholder, or candidate). I've created pivot tables to track the number of recommendations for each employee and by which type of recommendation. I'm trying to create a chart that shows which employees have received recommendations from all 3 types and which have received them from at least two different types.

    Thank you so much for any help/advice you can offer.

  17. Hi Svetlana,

    Hope you're doing well. I have an error in "IF(AND)" function. Where i am putting three condition to get the value but i got an NA reason being the first condition have the same number multiple times with the different amount(second condition). Example below:

    Table A Table B
    Number Amount Text Number Amount Value (Text)
    1026 550 Sun 1026 123 NA
    1026 123 Moon 1026 550 NA

    Hope i made my self clear. Please email me the solution.

    Thanks
    Sushanta Lenka

  18. Hi there,

    Just wondering whether you could help me on below?
    I have column D (ON RFQ) and column v (Order Number),
    my requirement is:-
    1)if D2 is blank and V2 contains order number, then return value as "PO"
    2)if D2 is * and V2 contains order number, then return value as "PO"
    3)if D2 is * and V2 is blank, then return value as "ON RFQ"
    4)if D2 is blank and V2 is also blank, then return vlaue as "OPEN REQ"

    Any help on above will be greatly appreciated.

  19. I would like to create formula in Excel i.e.
    if i typed "B" in cell 1 then value should be add in bottom 0.050 and in the same cell if typed V then value should add in bottom 0.035

  20. dear sir i want to get a formula in which i want to get the due date formula from the bill

    like:
    bill date is 10.06.2016
    due date is after 40 day from the bill date
    so tell me formula like that

    as due date-40 DAY = DUE DATE LIKE THAT 20.07.2016

  21. I have a problem regarding a calculation of commission total in a month with a condition that if actual sales is greater than the weekly target sales in all four weeks in a month plus monthly sales is also greater than the target monthly sales, then commission will be paid as a certain percent of the monthly sales, if in any week actual sale is greater than target weekly sale but not in all 4 weeks then commission will be paid as a certain percent of weekly sales, it will be not paid as a certain percent of monthly sales, even though monthly sale is greater than target monthly sale. Otherwise,if actual sale is not greater than target sale in any week, no commission will be paid at the end of the month.

  22. Hello
    I have a problem in a scenario of 1q1

  23. if cell value in minus than "Extra" and if 0 than "OK" and greater than 0 than Short

    suggest some formula

  24. Excel A1 cell 6Doz+ 6pics so how formula use and create answer 78 pls help

  25. Hi
    I have a cell(W28) which has only 2 drop down "Yes" and "No". But this affects my formula in cell (W42).

    If (W28="Yes") - I have to use formula as (A+B^0.5)/C
    If (W28 ="No")- I have to use formula as (A+(B/2)^0.25)/C

    I am not able to use the the formula based on my selection in Cell (W28).It gives only one value based on the formula i put
    Here is my formula -
    =IF(W38="Yes",(((W39*(W31/2)^0.5)/W40)^1.33),(((W39*W31^0.5)/W40)^1.33))

    Can anyone please help

  26. Hi, I'm trying to find a way of achieving the following,
    if A1 has a text entry of "W" then add 1 to cell B5, if A2 has a text entry of "L" then add 0 to C5. These have to be interchangeable so if A1 has a "L" entry enter 0 to B5, and if A2 is "W" add 1 to C5

    1. Hello Graham,

      You can use the following formulas.

      For B5:=IF(A1="W", 1, IF(A1="L", 0, ""))

      For C5:=IF(A2="W", 1, IF(A2="L", 0, ""))

  27. hi
    I Have 1 Sheet How i Can See On Sheet All negative value In One Sheet

    Example
    Name Total Amount
    Ram 60
    Sita -50
    Hari 150
    Gopal -140
    Like That
    i Need all Navigate Amount

  28. Hello, i am trying to create a formula similar to this but the result i am getting is "#NAME?.. Can you please help me out..

    IF(C2=ic/Freelancer, "5", IF(C2=Temp/Contractor, "1"))

    Thank you

  29. I have 3 columns
    A: has member ID
    B: Date
    C: Trainer ID

    I want to highlight members who saw different trainers on the same day..
    Will this work?
    =IF(XOR(AND((A3=A2),(B3=B2)),(C3=C2)),0,1)
    Appreciate your help

    1. Hi Asiya,

      Try the following formula:

      =IF(COUNTIFS(A:A,A1,B:B,B1,C:C,C1)>1, IF(COUNTIFS(A:A,A1,B:B,B1) - COUNTIFS(A:A,A1,B:B,B1,C:C,C1) = 0,"not different", "different"),IF(COUNTIFS(A:A,A1,B:B,B1)>1,"different","not different"))

  30. I have a cell (A1) that can contain the values of 6, 12 or 18.
    If A1 is 6, I want cell B2 to show 0; if A1 is 12, I want cell B2 to show 12; if A1 is 18, I want cell B2 to show 18.

    I've tried various combinations using IF and OR but can't arrive at the desired conclusion. Can you help?

  31. Hi,

    working on an excel with a lot of tabs.
    I need a sum, that checks different values, and sums them if all of them are true. Only the last part of the formula should check, if either one of the two conditions are true.
    Currently I am working with Sumifs, that works pretty good, except for the last part, where it should only sum, if one of the two values is true.
    This is the formula:

    =SUMIFS(Export!$D:$D;Export!$G:$G;B$17;Export!$Z:$Z;"2016-11-*";Export!$H:$H;"Forecast*";Export!$K:$K;"Open";Export!$AH:$AH;"*Medium*";Export!$AI:$AI;"*Medium*")

    So basically it should sum the values, if "Medium" is mentioned in the column AH AND/OR AI .

    Thank you very much for your help.

    1. Hi Jean-Luc,

      Try the following formula:

      = SUMIFS(Export!$D:$D;Export!$G:$G;B$17;Export!$Z:$Z;"2016-11-*";Export!$H:$H;"Forecast*";Export!$K:$K;"Open";Export!$AH:$AH;"*Medium*") + SUMIFS(Export!$D:$D;Export!$G:$G;B$17;Export!$Z:$Z;"2016-11-*";Export!$H:$H;"Forecast*";Export!$K:$K;"Open";Export!$AI:$AI;"*Medium*") - SUMIFS(Export!$D:$D;Export!$G:$G;B$17;Export!$Z:$Z;"2016-11-*";Export!$H:$H;"Forecast*";Export!$K:$K;"Open";Export!$AH:$AH;"*Medium*";Export!$AI:$AI;"*Medium*")

  32. IF SALARY >= 5565.61 THEN SALARY X 12.5%
    IF SALARY >= 3566.05 OR SALARY =1783.84 OR SALARY <=3566.04 THEN SALARY X 17.5%
    IF SALARAY <=1783.83 THEN SALARY X 20%

    How i write this in excel? need help

    1. IF SALARY >= 5565.61 THEN SALARY X 12.5%
      IF SALARY >= 3566.05 OR SALARY =1783.84 OR SALARY <=3566.04 THEN SALARY X 17.5%
      IF SALARAY <=1783.83 THEN SALARY X 20%

  33. Hi Svetlana,

    I need help to solve the following problem.

    If Line A2 Have a Word "TEXT" & B2 is Greater then B1 Then A2 otherwise Goto Next Line Number A3, if A3 Fines then stop other wise Go to A4,A5,A6,A7 So on ... on ... on... till A500 until Criteria Match with above said statement

    I look forward to hearing from you soon. Thanks in advance.

    AdiL

  34. Hi Svetlana,

    Need your help in calculating the following problem

    Volume Product Type
    200 A
    300 B
    200 C
    200 A
    100 B

    All the product type columns are selected via drop down list using data validation option in excel. My problem is that i need to sum up each product type separately.So can you help me out in this..

  35. Hi Svetlana,

    I need help to solve the following problem.

    Cells
    A B C D E
    1 Yes Yes Yes Yes Yes
    2 Yes Yes Yes No No
    3 No No No No No
    4 Yes Yes Yes

    Svetlana,
    If all cells in Row 1 = Yes then response is Yes in cell E1
    If any cell in Row 2 = No then response is No in cell E2
    If all cells in Row 3 = No then the response is No in cell E3
    If any cell in Row 4 has a blank then response in E4 is blank, empty.

    Hope you can help with a formula.

    1. Hi Henry,

      Try the following formula:

      =IF(COUNTIF($A1:$E1, "")>0, "blank", IF(COUNTIF($A1:$E1, "yes")=5, "yes", "no"))

  36. Hello, What is this? i tried to post something, but after send i got different how i sould i delete this post :(,

    Guys if you have any excel issue please try to use Mr. excel. Perfect Site to learn excel

  37. "=IF(AND(A2="White",B21),15,IF(AND(A2="Not white",B21),12,0))))"

    1. =IF(AND(A2="White",B21),15,IF(AND(A2="Not white",B21),12,0))))

  38. =IF(AND(A2="White",B21),15,IF(AND(A2="Not white",B21),12,0))))

  39. Hello Svetlana,

    *Something went wrong in my previous question. Here's the correct question.
    I have been trying to crack this for quite a while now. Your help will be much appreciated.

    I am trying to achieve the following rate/pc:

    If color is white and pcs is less than or equal to 1, rate/pc = 10
    If color is white and pcs is greater than 1, rate/pc = 5
    If color is not white and pcs is less than or equal to 1, rate/pc = 15
    If color is not white and pcs is greater than 1, rate/pc = 12

    I have been able to get the first 3 conditions correct using if(and) and if(not) however I can't get the 4th condition to work. Is there a formula for 2 not conditions?

    I look forward to hearing from you soon. Thanks in advance.

    1. =IF(AND(A2="White",B21),15,IF(AND(A2="Not white",B21),12,0))))

  40. Hello Svetlana,

    I have been trying to crack this for quite a while now. Your help will be much appreciated.

    I am trying to achieve the following rate/pc:

    If color is white and pcs is =1, rate/pc = 5
    If color is not white and pcs is =1, rate/pc = 12

    I have been able to get the first 3 conditions correct using if(and) and if(not) however I can't get the 4th condition to work. Is there a formula for 2 not conditions?

    I look forward to hearing from you soon. Thanks in advance.

  41. what formula can i use for example if cell a1:g1 is 60..i want to sum a1:g1 in one cell to be 40 and excess (20) in another cell?

  42. Sir
    How can import a particular Colum from PDF to Excel file how ever i have many of PDF files and I want Import a particular Colum (which contains some value) from PDF to Excel Please Help for this formulla or Micro Please and Please

  43. hello, i am stucked while preparing a XL for my investments. Every investment has end date. so I want to put formula in such way that when it comes to the end date status should show "mature" or else show "active".
    Please help.

  44. Help in Excel formula that is If sheet 1 cell C2 are equal or greater than 21 then copy data sheet 1 cell A2 to C2 and paste in Sheet 2 A2 to C2 if not equal 21 then do not copy data

    1. Hello Nisar,

      Enter the following formula in Sheet2 A2, and then drag it rightwards up to cell C2:

      =IF(Sheet1!$C$2>=21, Sheet1!A2, "")

      1. i AM HIGHLY THANK FULL FORMULLA WORKS

  45. A1 B1 C1 D1 E1 F1 D1
    _________________________________________________
    1450 1440 1073 301 66 73 D2

    HOW I USE FUNCTION IN G2 CELL IN ABOUT CONSTANT B1 WHEN F1 IS GREATER THAN EQUAL TO E1
    OTHER WISE MINUS E1 FROM B1 WHEN E1 IS GREATER THAN F1

    1. Hello LAXMIDHAR,

      If my understanding of your task is correct, you can use the following formula:
      =IF(F1>=E1, B1, B1-E1)

  46. hi, can anyone help me?
    I don't to write like this
    =IF(A1=10,F1=0,IF(A1=12,F1=0,IF(A1=13,F1=0,IF(A1=16,F1=0,IF(A1=18,F1=0,IF(A1=90,F1=0,IF(A1=91,F1=0,IF(A1=92,F1=0,IF(A1=93,F1=0,10000)))))))))
    I want a shorter

    1. Hi!

      You can use the OR function instead of nested If's:

      =IF(OR(A1=10, A1=12, A1=13, A1=16, A1=18, A1=90, A1=91, A1=92, A1=93), 0, 10000)

  47. I want copy particular cell formula in google sheet and I want to paste special to other 3 column which macro program I have to use..

  48. Hello! Good evening.
    Pls guide me solve the problem.
    1- If Table A1 value is 1, B1 is 54,
    2- In continuously If A2 value is 0, b2 value is 0.
    then whats the formula apply to get the total no. of receipts in both tables C1,C2. Pls give the one formula for both tables.

    Recpt. No.
    From To Total
    1 54 ?
    0 0 ?

    1. Hello, Prem,

      For us to be able to assist you better, please give us the result you need in C1 and C2.

  49. Hi,

    I need a formula whereby, when the value ranges from -0.03 to 0.03, it should appear as True and for other values it should appear as False.

  50. i have a question..there are 4 subjects namely s1,s2,s3,s4 each 100 marks in a single group .a student should get aggregate morethan 200 to pass group and he must get 40 or above in each subject.if he get morethen 200 and failed in one subject than he is nt qualified for group........solve me showing pass/fail in each subject along with group

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