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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. I have three conditions with five possible answers and cannot get the formula to work properly.
    Column A - if numbers match, move to next condition
    Column J - Type of resource(1=BLS or 2=RA)
    Column Q - Ensure row 2 > row 1
    Column T - Transport or not (0=No transport and 3=Transport)
    =IF(AND(A:A,J309=2,Q309>Q308,T309=3),"RATransport",IF(AND(A:A,J309=1,Q309>Q308,T309=3),"BLSTransport",IF(AND(A:A,J309=1,Q309>Q308,T309=0),"upgrade",IF(AND(A:A,J:J,Q309>Q308,T309=0),"RACHANGE",IF(AND(A:A,J309=2,Q309>Q308,T309=0),"downgrade")))))

  32. =IF(AND(OR(A2=1.5, A2=3.5,A2=4,A2=51,A2=54), B2<10),"Excluded",""))

    I want to add formula if A2 is 1.5/3.5/4/51/54 and B2<10, then C2 should be displayed with value "Excluded"

    1. Hi Deepa,

      Your formula is correct except for an extra closing parenthesis at the end:
      =IF(AND(OR(A2=1.5,A2=3.5,A2=4,A2=51,A2=54),B2<10),"Excluded","")

  33. Here is the formula I need to enter in column E. Can this be done?

    If column A is not blank, return B (for breakfast)
    If column B is not blank, return L (for lunch)
    If column C is not blank, return A (for Afternoon snack)
    If column D is not blank, return S (for supper)

    If multiple columns are not blank, I would need them to appear in column E with spaces between (B L A S) Thank you

  34. Thank you very much problem solved

  35. Hi I Need Help

    I am making some test reports ,I have given three grades for each parameter like deficient, optimum, excess.

    we have given formula for three conditions, there forma-ls =IF( H37 50,"Excess",".")
    my problem is how i can insert these three logical function in one Cell
    please Help

  36. Hi I need a help.
    I am making a format in excel for some company work where I want to track vehicle data.
    In example if on sheet 2 I copy whole report with defects from w,X,y,z lines and u want to make some format that when I copy data here on sheet 2 directly it will show how many defects from w,X,y,z. Not in numbers, I want them to copy in words. Like sumifs does for counting. Any idea?

  37. I'm trying to input a formula where

    Column R7 has pricing where some rows are € and some are $. I want to inpit a formula on the next column that if it contains the $ price - so basically if € price is in R7 it needs to be multiplied by 1.1, if $ price it should just be price given - make sense?

    1. Hi Simone,

      Are these text values with the 1st character being either € or $, or are they numbers with the Currency format applied?

      1. The cell selection is number then Accounting. In the upper bar you only see the numeric value.

  38. hi please me
    i struck on a formula.

    suppose A=A then A*100 but not > 200

    1. Sorry Gopal, your conditions are not clear. Could you try to explain diferently? For example:
      If A1="text" or X value,
      then A1*100,
      otherwise what?

  39. can you help me with the following grading system
    80%-100% A
    70-79% B2
    65-69% B3
    60-64% C4
    55-59% C5
    50-54% C6
    45-49% D7
    40-44% E8
    below 40 F9

    1. I can help you if you want to learn

  40. I want to find the place of either one of two station names from a cell. In other words, A1 contains some text matter, and I want to find the place of "New Delhi" or "Mumbai" whichever exists in it. Is there some formula to do this?

  41. Hi, I need a formula to work out commission based on business name... so if column A is "win" and column B is "business A" *column C by 0.02 or if column B is "business B" *column C by 0.05 Hope you can help? Many thanks in advance

    1. Hi Ben,

      Here you go:

      =IF(AND(A2="win",B2="business A"), C2*0.02, IF(AND(A2="win", B2="business B"), C2*0.05, ""))

      1. You are amazing thank you very much!! :)

  42. It is very useful to every one in working place.

  43. how to use subtotal function

  44. A1 = 0.66 & B1 = 0.45 & C1 = 0.12 I would like D1 to be A1 minus the half of C1 & E1 to be B1 + the half of C1 & if A1 = 0.45 & B1 = 0.66 & C1 = 0.12, E1 the must be A1 minus the half of C1 & E1 to be B1 + the 1/2 of C1

  45. Hey, i have to make a report that in G8 that i have to pay,i have balance in H8 and the I8 is the formula that showing that this amount is short or ok. I applied the formula =IF(H9>G11,"ok","Short")and it works, the problem is occur when this formula enter in the next row which is blank and the formula cell shows "short". I need that when its an empty cell, the formula cell gives blank message. I need assistance please

    1. Hi Farrukh,

      You can add another logical test that checks if H9 is not blank:

      =IF(AND(H9<>"", H9>G11),"ok", IF(AND(H9<>"", H9<=G11), "short", ""))

  46. I have one time in cell A1 (20:00:00) and I have another time in cell B1 (20:30:00). I want to know if the A1 falls within plus or minus 4 hours of time in B1. I am using this formula =IF(OR(A1"(B1-time(4,0,0)"),TRUE,FALSE) but I am not getting it.

    Kindly help

    1. Hi Gaurav,

      Try this one:

      =IF(ABS(A1-B1)<=TIMEVALUE("4:00"),TRUE,FALSE)

      1. u suggest me all excel function and formula please

  47. How do i do that could you please let me know ??

    1. Sorry, I cannot help with this, VBA code is outside of my skills. You can try posting this question on targeted Excel forums like mrexcel.com

  48. hi all,

    I want a formula that can look in a cell if there is a value in it lets say A1 then dont change the vale c1 d1 e1 f1 g1 otherwise delete value in c1 d1 e1 f1 g1

    Thanks

    1. Hi Nadeem,

      Regrettably, this task cannot be solved by using Excel formulas because a formula can return a value only into a cell where it is entered. You need a macro for this.

  49. Sorry some of my text was missing on my last post.

    I have a series of columns which all use > to test data in other columns. Is there a way the qualifier in my logical test ( >, = , 0,A1,"") and D1=if(B1>0,B1,"") etc.

    using one cell so I can change the series of tests:

    I could make E1 drive the tests to both be > or < or = .

    example without correct formatting:

    C1=if(A1 E1 0,A1,"") with D1=if( B1 E1 0,B1, "")

    Thanks!

    1. Sorry, I cannot understand what you are trying to achieve, maybe because the formals have been distorted by our blog engine. Can you describe the logic in words, please?

      1. Hi Svetlana,

        I am an aspiring Financial Analyst, do you have a link to formula I can use. Basically, popular formula that deal with logic, or shortcuts. Please email me the info. when you have time.

        Thanks

  50. Hi All,

    I have a series of columns which all use > to test data in other columns. Is there a way the qualifier in my logical test ( >, = , 0,A1,"") and d1=if(b1>0,b1,"") etc.

    using one cell so I can change the series of tests:

    I could make e1 drive the tests to both be > or < or = .

    example without correct formatting:

    c1=if(a1 e1 0,a1,"") d1=if( b1 e1 0,b1, "")

    Thanks!

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