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

  1. PLS I NEED YOUR HELP, BEEN THINKING ABOUT THIS IN THE PAST 2 MONTHS BUT UNTIL NOW I DONT HAVE ANY SOLUTION TO THINK OF.
    RIGHT NOW IM USING 2 WORKBOOKS TO WORK ON THIS. BUT I WANT TO USE ONLY ONW WORKBOOK . THIS WORKBOOK HAS TO BE USE EVERY YEAR WITHOUT CHANGING THE EXCHANGE RATE OF 2017 TO 2018. IF YOU WANTED TO KNOW MORE ABOUT MY WORKBOOK I CAN EMAIL YOU THE TEMPLATE.

  2. Looking for a formula as follows:

    If Cell A1 contains DELETE then return nothing, else return Cell contents of A1 to C1.

    And another formula: If Cell C1 contains nothing, do nothing. Else return contents of Cell B1 to D1.

    Basically, if this example uses a period to indicate a new cell it should look like this.

    Sample 1: Apple.27.Apple.27
    Sample 2: DELETE.31. .
    Key: (value1.value2.formula1.formula2)

  3. Hi

    i am trying to use these logical function OR, AND, NOT in farmula with offset function i dont know how to write this farmula,

    i want make a sheet for Patient Origin and want to calculate that patient origin by week number to select week number 1 from the week list and all patient origin of week 1 apear, if you want to look my database/spread sheet let me know i can send that to you..,

    Amjad khan

  4. Need a formulas to create an A,B & C analysis
    A >8000 Irs, B <8000 Irs and C as <1000 Irs.
    Thanks

  5. I want a cell to check another cell for the list below and add the appropriate number if it is found. Here is list of items:

    TS=2.50
    SL=2.50
    W SERV=3.00
    G SERV=2.50
    S SERV=5.00

    Please help and thank you

    1. I figured it out:

      =IF(O2="TS","2.5", IF(O2="SL","2.5",IF(O2="W SERV","3","?")))

      Thanks

  6. please i need formula for adding and subtracting of date, eg: add 35 days to 23/2/2014 that will give me 30/3/2014

  7. Please need a formula for the following:

    If the following cell (g2) contains any of these: early 5, mid 5, late 5 and the other cell (h2) has a value >=38 then I need cell (I2) to say "YES" but if cell (h2) has a value<=12 then I need cell (I2) to say "NO" otherwise cell (I2) needs to say "NA"

  8. I want formula for date.
    I have to find out the month of increment from joining date of an employee on excel sheet.
    Such as if date of joining is up to 15th the date of increment will be same month and if date of joining is after 15th then date of increment will be next month.
    Example, Date of joining of any employee is 12/03/2017 then increment month will be March, whereas date of joining is 16/03/2017 then increment month will be April.

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =TEXT(IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),A1),"mmmm")

      Hope this will help.

  9. I want formula for date. I have to find month of increment from his joining date.
    Such as if date of joining is up to 15th the date of increment will be same month and if date of joining is after 15 then date of increment will be next month.
    Example, Date of joining of any employee is 12/03/2017 then increment month will be March, whereas date of joining is 16/03/2017 then increment month will be April.

  10. Hi Frnds

    I need help to solve the below condition

    =IF(OR((M10+N10+O10)=0),"",(M10+N10)/(M10+N10+O10)*100),IF(OR((M10+N10+O10)=""),"",(M10+N10)/(M10+N10+O10)*100))

    If anyone help me to solve this plz
    Need to implement urgently

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  11. Hi Frnds

    I need help to solve the below condition

    =IF(OR((M10+N10+O10)=0),"",(M10+N10)/(M10+N10+O10)*100),IF(OR((M10+N10+O10)=""),"",(M10+N10)/(M10+N10+O10)*100))

    If anyone help me to solve this plz

  12. Guy's please help to fixed the formula in below condition. I have applied formula =(IF(O2<10,"<10",IF(O2<20,"10-20 Min",IF(O2<30,"20-30 Min",IF(O2<60,"30-60 Min",IF(O2=120,">120 Min"," "))))))) but result is not proper.
    Time Condition
    2:11:59 120 Mins

    1. Hello,
      If I understand your task correctly, please try the following formula:

      =IF((VALUE(O2)*24*60)<10,"<10",IF((VALUE(O2)*24*60)<20,"10-20 Min",IF((VALUE(O2)*24*60)<30,"20-30 Min",IF((VALUE(O2)*24*60)<60,"30-60 Min",IF((VALUE(O2)*24*60)<120,"60-120 Min",IF((VALUE(O2)*24*60)>=120,">120 Min"," "))))))

      Hope it will help you.

  13. Guy's please help to fixed the formula in below condition. I have applied formula =(IF(O2<10,"<10",IF(O2<20,"10-20 Min",IF(O2<30,"20-30 Min",IF(O2<60,"30-60 Min",IF(O2=120,">120 Min"," "))))))) but result is not proper.

    Time Condition
    2:11:59 120 Mins

  14. Hi,
    i'm working on a daily tracker which is updated on a daily basis value "0" or more than 0 . like 1 or 2. in left to right order.

    i want something which should indicate me if last 3 days value is "0".

    please help me

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  15. Hi, i need a formula to calculate a range table of age like 15-34, 35-44, 45-49 and they have to match with 2 different class, with 2 different amounts link to each class, if the amount in column A is <=34 and column B is "2" the amount should be the amount in cell G2 or if Column B is "1" it should be the amount in cell G3. hope you can assist.

    1. Hello,

      If I understand your task correctly, you need 2 different formulas for cells G2 and G3:

      For cell G2:
      =IF(B1=2,IF(AND(A1>=15,A1<=34),"15-34",IF(AND(A1>34,A1<=44),"35-44",IF(AND(A1>44,A1<=49),"45-49",""))),"")

      For cell G3:
      =IF(B1=1,IF(AND(A1>=15,A1<=34),"15-34",IF(AND(A1>34,A1<=44),"35-44",IF(AND(A1>44,A1<=49),"45-49",""))),"")

      Hope this will help you!

  16. Hi, can you help with how do i use 'one of' constraint: like if (A1 is one of ARRAY;Y;N). Haven't found anything that would match and having like 600 lines in the array would be complicated to set OR(A1=value1;A1=value2.....)
    Thank you!

    1. Hello,

      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.

      I'll look into your task and try to help.

  17. I have three colms as
    A1 (City), B1 (Quantity), C1 (Amount)

    If Colm A1 is Karachi, I want then quantity (B1) should multiply with 15 in Amount colm (C1)

    If Colm A1 is Lahore, I want then quantity (B1) should multiply with 15 in Amount colm (C1)

    1. Hello, Azam,

      Please try the following formula:

      =IF(A1="Karachi", B1*15,IF(A1="Lahore", B1*15,""))

      Hope it will help you.

  18. I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
    Thanks

    1. Hello, Patrick,

      Please try the following formula:

      =IFS(A1<=8,"$x",A1<=15,"$y",A1<=20,"$z",A1<=25,"$v",A1<=30,"$w")

      Hope it will help you.

  19. Hi,
    I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
    Thanks

  20. hi...can i ask for help..how can i solve this logic delta column:

    if stock > max >>> stock - max (the value must be highlighted in red)
    if min <=stock >> stock quantity (the value must be highlighted in green)
    if stock>> stock-min (the value must be highlighted in yellow)

    1. Hello, jelmer,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  21. some plz help me to use this

    400,001 to 500,000 Slab 02 (AZ163-400000)*2%
    500,001 to 750,000 Slab 03 2000+(AZ164-500000)*5%
    750,001 to 1,400,000 Slab 04 14500+((AZ165-750000)*10%)
    1,400,001 to 1,500,000 Slab 05 79500+((AZ166-1400000)*12.5%)
    1,500,001 to 1,800,000 Slab 06 92000+((AZ167-1500000)*15%)
    1,800,001 to 2,500,000 Slab 07 137000+((AZ168-1800000)*17.5%)
    2,500,001 to 3,000,000 Slab 08 259500+((AZ169-2500000)*20%)
    3,000,001 to 3,500,000 Slab 09 359500+((AZ170-3000000)*22.5%)
    3,500,001 to 4,000,000 Slab 10 472000+((AZ171-3500000)*25%)
    4,000,001 to 7,000,000 Slab 11 597000+((AZ172-4000000)*27.5%)
    Exceeds from 7,000,000 Slab 12 1422000+((AZ173-7000000)*30%)

  22. IF(AND(F47<=22500000,F4750000000,F47<=100000000),0,E43*0.1))

    I AM USING THIS FORMULA BUT THE RESULT IS NOT GIVEN WHERE IS WRONG
    PLEASE GIVE ME SALUTATION

  23. IF(AND(F47<=22500000,F4750000000,F47<=100000000),0,E43*0.1))

    I AM USING THIS FORMULA BUT THE RESULT IS NOT GIVEN WHERE IS WRONG
    PLEASE GIVE ME SALUTATION

  24. hell can you help me. i sen message to facebook and come friend.

  25. I have data like this

    123415678
    432128765
    001218090

    These numbers represents ID numbers If the 5th number is 1 then the ID belongs to a male if its 2 then the ID belongs to a female

    1. Hello ,

      Go the next column, and use this formula (=Left(A1,5))
      in Column C , put this formula ( =Right(C1,1)

      Codes Left Coding Right Coding Sex
      123415678 """=LEFT(A2,5) """=RIGHT(B2,1) ''''=IF(C2="2","Female","Male")
      432128765 43212 2 Female
      301218090 30121 1 Male
      214510025 21451 1 Male
      301228090 30122 2 Female
      301228090 30122 2 Female
      301218090 30121 1 Male

    2. Hello ,

      Go the next column, and use this formula (=Left(A1,5))
      in Column C , put this formula ( =Right(C1,1)

      Codes Left Coding Right Coding
      123415678 """=LEFT(A2,5) """=RIGHT(B2,1)
      432128765 43212 2
      301218090 30121 1
      214510025 21451 1
      301228090 30122 2
      301228090 30122 2
      301218090 30121 1

  26. Wanted to extend my thanks. This is exactly what I was looking for!

  27. Using excel formulae, find out the following
    1. How many have neither registered nor completed any of the 3 courses?
    2. How many have registered or trained in atleast 2 of the 3 courses?
    3. How many have not been trained in any of the 3 yet?

    R - Registered for training (training not done yet)
    T - Trained
    Blank - Neither

    Name SQL SAS Excel
    Prakash R T
    Rahul R
    Rajiv
    Priya R T
    Amit T

  28. =IF(NOT(isblank (M6)), "unaccomplished", if (isblank N6))' unaccomplsihed
    can somebody help me?

  29. I have an excel sheet named "ALL".Just trying to apply formula for counting open points related to Business Development.I applied below formula but its not working can you please check & confirm

    =COUNT(AND(All!K4:K1000="Business Development",All!N4:N1000="Open"))

  30. Hello, I need help. I need to verify whether my column C's (numeric) information and column B's (description) information are appropriate and accurate for my column A's (code). Thanks in advance.

  31. Hi,
    please solve this question.
    If Mr.A has 125 coins then he will get "1 Apple" and if Mr.A has 170 coins then he will get "1 Apple" for 125 coins and on balance 45 coins, he will get "1 Lemon" on every 25 coins.

    Please convert the same in to logical formula in excel.

    1. LET CELL A1 have the coins MR. A HAS, THEN copy THE following EXPRESSION
      =CONCATENATE("IF MR.A HAS 125 COINS, THE HE WILL GET 1 APPLE, AND IF MR. A HAS"," ",+A1," ","COINS"," ","THEN HE WILL GET"," ",ROUNDDOWN(+A1/125,0)," ","APPLE(S)"," ","AND ON BALANCE"," ",A1-(ROUNDDOWN(+A1/125,0)*125)," ","COINS, HE WILL GET"," ",ROUNDDOWN(+(A1-(+ROUNDDOWN(+A1/125,0)*125))/45,0)," ","LEMON ON EVERY 25 COINS")

    2. =IF(G14="Mr.A",(IFS(H14=125,"1 Apple",H14=170,"1 Apple, 1 Lemon"))," ")

      Hope this will help you

  32. =IF(AND(V17="B",V18<="470"),"127","0"),IF(AND(V17="BC",V18<="700"),"112","127"),IF(AND(V17="C",V18<="420"),"0","112")

    I AM USING THIS FORMULA BUT THE RESULT IS " #VALUE! "
    PLEASE GIVE ME SALUTATION.

    1. Muhammad, Possible Solution
      1) To make this easier, separate the formulas and evaluate each one separately. I noticed that there is overlapping logic. If formula #1 is False you get a -0- and if formula #3 is True you get a -0-, and the same thing with 127 and 112 across all 3 formulas. How would you know which values gave you the answer? Also, what if the input values do not match your expected answers, do you evaluate them? And your attempt to nest the IF statements is wrong.
      Nesting as below will work but you will still only get 112, 127 or -0-.
      =IF(AND(B1="B",B2<="470"),"127",IF(AND(B1="BC",B2<="700"),"112","0"))
      Hope this gives you some insight.

  33. Hi
    I have 9 sheets in the same workbook variously populated with 0 and 1. In a separate sheet I want to fill a cell with "1" if 1 occurs in the same cell in any of the 9 sheets, or "0" if it doesn't occur at all. I have tried using the following:

    =IF(OR('Training NetworkB'!C5="1",KnowledgeSharingBtxt!C5="1",'Encouragement NetworkB'!C5="1",'Organisation NetworkB'!C5="1",'Monitoring NetworkB'!C5="1",'Networking NetB'!C5="1",'Labour sharing NetworkB'!C5="1",'Conflict NetworkB'!C5="1",'Phys_Finan Capital NetB'!C5="1"),"1","0")

    It returns a 0 in all cells, even if a 1 occurs. Can you help at all?
    Many thanks in advance

    1. Hi,
      It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.

    2. Hi,
      It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.

  34. Hi,
    I need to make a formula to count 3 different columns but counting just one time if they have more than one value for the others 2 columns.
    How I can do that??
    I try different ways but I don't know do it!
    Thanks!!

  35. hi
    i need a formula for. .

    if found 0152 then get 18
    &
    if found 6305 then get 5

    1. Hi

      Thank you for contacting us.

      Please try the following formula:

      =IF(A1 = 152, 18, IF(A1=6305, 5, " "))

      I hope this helps. Please let me know if you have any other questions or difficulties.

      1. Hi Mary ,
        can you help me in macro how to record macro ?

        1. Hi Amit,

          First you have to save as your Excel sheet to .xlsm. after this step you have to go view ribbon and choose Macro option. There you can find macro recording.

          Start recording and work in the same sheet without using mouse, it works perfectly. Because if you use mouse Macro may not write program as per your clicks.

          Once you stop recording you just give an non-calculated report which you did earlier and run macro from same macro option.

          Regards,

          Mohan

  36. Hi Svetlana
    I need a formula for comparison of numbers with text
    E.g.
    If x is less than or equal to 1600 them yes or if x is not available then "not avl"
    But if X is more than 1600 then wow but if x is not avl then not avl

    1. =IF(OR(B6="",B6<=1),"Not Available",IF(B6<=1600,"Yes","Wow"))

    2. =IF(C18>1600,"Wow",IF(C18>0,"yes",Not Available))

      1. =IF(C18>1600,"Wow",IF(C18>0,"yes","Not Available"))

        1. Hi Please use this.
          =IF(OR(C24="",),"Not Available",IF(C24>=1600,"Yes","Wow"))

    3. '=IF(+C6=0,"not avl",IF(+C61600,"wow")))

    4. Hi 201, please use below Formula

      =IFERROR(IF(VLOOKUP(D1,A1:B13,2,0)>1600,"Wow",IF(VLOOKUP(D1,$A$1:$B$13,2,0)<=1600,"Yes","0")),"Not Avl")

  37. Hello all,

    Please help..
    Distance 50 kg 100 kg 250 kg 500 kg 1000 kg
    Upto 25 NIL NIL NIL NIL NIL
    26-100 km 650 750 1000 1150 1500
    101-150 km 1000 1250 1500 1750 2000
    151-200 km 1250 1500 1750 2000 2250
    201-300 km 1500 1750 2000 2500 3000

    My query is
    If distance 100 km & wg 50 so charge 650/-. if distance 100 km & wg 100 kg charging cost 750/-. if distance 100 km & wg 250 kg charging cost 1000/-. same applicable for 500 kg & 1000 kg.
    same for 150 to 300 km & kg 50 to 1000 kg.
    my query is condition one is same but condition 2 change so which formula I can use ?

    1. hi Mahesh,

      write down your distance on a row on top and quantity in left side colomn such as
      100 150 200 250 300 (start this from "B" cell)

      50
      100
      150
      200
      250

      Then apply this in cells and change the value of only 100 in formula with your distance value.

      =IF(OR(B18=100,A19=50),650,IF(OR(B18=100,A19=100),750,IF(OR(B18=100,A19=150),850,IF(OR(B18=100,A19=200),900,1000))))

  38. Hi, In my spreadsheet I have the following information:
    A2="Gold", B2="Silver" C2="Bronze, D2=a blank field. I'm trying to create a single formula in column E2 which captures the following:
    If range A2 to D2 contains "Gold" then E2="Gold";
    If range A2 to D2 does not contain "Gold" but it contains "Silver" then E2="Silver";
    If range A2 to D2 does not contain "Gold" or "Silver" but contains "Bronze" then E2="Bronze";
    If range A2 to D2 fields are all blank fields then E3="unrated"

    Hope you can please assist. Thank you.

    1. =IF(OR(A2="Gold",B2="Gold",C2="Gold",D2="Gold"),"Gold",IF(OR(A2"Gold",B2"Gold",C2"Gold",D2"Gold")*OR(A2="Silver",B2="Silver",C2="Silver",D2="Silver"),"Silver",IF(OR(A2"Gold",B2"Gold",C2"Gold",D2"Gold")*OR(A2"Silver",B2"Silver",C2"Silver",D2"Silver")*OR(A2="Bronze",B2="Bronze",C2="Bronze",D2="Bronze"),"Bronze","Unrated")))

      May be it will help you

  39. hi
    i have one err
    exp
    sheet1 cell A2 in a name SOM

    sheet2 cell b2 in formula given =sheet1!A2

    but he shown name a repeat in cell

    exp - SOM-SOM-SOM

    PLS HELP ME

  40. Hi Svetlana Cheusheva!
    Please help me with complete list of excel logical function. it's urgent.

  41. Hi there, I am not sure how to phrase my question, so please be patient with me!
    I have a spreadsheet, which identifies results (based on selections) and returns scores

    i.e. E3 is the result column
    C3 and D3 are Player 1 and 2 respectfully.

    Now, Player 1 and player 2 choose a number between 1-6. the result (from the game) goes in column E and the formula in F returns "Player 1" or "Player 2" if their answer matches the result...I hope you follow! this all works great - until, Player 1 and player 2 both choose 3!!!
    This gets even more complicated if 3 is the winning result!
    I need a win, lose, draw, no result type scenario but NOTHING I have tried will resolve the issue :( PLEASE HELP!

  42. Hi,

    I have 2 formulas that both work however I need to have them work together as an "OR" situation and can't come up with the right formula for that. Can you help?

    =IF(AND(D45=1,Q45="N"),(I45*0.0925))
    =IF(AND(D45=1,Q45="Y"),P45)

    1. HI Dave

      do just like this

      =IF(AND(D45=1,Q45="N"),(I45*0.0925),IF(AND(D45=1,Q45="Y"),P45),0)

  43. hi,
    I want to solve below conditions.

    9:00am to 10:00am ="A"
    10:00am to 11:00am = "B"
    11:00am to 12:00pm = "C"

  44. Hello All, please help to make logical formula in excel

  45. This One:-

    =IF(A1<4,"short",IF(A1=6,"Long")))

  46. Hi Roshan,

    Try This:-
    =IF(A1<4,"short",IF(A1=6,"Long")))

  47. I want to solve simple 3 equation.
    Ex:- 6inch> tall, 4 to 6 medium, 4inch<Short.

    1. Hello, Roshan,

      try this formula:
      =IF(A1>6, "Tall", IF(AND(A1>=4,A1<=6), "Medium", IF(A1<4, "Short", "")))

  48. Can you get an IF AND OR BUT IF formula

    Example: =IF((AND(OR(D2="Peach",D2="Orange",D2="Apple"),OR(H2="Standard",H2="Close-Cut")),1,-1) I require a but if Cell D3=1 then the value id 1

    1. Hello,

      I'm sorry but your task is not entirely clear.
      What do you mean by "value id 1"? Where do you want to see this id number? Do you want to include D3 in this condition as well? Please specify so we could advise you with your formula.

  49. =IF(AND(AB67>100%,AC670),1%,0%)

    Plz explain this formula

    AB67 contains value 45000
    AC67 contains 30000-150000

  50. Here is the data:

    Total surveys 20 and the csat is 18 where I'm standing at 90%. Iam checking for the formula where, how many csat's required to hit 95%

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