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 4. Total comments: 299

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

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

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

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

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

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

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

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)