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

  1. Hi Sevetlana
    I want to code this formal I.e

    =if(c4*c8<c5"c4*c8","c5")
    how will I code this that the answer itself will show in the quotation box not the cell.

  2. Hi I've a requirement. If in a column A and for Row 1 and Row 10, if values are "paid", then the status of "C1" should get updated as "DONE".

    Please help in this. Your inputs are highly appreciated.

    Thanks,

    SrinivaS

  3. I want to know the difference between numbers in two different cell, if it is lower than the previous cell it should retain the current value, if it is more than the previous cell it should show the difference.
    For eg: If A67=14 and A60=2, the difference or change should show as 2 and not as -12 in A 61

    If A67=14 and A60=16, the difference or change should show as 2 in A61

    Please help.

  4. Please help!!I am trying to do a IF THEN formula over multiple columns where the column is marked with an "x" or is blank. So IF A1="x", then value is E1*G1, or if B1 ="x", then the value is E1*G1, or if C1="x", then the value is E1*G1.
    What would be the best formula to use. G1 is a percentage entry. So the value will either be 0%-100%.

    I can do one, but don't know how to include all 3 columns .
    So far I have: =IF(A1="x",E1*G1,0)

    Thank you,
    Quinn

  5. I need to include a formula that shows if a cell displays 'yes' the cell will fail. It needs to be incorporated in the fomula below.

    =VLOOKUP($B$25,$A$71:$D$76,4,FALSE)

    Any help would be appreciated.

    Thank you,

    Natalie

  6. =IF(C4="D-486","Rs.2,800","")& IF(C4="D-487","Rs.1,800","")& IF(C4="D-488","Rs.3,200","")& IF(C4="D-489","Rs.2,400","")
    Using this formula for my deals but in last i can't get total ??
    Would you please help me in this
    thank you

  7. Perfect, thanks Svetlana.

  8. Hi, I'm got this formula to work only if it's not blank, but leave the cell blank if there is no number in it,

    =IF(T1<=249,"50",IF(T1<=499,"34",IF(T1<=999,"24",IF(T1=2500,"7")))))

    I've tried the isblank but to no avail.

    1. Hi Baz,

      You can add one more If function that checks for blanks, like this:

      =IF(T1="","",IF(T1<=249,50,IF(T1<=499,34,IF(T1<=999,24,IF(T1=2500,7)))))

  9. I want to apply thee conditions for example if value*600 or RW > "accept,"reject" and value Equal to Com plate , "TBA.

    X=0.5 Y=Com plate z=TBA/Reject/Accept A= Given Value

  10. I want to apply two conditions for example if transfer rate is 75% and score is between 65-70% then x should be given as 5%of x

  11. Thanks Gemnigirl63

  12. Hi Svetlana Cheusheva.
    Few days before I am using excel formulas "and" "or" but I have few confution using this formulas I am not sure where is will work.

    i needs your assistant. please provide your contact no or call me 9888862114

  13. Excel Sheet

    A B C D E
    1 CAST PER ADMISSION
    2 Gen 50 Yes
    3 SC 45 Yes
    4 Obc 45 Yes

    Logic

    OBC or SC 45% Yes
    Gen Need 50% Ys

  14. I Have 4 Tayp Of Vichel (Innova /Taveera/Ac/NonAc
    Waht Type Formula I Use In Extra Km Charge Multipal With 18/14/11/10

  15. I need a formula to compare B1 and C1 and take the higher amount and subtract from A1 PUT IN D1

  16. I just noticed an error in my initial submission
    It should be:
    IF a cell is >100, return a value of 100, but
    IF value is <.0032, then return a value of <.0032
    The rest between 100 down to 0 should be the same.
    Example:
    IF Cell A2= return value of
    120 100
    100 100
    .002 <.0032
    0 0
    10 10
    Thank you.

  17. I couldn't get a formula to work in excel where I want to get a value
    IF a cell is >100, return a value of 100, but
    IF value is <.0032, return a value of 100,"100"),IF(C1<.0032,"<.0032")))
    I tried many different combinations :(

  18. hi need a help

    logical function that returns 1 if the sales person has reached their target sales and 0 if not.

  19. A B C D Ans should be like this

    3 Blank 3 3 TRUE
    7 5 7 7 FALSE
    4 3 4 3 FALSE
    Balnk Blank 5 5 TRUE
    2 Blank 2 Blank TRUE
    3 3 1 1 FALSE

    I wan to ans like this, please tell me which how can i please...

  20. If cell J1,M1,P1 are = a or p I would like cell B1 and cell C1 = 1

    1. Hi Jim,

      Enter the following formula in cells B1 and C1:

      =IF(AND(OR($J$1="a",$J$1="p"), OR($M$1="a",$M$1="p"), OR($P$1 ="a",$P$1 ="p")), 1, "")

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  35. Hi,

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

    Please reply me quickly

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

  37. Thanks so muck my problem is solved

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

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

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

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

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

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

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

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

    Please Help with atleast two example.

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

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

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

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

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

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