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

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

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

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

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

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

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

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

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

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

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

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

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

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

  14. Thank you very much problem solved

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

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

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

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

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

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

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

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

  23. how to use subtotal function

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

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

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

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

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

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

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

  31. I am trying to put a 3 way formula into a box. I want Colum A1 to say Open if text is missing, closed if text is present, or suspended is there is NA in the cell. Like a 3 way condition through the cell. is this possible, that's

    1. Hi!

      Yes, you can do this using the nested IF formula, like this:
      =IF(B1="", "Open", IF(B1="NA", "Suspended", "Closed"))

  32. if i want to calculate time difference between two cell.Example A2=7:00a.m,and ,B2=12:00P.m and column C1=no of days.
    i want to implement formula If B2 minus A2 the result is greater or equal to 4 hours then C2=no.of day +1.

    1. Hi Sinan,

      I believe this is what you are looking for:
      =IF((B2-A2)>4/24,C1+1,"")

  33. so nice of you Svetlana

  34. I have a spreadsheet for commissions. I am trying to take the information and put it into another sheet. I need a formula that will take the value of possibly 3 cells. I would need it to the value of 2 cells if a amount in there. If not I need it to take the main value! Any ideas. I've come close but it does a False statement. Need it to the value of that cell over.

    Please help

  35. Hi, Svetlana,

    I'm a bit stuck on finding a formula that will suit my needs. My spreadsheet has column A: first name; column B: last name; column C: type of training; column D: date of training.

    I need a formula that will look at column A, B, and C and if they match any other row in column A, B, and C I need it to select the most recent date.

    this will remove all duplicate training and keep only their most current training record.

    Any suggestions? i'm stumped on this one.

    Also, I can certainly send you a copy of the workbook if that will help you understand, better, what I am looking for.

    Thank you in advance for your help.

    1. Hi Amanda,

      You can simply sort your table by column D (most recent dates will be at the top), and then use the standard Excel Remove Duplicates tool to find duplicates in columns A, B, C and delete them.

  36. with out using the mouse, with the help of keyboard to increase & decrease the size of the single cell in MS Excel sheet 2007

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