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 13. Total comments: 567

  1. hi, i've a excel sheet with multiple column. what formula do i use to calculate the trip time if the criteria is A and B by subtracting D to C

    A (Compy) B(veh) C (in) D (out) E (Trip Time)
    XYZ XD 3787M 11:26:23 11:26:29 ?
    XYZ XD 3658C 11:33:12 11:33:21
    XYZ XD1900R 11:36:30 11:36:43
    XYZ XD 6522R 12:17:29 12:17:38
    XYZ XD 3787M 12:19:30 12:19:40
    XYZ XD 3658C 12:21:33 12:21:40
    XYZ XD1900R 12:23:04 12:23:14
    XYZ XD 6522R 12:24:37 12:24:46
    XYZ XD 3787M 12:26:22 12:26:30
    XYZ XD1900R 12:28:30 12:28:37
    XYZ XD 6522R 12:30:14 12:30:25

  2. Hi,

    I am trying to work on a possibility where if condition on two cells are correct then the formulae should fill the relevant code dependent on it in a new cell.

    for example if a15=value(a2:a33) & b15=value(b2:b33) then the C15 should be value(d2:d33); the values in d is unique for all cells where the first two match.

  3. I'm doing a mark sheet , now one of the teacher wants to have absent appear in marks. If they put '0' marks in the box and I want it to appear as'ABSENT'. But I have put a formula in it to calculate so how do I join together saying that If marks is 0 then grade is absent.

  4. Hi i need a formular that will look up name of employee name and return text "YES" or "NO" if a row contains a negative value. kindly assist. Thanks

  5. Hello I am working tracker that tracks #of observations. In order to be 100% compliant there needs to be 3 observations done but one of those 3 observations need to be a 1 of 2 types of observations. So I need the tracker to show both the total and 100% goal met if one of the two specific observations were completed.

  6. In Col I Write aIn Col I Write a formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    plz reply urgent

  7. In Col I Write a formula to give In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    plz reply urgent

    1. Use V lookup function

  8. In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

  9. hi trying formula A=B,C=D,E=F,G=H THEN 10000 PLZ HELP

    1. hi hassan,

      Just try like this if you need through number format keep numbers in another sheet like this(1,2,3,....10000) give formula, and past below sells it will take automatically up N numbers

      =IF(1=2," TRUE "," FALS ")

  10. Dear Ms. Seventha,
    I am writing below mentioned formula i need a specific formula for counting for odd numbers i.e. 3,5,7,9 as well even figure. can you please help how to write this both condition in 1 formula for even and odd number for CEll No. L105)
    IF(C107="lock bottom",IF(AND(L105=3),(I105*L105)+(G105*L105)+(F105*2)+(H105*2),IF(AND(L105=5),(I105*L105)+(G105*L105)+(F105*3)+(H105*3),IF(AND(L105=7),(I105*L105)+(G105*L105)+(F105*4)+(H105*4),IF(AND(L105=9),(I105*L105)+(G105*L105)+(F105*5)+(H105*5

    Regards
    Mukesh

  11. I don't really understand this!!???
    Please help me out first. :-)

  12. Hiii Mem I Want Formula that is (I want 1 to 10 Or 10 to 20 rows values in column A by sequentially in column B From 1 to 10, Like wise , if I specify in A2 how many rows to take exp 10 row and also i will give the rage to take 1 to 10, or 10 to 20, like wise the same values should come in column c

    Ex A1 to A10 in B1 to B10 is it possible to execute the same condition should work with multiple conditions please specify the formula

    With Request

    K.Gopala Krishna

  13. hi,

    i am trying to write a formula where if cell b =x or y or z give me a blank any help is much appreciated

  14. Hi what function can I use to get this result?

    If A6 is less than 5 add 1, if not return 5?

  15. Hi,

    There are two answer from two different cells in a row but i want either of one answer.

    Please reply me quickly

  16. HI Svetlana Mame
    I am using an sheet for router of security staff. I want that if an security guard works 7 days "day duty" then the next seven days the auto change the shift to "night duty".
    (I use "P" for day duty and "B" for night duty)
    Need Ur Help .......

  17. Thanks so muck my problem is solved

  18. If Cell M7 in below table is "Buy" then Column P has to return value of (Column O - Column N) and if Cell M7 has "Buy" then Column P has to return value of (Column N - Column O). Data Table for reference is given below
    Column →L M N O P
    Row ↓ Deal Type Deal price Expected price Profit
    7 x Sell 184 169.34 -14.66

    8 y 253 277.44 24.44

    Formula Input in P7 & P8 is given below:
    For P7 : =IF(AND($M7="Sell",$M7="Buy"),SUM($N7-$O7),SUM($O7-$N7))
    For P8 : =IF(AND($M8="Sell",$M8="Buy"),SUM($N8-$O8),SUM($O8-$N8))

    My Problem is as below:
    I have input above formula in cell P7 and P8. Whatever I input in Column M 7 & 8 or leave it blank , it calculates the value by assuming that the condition is "Buy".

    Cannot understand why this is happening and how to correct this.Am I missing something. Please help.

  19. I need help to write an if statement. i have dates in column E, I need the spreadsheet to add 90 days if answer in column F is yes, and add 365 days if answer in F is NO.

    1. Hi Jessica,

      Here's the formula:

      =IF(F1="yes", E1+90, IF(F1="no", E1+365, ""))

  20. I WANT TO CALCULATE HOUSE RENT BY FORMULA
    GIVEN, BASIC SALARY:A2= 100000
    NOW
    A) HOUSE RENT(B2):( 50% OF BASIC SALARY OR TK 25,000/-WHICH IS HIGHER BETWEEN THEM)

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

  22. 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,"")

  23. 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.

  24. 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

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

    Please Help with atleast two example.

  26. 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%))))))))))))))))))

  27. 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

  28. 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.

  29. 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, "")))

  30. 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.

  31. 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)

  32. 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

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

  34. 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.

  35. =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.

  36. 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.

  37. 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

  38. 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.

  39. 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

  40. 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

  41. 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.

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

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

    suggest some formula

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

  45. 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

  46. 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, ""))

  47. 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

  48. 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

  49. 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"))

  50. 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?

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