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

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

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

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

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

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

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

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

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

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

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

  11. Can you help me with this? If A1:B1 match any A:B then add matching C cells?

    1. Hello, TJ,

      Please try this formula:
      =IF(AND(INDIRECT("A1")=A3,INDIRECT("B1")=B3),"Matching","Non-matching")

  12. A1 = RECEIVED / CANCELLED / DECLINED (Dropdown)

    If A1="CANCELLED" then A2, A3, A4... will show CANCELLED
    If A1="DECLINED" then A2, A3, A4... will show DECLINED

    but

    If A1="RECEIVED" then A2, A3, A4... must be blank

    Please advise. Thank you

    1. GOTCHA! :D

  13. Hi, I am lost. can you help me with this problem.

    I want to choose from a drop down list for the following
    1) choose either ATM or CDM
    2) choose either Shift1 or Shift2
    3) choose the units available example 100, 200, 300 etc
    4) choosing from 1) and 2) and 3) will get the result

    the result will show
    ATM Shift1 100 units = xxx
    ATM Shift1 200 units = yyy
    ATM Shift1 300 units = zzz
    CDM Shift1 100 units = aaa
    CDM Shift1 200 units = bbb
    CDM Shift1 300 units = ccc
    ATM Shift2 100 units = ddd

    and so on

    Is it possible? Please advise. Thanks

  14. =IF(AND(C26>=0,C26=3,C26=5,5,"error")))))

  15. Hi

    I have a 3 in one if statement to put value to certain conditions:
    =IF(AND(C26>=0,C26=3,C26=5,5,"error")))))

    If I make the last condition = 5, it gives me the "error" msg for anything else typed in - which is correct. When I say >5 it gives me 5 for any word typed in which should be giving me "error" msg? How do I fix this?

    Thank you.

    1. Hello, Shirleen,

      This formula isn't true since a value cannot be equal to 3 and 5.5 at the same time. We suppose that you need a formula like this:
      =IF(OR(C26>=0,C26=3,C26=5.5),"error", "")

  16. Hi Svetlana!

    I need assistance calculating the following information in Excel 2013, if possible. If you can simplify, please do.
    --
    * The “Severity” and “Status” of a case, determines how often the case should be updated
    * Based on the “Last Worked” date, automatically calculate the “Due Date” for updating the case. The “Due Date” is based on business days and does not include U.S. holidays.
    * Automatically countdown the difference (“Days Remaining”) between the “Last Worked” date and the “Due Date”
    * Based on the “Status,” determine whether the case is “On Time,” “Overdue,” or “Completed”
    --
    Sev 1: If status is “In Progress” or “Customer Response Received”, update case everyday (1 day)

    Sev 2: If status is “In Progress” or “Customer Response Received”, update case at least every 1 - 2 days

    Sev 3/4: If status is “In Progress” or “Customer Response Received”, update case at least every 2 - 3 days

    Regardless of the severity, an update is not needed for cases with a status of “Closed” or “Resolution Provided.”
    --
    Example 1 (based on current date 11/2/2015):

    Case: 1234543 | Sev: 1 | Status: In Progress | Last Worked: 10/22 | Due Date: 10/23| Days remaining: -9 | Progress: Overdue

    Example 2 (based on current date 11/2/2015):

    Case: 5432123 | Sev: 1 | Status: Resolution Provided | Last Worked: 10/31 | Due Date: 11/2| Days remaining: | Progress: Completed
    --
    Thank you, in advance!

  17. Hey Svetlana,

    I might be going past what Excel is capable of doing, but I'm trying to find a way for a function in excel to filter information from a cell(ie 1.2.3.4 in A1, to 1 in A2, 2 in A3, 3 in A4, and 4 in A5), and I've figured that one out using a LEFT, MID, RIGHT combination.

    The problem comes when I put it into practice, because I have some that I can use a simple =LEFT(A1,1) =Mid(A1,3,1) =Right(A1,5,1) etc. and then I have some that I can't use that for(ie 12.34.56.78 or 123.456.789.123)

    Essentially I'm trying to find a way to have excel differentiate, or at the very least stop when it gets to the "." I've been looking for a few hours, and I'm pretty good at figuring out how to do things in unorthodox ways, but I've hit the limit of my knowledge on this one.

    I filter through large sums of data a day, so I'm trying to find a way to simplify this instead of typing these things out hundreds of time. Any advice you can give would be appreciated

  18. i want to determine date taxaton

    A1=date(21/10/2015
    B1=date(19/10/2015
    I want to show in C1

    I A1 date later then B1 date C1date=B1date

  19. Hi Svetlana

    When all the cells are blank. As the cells are filled "pass"/"fail", then filled appropriately.

    Thanks

    1. Hi Phil ,

      Then you can add one more AND function that checks for blank cells:

      =IF(AND(D45="", D46="", D47="", D48=""), "", IF(AND(D45="Pass", D46="Pass", D47="Pass", D48="Pass"), "Pass", "Fail"))

      Alternatively, you can use COUNTIF to make the formula more compact:
      =IF(COUNTIF(D45:D48, "")=4, "", IF(COUNTIF(D45:D48, "pass")=4, "Pass", "Fail"))

  20. Hi

    I have a column of cells in which "pass" or "fail" is entered. I have a formula in the last cell (D49) set to auto fill, as an overall "fail" if only one of the cells has "fail" in it.

    =IF((AND(D45="Pass", D46="Pass", D47="Pass", D48="Pass")), "Pass", "Fail")

    This is what I want. However, when the other cells are blank, it auto fills to "fail", as no "pass" is present. I would like D49 to be blank if there is no text in the Cells D45 to D46. What can I add to the formula to keep it blank if cells D45 to D48 are blank?

    Thanks

    1. Hi Phil,

      Do you want D49 to be blank when all the cells (D45 to D48) are blank or when at least one cell is blank?

  21. Sir,

    Thanks for Ablebits.

    It helped me lot about how to make combined use of IF, OR, AND logical functions.

    Regarads,
    P.G. Kerkar
    Goa.

  22. My question is related to And function.
    I have the below Duration in Dates
    Phase 1 Start : Phase 1 End | Phase 2 Start : Phase 2 End.
    October 1 : October 5 | October 4 : October 10

    I am comparing each October date if it falls in Phase 1 then return "X" Value and if falls in Phase 2 return "Y" Value.. However if you see OCT 3 and 4th falls in both Phases how to generate "Z" Value for those dates.

  23. what logic should i use for developing a duty roster of 40 people with the conditions as:
    No repetition of duty till each out of 40 personnel performs duty.
    No two personnel from same section within a week.

  24. HI i need help. im trying to ask excel to evaluate a less than but greater than scenario. for example: IF(A3 is greater than 1 but less than 100) , "...." ... Anybody?

    1. Hi Francisco,

      You can use an AND statement, like this:

      =IF(AND(A3>1, A3<100), value_if_true, value_if_false)

  25. Hi,

    I need a Function that is yes and no column are multiple as below, how use function.. i have tried =if(and(
    FUNCTION, But not able, plz help

    South9601 NO South9601 NO 0 N/A
    South9567 Yes South9566 Yes SOUTH7065 Yes
    SOUTH5965 NO SOUTH5965 NO SOUTH5965 NO
    SOUTH7146 NO SOUTH7146 NO SOUTH7146 NO
    SOUTH5067 NO SOUTH5067 NO SOUTH5067 NO
    South9734 Yes South9574 Yes 0 N/A
    SOUTH8400 NO SOUTH8400 NO SOUTH8400 NO
    SOUTH8288 NO SOUTH8288 NO SOUTH8288 NO

  26. I appreciate your skill & devotion to make known Excel Technology to the world. By the help your free Excel tutorials, I have developed my Excel skill & used it to solve many practical problems. Eg. I have created a smart Excel format to identify the least bidder with a short period & with 100% accuracy.
    Dear Sir: I have one question to be answered.
    I'm a teacher in a high school and a home room teacher in particular for 50 students. They are learning 10 subjects in a year. Thanks for your smart Excel Tutorials that enables me to manage the students mark using Excel like their total mark, average, Rank and their mark analyses. But at the end the year I'm still unable to decide (using Excel formula) whether each student is passed/Failed under the following conditions.
    A student failed/Detained if.....
    1. His total average mark is < 51% & scored <50% by one subject OR
    2. His total average mark is <53% & scored <50% by two subjects OR
    3. His total average mark is <55% & scored <50% by three subjects OR
    4. Failed by any four or more subjects.
    Thanks in advance
    Maru from Ethiopia

  27. hi,
    I have a column with 500 numbers from A1 to A500 , ranging from 1 to 999,999 and numbers ending in M ( million ). I want a IF function to convert them all to numbers without letter M ( million ).For example i want 1.7M to be converted to 1,700,000. Please help

  28. Hi,

    I have a spreadsheet that has three columns of 200 lines

    Column A has values of I,II,III and IV
    Column B has values of A,B,C,D,E,F
    Column C would be either High, Medium or Low based on the IA,IB,IC, IIA, IIB etc

    What is the calculation for this.

    To further complicate things I also need High to fill the cell Red background, Medium to fill the cell Orange background and Low to fill the cell Green background.

    Its driving me mad.

  29. =IF(AND(O13="",I13="",S13="",W13=""),"",IF(J13="NO","",IF(O13="",TODAY()-I13,O13-I13)))

    How can I make this formula work, IF Cell "O13 is Blank and the Data is available with Cell S13 or W13.

  30. I have a column with two forced choices (i.e., Group or individual). There are ten rows in the column. I am trying to create a formula that returns a "Yes" or "No" if the text "individual" is entered in any one of the ten rows in the column. The formula for one row in the column is =IF(F3="Individual", "Yes", "No"

    How do I get the formula to recognize all ten cells (i.e., F3:F12) to determine if the value is present in just one of the ten cells?

  31. svetlena !!!!!!
    please send me few excel sheet for working & practise please it my request for you

  32. I can't figure out which formula to use for the following scenario. Really hoping you can help me.
    -sheet 2 will have a football roster, column a - school, column b - # on jersey, and columns c-e - will have name, grade level, stats
    -I need to be able to type school code and jersey number on sheet 1 in columns a & b and have the players info display in columns c-e

    So during the football game, the announcer can type in a school code, like R and a jersey number, like 2 and the name of the player will pop up.

  33. formula in O4 cell D4-(C4+F4+I4+L4) and second formula IF(O4>=0,0,IF(O4<=0,O4)). how to used both formula in same cell.

    1. Hello PANKAJ,

      If you want to perform all the calculations with a single formula, here you go:

      =IF(D4-(C4+F4+I4+L4)>=0, 0, D4-(C4+F4+I4+L4))

      If you are looking for something different, please clarify.

      1. Please send me few excel work sheet for office usage, & practise please it my request for you....

        please

  34. Hello
    I am trying to create a bonus tool where 3 cells are T (Target), A (Actual) and B (Bonus)
    The bonus will be calculated by achievement over target but due to the variable nature I need to input the numbers on a spreadsheet.
    The logic is essentially as follows...
    if A=T(1 to 1.29) THEN B=A(0.01)
    in other words, if the actual is 100% to 129% of target then Bonus is 1% of Actual
    Then, if possible incorporate another rule for when 130% to 149% is achieved setting the bonus at 2% of Actual
    Finally incorporate one more rule for when 150% or more is achieved setting the bonus at 3% of Actual.

    Many Thanks

  35. Hi,

    I am trying to determine how i can take the value of two different cells and return the difference in another cell. I realize that if I subtract one cell from the other it will give me the difference. However this will not work correctly if the first cell's value is less then the 2nd cells value. I need the third cell to show either a positive or negative number based on the values entered into the first two cells.

    Thank You

    1. Hi Ron,

      I am not sure I understand the problem. For example, if A1 is 1 and B1 is 3, and you put the formula A1-B1 in C1, the answer will be -2.

      If you want the formula to do something different, please clarify.

  36. Hi,

    I'm looking for a formula that will count. EG if both cells a1 and b1 are negative #s, then enter cell b1 in cell c1.

    1. Hi Crystal,

      If #s means negative numbers, you can enter the following formula in C1:

      =IF(AND(A1<0, B1<0), B1, "")

  37. hi,
    i have some prob to derive this help me to solve.i have 85 in a1 and 90 in b1 i looking to implement this condition
    a1-b1 plus or minus 0 to 1 means the result would be maxvalue+3
    a1-b1 plus or minus 2 to 4 means the result would be maxvalue+2
    a1-b1 plus or minus 5 to 10 means the result would be maxvalue+1
    a1-b1 plus or minus >10 means the result would be maxvalue

  38. I am not able to merge the If and formula which is below mention as getting error #VALUE
    please help me to get resolve this issue
    =IF(AND(D10="Ascend",K10>0),F10/60*500,IF(AND(D10="ATC",K10>0),F10/60*500,IF(AND(D10="CNIL",K10>0),F10/60*500,IF(AND(D10="GTL",K10>0),F10/60*500,IF(AND(D10="TVI",K10>0),F10/60*500,IF(AND(D10="VIOM",K10>0),F10/60*1000,0)))))),IF(OR(D10="INDUS",D10="BHARTI"),IF(AND(I10>=99.9%,I10=99.7%,I10=99.5%,I10=99%,I10<99.5%),25%*S10,IF(I10<99.5%,30%*S10,0))))))

  39. i want to give 3logics by using IF condition i.e.,if a1 is greater than30=ok,lessthan30=Not good and if respective cell contain negative figure =NA, then how to give formula

    1. Hello KRISHNA,

      You can use a nested IF formula like this:

      =IF(A2<0, "NA", IF(A2<30, "not good", "ok"))

      1. thank You

  40. Hi

    i am trying to work out a formula were a cell have 3 drop down suggestions, from the dropdown picked i want another cell to be able to determin my anser

    =IF(AB5="introduced",AE5*0.2),IF(AB5="introduced returning","5"),IF(AB5="existing","0")

    thought this might work but i am getting "value" returning and not the answer i need

    Kind regards

    1. Hi Ken,

      The correct syntax of a nested IF formula is as follows:

      =IF(AB5="introduced",AE5*0.2, IF(AB5="introduced returning", 5, IF(AB5="existing", 0)))

      1. Thank you very much.. silly mistake on my part , works perfect.

  41. I ma trying to put if and but to get the answer to be a number of a difference between two cells how can i take this up , the results are coming as just =A1-B1 , How do i get the formula to return the result of this =IF(AND(EV6>0,EV6<=B6),"=EV3-B3","0")
    Thanks

    1. Here you go:
      =IF(AND(EV6>0,EV6<=B6), EV3-B3, 0)

  42. Hello!
    Can you help me on table below :
    date Party Material target achieved balance
    24/7/2015 Delhi dairy Milk

  43. hello
    you are the best teacher;)
    thank you:)

  44. Hi I am having trouble creating a formula to reflect:
    If preferred is True (F4), or the budget (C4) amount is greater than the quote (D4), then display the quote (D4) less the discount (H4), otherwise display nothing.

    My formula: =IF((F4="true")*OR(C4>=D4-H4),D4-H4,"")

    is not showing all the necessary information. Would really appreciate the help.

    Thank you

    1. Hi Kelly,

      Here is th correct syntax if F4 is the Boolean value TRUE:
      =IF(OR(F4=TRUE, C4>=D4-H4),D4-H4,"")

      If F4 is a text value:
      =IF(OR(F4="true", C4>=D4-H4),D4-H4,"")

  45. Hi,
    Wanted to check the next immediate number how can I do.

    example
    700
    600
    500
    400
    300
    200

    if value 650 than immediate high value is 700 and low is 600,how to display

  46. hi svetlana
    m trying a1*b1=c1 but the problem is if there are no any number at a1 or b1 c1 show as #VALUE! i want c1 shouldnt show any thing its should be blank is it possible then plz explain

  47. hi Svetlana
    I have column A=20, B=25, C=20,D=25, Want to put formula in column E that if D is less than or equals to column B then the value would be column D or zero.

    Please help me with some formula

    Thanks & Regards
    Barun Ghosh

  48. Hi Svetlana,

    I have try to count truck trip no in our data sheet,

    We have data invoice no and vehicle no and time , so we need count truck trip,

    Date Vch/Bill No Time Veh No. TRIP Manaul Trip
    01-06-2015 AIL-400957 07:14 AM HR55P5729 Need Formula 1
    01-06-2015 AIL-400958 07:15 AM HR55P5729 1
    01-06-2015 AIL-400959 08:25 AM HR55P5731 2
    01-06-2015 AIL-400960 08:26 AM HR55P5731 2
    01-06-2015 AIL-400961 08:28 AM HR55P5731 2

    Please help me if you could give me formula for my situation.

    Sanjay

  49. Hi Abdul,

    I think you can use a formula similar to the one below, where A1 is the sample value and B1 is the value you are comparing to the sample:

    =IF(AND(B1>=A1*0.8, B1<=A1+A1*0.2), "pass", "fail")

    If B1 is within the range A1±20% inclusive, the formula returns "pass", "fail" otherwise.

  50. Hi there!
    I don't know much about entering logical formulas in Excel. Please help me if you could give me formula for my situation.
    In my analysis the values I'm getting about different analytes has be to within the range of ±20 percent to be acceptable. Test results "Pass" if it is in this range otherwise "Fail".
    Thanks
    Abdul

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