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

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

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

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

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

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

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

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

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

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

    thanks for your help

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  27. Perfect, thanks Svetlana.

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

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

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

  31. Thanks Gemnigirl63

  32. Hi Svetlana Cheusheva.
    Few days before I am using excel formulas "and" "or" but I have few confution using this formulas I am not sure where is will work.

    i needs your assistant. please provide your contact no or call me 9888862114

  33. Excel Sheet

    A B C D E
    1 CAST PER ADMISSION
    2 Gen 50 Yes
    3 SC 45 Yes
    4 Obc 45 Yes

    Logic

    OBC or SC 45% Yes
    Gen Need 50% Ys

  34. I Have 4 Tayp Of Vichel (Innova /Taveera/Ac/NonAc
    Waht Type Formula I Use In Extra Km Charge Multipal With 18/14/11/10

  35. I need a formula to compare B1 and C1 and take the higher amount and subtract from A1 PUT IN D1

  36. I just noticed an error in my initial submission
    It should be:
    IF a cell is >100, return a value of 100, but
    IF value is <.0032, then return a value of <.0032
    The rest between 100 down to 0 should be the same.
    Example:
    IF Cell A2= return value of
    120 100
    100 100
    .002 <.0032
    0 0
    10 10
    Thank you.

  37. I couldn't get a formula to work in excel where I want to get a value
    IF a cell is >100, return a value of 100, but
    IF value is <.0032, return a value of 100,"100"),IF(C1<.0032,"<.0032")))
    I tried many different combinations :(

  38. hi need a help

    logical function that returns 1 if the sales person has reached their target sales and 0 if not.

  39. A B C D Ans should be like this

    3 Blank 3 3 TRUE
    7 5 7 7 FALSE
    4 3 4 3 FALSE
    Balnk Blank 5 5 TRUE
    2 Blank 2 Blank TRUE
    3 3 1 1 FALSE

    I wan to ans like this, please tell me which how can i please...

  40. If cell J1,M1,P1 are = a or p I would like cell B1 and cell C1 = 1

    1. Hi Jim,

      Enter the following formula in cells B1 and C1:

      =IF(AND(OR($J$1="a",$J$1="p"), OR($M$1="a",$M$1="p"), OR($P$1 ="a",$P$1 ="p")), 1, "")

  41. hi, i've a excel sheet with multiple column. what formula do i use to calculate the trip time if the criteria is A and B by subtracting D to C

    A (Compy) B(veh) C (in) D (out) E (Trip Time)
    XYZ XD 3787M 11:26:23 11:26:29 ?
    XYZ XD 3658C 11:33:12 11:33:21
    XYZ XD1900R 11:36:30 11:36:43
    XYZ XD 6522R 12:17:29 12:17:38
    XYZ XD 3787M 12:19:30 12:19:40
    XYZ XD 3658C 12:21:33 12:21:40
    XYZ XD1900R 12:23:04 12:23:14
    XYZ XD 6522R 12:24:37 12:24:46
    XYZ XD 3787M 12:26:22 12:26:30
    XYZ XD1900R 12:28:30 12:28:37
    XYZ XD 6522R 12:30:14 12:30:25

  42. Hi,

    I am trying to work on a possibility where if condition on two cells are correct then the formulae should fill the relevant code dependent on it in a new cell.

    for example if a15=value(a2:a33) & b15=value(b2:b33) then the C15 should be value(d2:d33); the values in d is unique for all cells where the first two match.

  43. I'm doing a mark sheet , now one of the teacher wants to have absent appear in marks. If they put '0' marks in the box and I want it to appear as'ABSENT'. But I have put a formula in it to calculate so how do I join together saying that If marks is 0 then grade is absent.

  44. Hi i need a formular that will look up name of employee name and return text "YES" or "NO" if a row contains a negative value. kindly assist. Thanks

  45. Hello I am working tracker that tracks #of observations. In order to be 100% compliant there needs to be 3 observations done but one of those 3 observations need to be a 1 of 2 types of observations. So I need the tracker to show both the total and 100% goal met if one of the two specific observations were completed.

  46. In Col I Write aIn Col I Write a formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    plz reply urgent

  47. In Col I Write a formula to give In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    plz reply urgent

    1. Use V lookup function

  48. In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    In Col I Write a formula to give rank to student based on below table (Without using IF Condition)

    Marks Grades
    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

    =33 but less than 60 Pass
    >= 60 but less than 70 3rd Div
    >=70 but less than 80 2nd Div
    >=80 but less than 90 1st Div
    >=90 Distinction

  49. hi trying formula A=B,C=D,E=F,G=H THEN 10000 PLZ HELP

    1. hi hassan,

      Just try like this if you need through number format keep numbers in another sheet like this(1,2,3,....10000) give formula, and past below sells it will take automatically up N numbers

      =IF(1=2," TRUE "," FALS ")

  50. Dear Ms. Seventha,
    I am writing below mentioned formula i need a specific formula for counting for odd numbers i.e. 3,5,7,9 as well even figure. can you please help how to write this both condition in 1 formula for even and odd number for CEll No. L105)
    IF(C107="lock bottom",IF(AND(L105=3),(I105*L105)+(G105*L105)+(F105*2)+(H105*2),IF(AND(L105=5),(I105*L105)+(G105*L105)+(F105*3)+(H105*3),IF(AND(L105=7),(I105*L105)+(G105*L105)+(F105*4)+(H105*4),IF(AND(L105=9),(I105*L105)+(G105*L105)+(F105*5)+(H105*5

    Regards
    Mukesh

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