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

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

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

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

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

  5. This One:-

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

  6. Hi Roshan,

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

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

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

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

    Plz explain this formula

    AB67 contains value 45000
    AC67 contains 30000-150000

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

  11. 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
    Priya R T
    Amit T
    .
    .
    .
    ...
    .
    .
    ..
    .
    .

  12. Sir
    How can import a particular Column from PDF to Excel file how ever i have many of PDF files and I want Import a particular Column (which contains some value) from PDF to Excel Please Help for this formula or Micro Please and Please

  13. i am trying to develop a syntax that will allow me to classify patients as having metabolic syndrome. The syndrome satisfies the condition meeting any 3 out of 5 criteria. My data is in excel. truly appreciate for any help.
    Thanks, Anam

    1. It's almost impossible to give you a definite formula without any details on the data you use in your table. It would be helpful to know whether the results are numbers or text and which criteria they should meet… What I can do is to only show you a super simplified example of what your data and function may look like
      =IF(COUNTIFS(B2:F2,"yes")>=3,"metabolic syndrome","")
      COUNTIF counts the number of met criteria, and if it's more than or equal to 3, it returns the result with a diagnosis, otherwise the cell remains empty.
      example

  14. E4 shows years experience.
    I need H4 to show the percentage equivalent from the table below.

    10-15 years = 1.500
    15-20 years = 2.250
    20-25 years = 3.250
    25-99 years = 4.500

    I so appreciate any assistance.

  15. Hi
    I want to match a column in a table or array by using vlookup formula it give me just one column from the table can i match the column and pick the whole table with help of vlookup if yes then please give me some hints
    Brief Explanation:
    For example
    =VLOOKUP(C17,A3:H7,4,FALSE)
    the desire column index is "4" it will take just column 4 what if it take the whole table not just a column?? can i do this please help me

    Thank you.

  16. Can someone please help me with this formula
    - IF(COUNTIF($AA4:$AD4,"Y")>1, COUNTIF(AA4:AD4, "EXCLUDED")>1, "Y","N"

  17. This helped. I wanted to give scores from result based on percentage. Thanks

    =IF(AND(J5>=100%,J5=121%,J5=141%,J5200%,1,IF(J5<100%,5,"")))))

  18. I need help using the IF function, to work out the score range
    High =9-13
    Moderate =5-8
    Low 0-4

    Thank you

    1. =IF(AND(A1>=9,A1=5,A1<=8),"Moderate",IF(A1<=4,"Low","")))

  19. Hello,
    I need help with using the IF function. In the example that my instructor uses, it looks like this:

    =IF([@[Billable Hours]]>4, "highly billable", "low billable"}

    when he clicks on cell D2 (the title is called billable hours)it actually shows up like this, however when i click on the cell D2 its just coming up as D2 not as ([@[Billable Hours]]

    If i leave the formula as saying D2 verses [@[Billable Hours]] i notice it doesnt come out right. How to I get the formula to recognize the title in the spreadsheet?

    thank you

  20. Hi!
    the formula is if(and(d2="mkt",e2="manager"),20000,18000),if(and(d2="mkt",e2="officer"),15000,13000)where is worng plz help me.
    shukriya

  21. I'm stuck. I have a sheet with a cell, D7, that can have "HM", "WH", "CM" or "HOTH" in it. Then there is a second cell, F7 that can be blank or have an "x" in it. What I need is a formula, lets say in AA7, that will return a "1" if D7 has "HM" and there is an "x" in F7. But it needs to return "2" if D7 has "WH" and there is an "x" in F7. So on and so forth with "CM" returning "3" and "HOTH" returning "4", but remaining blank if there is no "x" in F7. Please help.

  22. I am trying to get get a formula to work for the below can anyone help.
    In column c I have either a yes or no answer I'm column d I want to have it that if any of these 5cells in column c have a no answer this cell shows 0% and only if all cells show a yes answer then it shows 25% for all cells together not individually.

  23. Hello,

    I want to find out a solution for a simple problem. my query is I have to find out a solution with if statement. the condition is i have one rows and three columns, in that first row only numeric values will be there, in 2nd row it has only either yes or no only, 3rd one will be empty one.In the 3rd column i have display if 2nd columns has yes means it has print numeric value of 1st or else it has to display zero in 3rd. please provide a solutions as soon as possible.

    Thanks and Regards,
    D.Rameshkumar

  24. Column A includes customers who order in 2015, and column B customers who ordered in 2016. I want to have customers who ordered in 2015 but not in 2016. Is there a formula?

  25. Dear All,

    I want to combine the below formula in one formula, how do I do that?

    IF(A1="Male", IF(B1>18, "Man", "Boy"), "Boy")

    and

    IF(A2="Female", IF(B2>18, "Woman", "Girl"), "Girl")

    Thanks in advance :-)

  26. create a table with 2 columns and 10 rows in excel and give the name to the first column "color" 2nd column "Green Or Red" .If the First Column Contains green or red value the second column should return true otherwise false.
    [Hint:Use Logical Function]

    Madam Plzzzzz Solve This One I just want know How Could You Will Do this

  27. I can not see my yesterday post about if formula!
    thanks

  28. Hello Sevelana

    there is a formula uses for blan means if(isblank( A2,"",..........but it calculate the 0 is nothing it does not shown as blank formula cell.

    thanks for feedback

  29. Hi
    I want to use a formula like =if A2>01012017,A2<01022017,"Jan",...

    thanks for your help

  30. Hello all,

    I have a requirement that I am not sure how to handle in Excel.

    Here is my scenario

    Total Price (this takes the # of Apartment Units and multiplies by the Per Unit Price) OR...I can manually enter in the Total Price and it will then automatically calculate the Per Unit Price.

    I want to be able to enter in either field and have the other field calculated AND I don't want to lose the formulas I have in the cells. So, I could change one field and calculate the other field and then change the other field and have the other field calculated.

  31. Hi Svetlana,

    I have tried to find the many formula however i am not getting success, If you can help me out that is great.

    I have a query I am an admin and i want to prepare an attendance however i am not able to figure out which formula to apply, If any one can help me it that would be great.

    My company has a Rule that those who are coming before 10:00 we are giving levi, Those check in time between 10:01 to 10:30 are Late and after 10:30 it is halfday and same way those leaving in between 18:00 - 18:30 marked as early check out, Everyone has to check out after 18:31

    Those who are meeting the criteria of coming before 10:00 and leaving after 18:30 will be marked present.
    Those who are late by 10:00-10:30 and check out after 18:31 marked Late
    Those who are coming after 10:31 and leaing after 18:31 marked as halfday.
    Below is the biometric record from this i have to prepare attendance of more then 500 employee which is too tedious.
    Help me out i will be greatful.

    Att. Date InTime OutTime Shift S. InTime S. OutTime
    01-Jan-2017 NS 00:00 00:00
    02-Jan-2017 09:58 18:56 GS 09:30 18:30
    03-Jan-2017 10:13 18:39 GS 09:30 18:30
    04-Jan-2017 09:46 18:30 GS 09:30 18:30
    05-Jan-2017 09:43 18:59 GS 09:30 18:30

  32. As a Charity we have many clients. The spread sheet shows name and age in adjacent cells the next cells show catorgary of age spans ie 1-20, 21-40, 41-60, 61-80, 80-100. Our funder is asking us to show a score of 1 in appropriate catorgary cell corresponding to age. Do I use IF's & and's & or's and would be obliged if you can help...many thanks ...mike

  33. Hi, want to check error in the formula: if(B5="High", if(C5="High",(5+5)/2,if(C5="Medium", (5+3)/2, (5+1)/2)), if(B5="Medium", if(C5="High", (3+5)/2, if(C5="Medium", (3+3)/2, (3+1)/2))), if (B5="Low", if(C5="High",(1+5)/2, if(C5="Medium", (1+3)/2, (1+1)/2)))

    the purpose is to find out average of two values whose rating could be high, medium & low and weightage assigned in High = 5, Medium = 3 & Low = 1. Eg. if the combination of the two values if Medium & High, then the average should be (3+5) /2 = 4.

    Kindly guide.

  34. Hello!
    I am trying to right a nested IF statement and no luck. Each of the below statements works individually, but when I try using them together I am getting the #Value error. Any help would be greatly appreciated!

    =IF(AND(R4=Q4,T4=S4), "Match", "No Match"),IF(AND(T4=S4,R4Q4),"Campaign No Match",""),IF(AND(R4=Q4,T4S4),"Adgroup No Match","")

    Context:
    If values columns R and Q match as well as values in columns T and S, then the value should show "Match"

    If values in columns T and S match, but values in columns R and Q do NOT match then the value should show "Campaign No Match"

    If values in columns R and Q do match, but values in T and S do NOT match then the value should show "Adgroup No match"

    thanks in advance

  35. Hi there,

    I have a student that is doing research and is looking for a formula with the following conditions:

    Context: Each patient may have 3 or more rows of data. He has color coordinated rows that have date of knee surgery as one color and date of knee injection as another color in separate rows.

    Looking for a function that will look at each patient and if either of the injection dates were before the surgery date, then generate result of TRUE.

    I know the or statement is simple. But need the function to know to compare within the same patient and to compare the Injection fields that are in the white rows to the surgery fields in the green rows.

    Thanks in advance

  36. I sorted the holiday lookup problem out using the vlookup function. Now I face another problem, hopefully the last.
    In my spreadsheet the information gives a final result of the hours worked. The company takes this figure and pays 195 hours as normal time and the remainder as overtime, i.e. 203:00 normal hours = 195:00 (threshold hours and 8:00 (overtime hours). So this 195 hours is like a threshold value - if 195:00 is reached then the remainder becomes overtime. But here is the thing that also throws problems my way, if the amount of hours worked is below 195:00 then that value needs to be displayed in the threshold hours cell as well as 0:00 in the overtime cell, i.e. Threshold hours 190:00 -- Overtime hours 0:00. I wish I could upload my spreadsheet.
    Please help me

    Nick

  37. HI

    I need a help, I will give small example apart from that, I have a A,B,C up to Z,columns when I had enter in some value(1,2,...) it will be displayed certain sheet as well as I will create like A to Z sheets also, may be that numbers will be repite please reslove

    Warm regards
    kittu

  38. I am trying to create a spreadsheet to calculate hours payable from a daily entry of a starting time and a finishing time. From the date, day and time of work I need to extract various pieces of information. I have already managed to get most of the information but one or two problems still challenge me. I have made a list of holidays that are paid at different rate to normal hours) - I would like to test a date to see if it appears as one of the holidays, then to check if it was a Sunday and return the amount of hours worked. I have a few employees and I can do this manually but why if an excel formula can do it automatically for me. This spreadsheet will be an ongoing thing, please point me in the right direction.

    Kind regards
    Nick

  39. I need help for "if" formula:
    if A1="Sunday" equal "0"
    if A1="Saturday" equal "4"
    if A1="Monday" till "Friday" equal "8"

    I don't know how to write a formula.

    Best Regards,

    Saroeun.

  40. xyz A C D F

    I Want a formula if A<B<C<D then value "1", if A<B<D<C then value "2",if A<C<B<D then value "3", if A<C<D<B then value "4" and if A<D<B<C then value will be return "5" in single formula ....

    Thanks for your valuable time.

    Regards,
    Neetesh Shivhare , India

  41. Hi Sevetlana
    I want to code this formal I.e

    =if(c4*c8<c5"c4*c8","c5")
    how will I code this that the answer itself will show in the quotation box not the cell.

  42. Hi I've a requirement. If in a column A and for Row 1 and Row 10, if values are "paid", then the status of "C1" should get updated as "DONE".

    Please help in this. Your inputs are highly appreciated.

    Thanks,

    SrinivaS

  43. I want to know the difference between numbers in two different cell, if it is lower than the previous cell it should retain the current value, if it is more than the previous cell it should show the difference.
    For eg: If A67=14 and A60=2, the difference or change should show as 2 and not as -12 in A 61

    If A67=14 and A60=16, the difference or change should show as 2 in A61

    Please help.

  44. Please help!!I am trying to do a IF THEN formula over multiple columns where the column is marked with an "x" or is blank. So IF A1="x", then value is E1*G1, or if B1 ="x", then the value is E1*G1, or if C1="x", then the value is E1*G1.
    What would be the best formula to use. G1 is a percentage entry. So the value will either be 0%-100%.

    I can do one, but don't know how to include all 3 columns .
    So far I have: =IF(A1="x",E1*G1,0)

    Thank you,
    Quinn

  45. I need to include a formula that shows if a cell displays 'yes' the cell will fail. It needs to be incorporated in the fomula below.

    =VLOOKUP($B$25,$A$71:$D$76,4,FALSE)

    Any help would be appreciated.

    Thank you,

    Natalie

  46. =IF(C4="D-486","Rs.2,800","")& IF(C4="D-487","Rs.1,800","")& IF(C4="D-488","Rs.3,200","")& IF(C4="D-489","Rs.2,400","")
    Using this formula for my deals but in last i can't get total ??
    Would you please help me in this
    thank you

  47. Perfect, thanks Svetlana.

  48. Hi, I'm got this formula to work only if it's not blank, but leave the cell blank if there is no number in it,

    =IF(T1<=249,"50",IF(T1<=499,"34",IF(T1<=999,"24",IF(T1=2500,"7")))))

    I've tried the isblank but to no avail.

    1. Hi Baz,

      You can add one more If function that checks for blanks, like this:

      =IF(T1="","",IF(T1<=249,50,IF(T1<=499,34,IF(T1<=999,24,IF(T1=2500,7)))))

  49. I want to apply thee conditions for example if value*600 or RW > "accept,"reject" and value Equal to Com plate , "TBA.

    X=0.5 Y=Com plate z=TBA/Reject/Accept A= Given Value

  50. I want to apply two conditions for example if transfer rate is 75% and score is between 65-70% then x should be given as 5%of x

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