Comments on: Excel logical operators: equal to, not equal to, greater than, less than

Many tasks you perform in Excel involve comparing data in different cells. For this, Microsoft Excel provides six logical operators, which are also called comparison operators. This tutorial aims to help you understand the insight of Excel logical operators and write the most efficient formulas for your data analysis. Continue reading →

Comments page 32. Total comments: 1256

  1. I have 10 numbers(positive/Negative) in cells A1 to J1.
    I want to determine whether this numbers from A1 to J1 are having growth/fall.

    For e.g.
    1,2,3,4,5,6,7,8,9,10 are having growth.
    Similarly,
    10,9,8,7,6,5,4,3,2,1 are having fall.

    Both the cases are acceptable.("YES")

    But,

    4,5,6,7,5,4 are not having continuous growth.("NO")

    I need a formula for this.

    What i have managed to do so far.
    =IF(OR(AND(D5>E5,E5>F5,F5>G5,G5>H5,H5>I5,I5>J5,J5>K5,K5>L5,L5>M5,M5>N5),AND(D5<E5,E5<F5,F5<G5,G5<H5,H5<I5,I5<J5,J5<K5,K5<L5,L5<M5,M5<N5)),"YES","NO")

    But this doesn't work if any of the cells are blank.

  2. I want to the if function to test value in too different cells, and display which is higher and if equal should display any of the value

    Correct answer would be appreciated.

    Thanks

    1. Hi Zack,

      You can use the MAX function, for example =MAX(A1:C1) or =MAX(A1, C1, E1)

  3. Hi,

    I have two cells like below.
    Category Wages
    O/L TAILOR -I 280
    F/L TAILOR -I 305
    HELPER 175
    CUTTING HELPER 220

    i need a automatic for wages cell , (e.g If i entered O/L TAILOR IN CATEGORY CELL AND WAGES SHOULD BE CHANGE AUTOMATIC VALUE OF 280)Some one help me out

  4. Hi
    I've list of numbers in a column, I want to find out the numbers which are equal to, 10 grades larger or 10 grades lesser than a number in the column.
    Thank you

  5. Hi,

    my quires

    (>=15% -25points)(>12%0%15%,"25",IF(K2=15%,"25",IF(K2>12%,"15",IF(K20%,"5",IF(K2<12%,"5"))))))

    first two logic will come but last logic not yet come.

    Regards

    SP

  6. Hi,

    my quires

    >=15% -25points
    >12%0%15%,"25",IF(K2=15%,"25",IF(K2>12%,"15",IF(K20%,"5",IF(K2<12%,"5"))))))

    first two logic will come but last logic not put 5 points

    Reagrds

    SP

  7. Hi Svetlana,

    I am struggling with the following challange.

    I would like to deduct in 00:30 hrs if the time difference between 2 cells is more than 6 hours.

    I have A1 12:00, B1 18:30, C1 = difference A1 and B1, BUT if the difference = more than 6 hrs I want C1 to deduct 00:30

    Thank you very much for your help.

    Regards, Bram

    1. Hi Bram,

      Try the following formula:
      =IF(B1-A1>TIME(6,0,0), B1-TIME(0,30,0), B1-A1)

      1. Hi Svetlana, Thank you for your prompt reply.
        I am very happy! It put me on the right track.

        I solved it by first placing the difference in cell C1 and then placing the formula =IF(C1>TIME(6;0;0); C1-TIME(0;30;0); C1) in cell D1

        Very best regards,

        Bram

  8. Need to insert today date on B1 if A1 contains a specific value or characters

    need formula, pls help

    1. Hi Lokesh,

      You can use a formula similar to this:

      =IF(A1="text", TODAY(), "")

      Remember to apply the Date format to B1.

  9. I'm trying to create a cell I want it to read 25% of earned income. However, if expenses exceed the 25% of earned income, I want the cell to read 0. Can I do this?

    1. Never mind. I did it! I think.
      =IF(F4<(B4*0.25),B4*0.25,0)

  10. Hi Svetlana,

    If A1 amount has a range and the results in A2 with different types.

    0-50000 = Type A
    50000-200000 = Type B
    200000-700000 = Type C

  11. Hi Svetlana,

    What if I wanted the output cell to be equal to 1 only if the copied cell is greater than 500, and 0 only if the copied cell is less than 500?

    i.e. X=1 IF X > 500, 0 IF X < 500

    Thank you for your assistance :)

    1. Hi Clyde,

      You can use a formula similar to this:

      =IF(A1>500, 1, 0)

      1. Ah perfect, thank you very much...way more simpler than I expected it to be :)

  12. if any value like 19 or less then it then it should be count 19 or same but if it is above 20 then it should count 20.i want formula for this query?

    1. Hi Chirag,

      You can use a formula similar to this:
      =IF(A1<=19, 19, 20)

      1. I have a spreadsheet that I like to skip the column that have a grand Total of "0"

        So if Column G5 grand total is zero, skip G5 and look for G6
        if G6 is zero look for G7.

        If G7 value is 1 then insert the value A1

        Basically I like to keep only the values 1 and above in the spreadsheet

        Thank you

        1. Hi Jaison,

          And what do we do if G7 is 0 or >1?

          1. Thanks for the reply

            Once the value is >1 on the pivot, we will use that value.

            1. Jaison,

              If my understanding of the task is correct you can use the following formula:

              =IF(G5>0, G5, IF(G6>0, G6, IF(G7>0, G7, A1)))

  13. Hi there:

    I am trying to write a formula that says

    "If (reference to a cell on another sheet) is >=15, then enter 15000 in the cell if not, then enter 0."
    This is the formula I have entered that does not seem to be working..
    =IF('Visits Schools'!C38:C40>="15",15000,0)
    What's wrong with the formula?

    1. Kacey,

      Double quotes are not needed for numbers and each cell should be referenced individually, for example:
      =IF('Visits Schools'!C38>=15, 15000, 0)

      or

      =IF(AND('Visits Schools'!C38>=15,'Visits Schools'!C39>=15, 'Visits Schools'!C39>=15), 15000, 0)

  14. I have a spreadsheet that I like to skip the column that have a grand Total of "0"

    So if Column G5 grand total is zero, skip G5 and look for G6
    if G6 is zero look for G7.

    If G7 value is 1 then insert the value A1

    Basically I like to keep only the values 1 and above in the spreadsheet

    Thank you

    1. Any help would be much appreciated

  15. How do I write a formula for, If G2>H2, return 0, if h2<G2, subtract h2-g2, for answer.

    Help!!

    1. Hi Wanda,

      Here you go: =IF(G2>H2, 0, H2-G2)

  16. Hello!

    I have a spreadsheet which takes a ton of input from various user created lists. One of my cells is a simplified display, but properly doing the code is turning out to be difficult.

    Currently I have:
    =IF(ISNUMBER(FIND(Facility,Each1)),"↑BCR",IF(AND(ISNUMBER(FIND(Facility,Each2)),NOT(ISNUMBER(FIND(Facility,Each1)))),"↓BCR",""))&IF(ISNUMBER(FIND(Facility,Each3))," ↑BM",IF(ISNUMBER(FIND(Facility,Each4))," ↓BM",IF(ISNUMBER(FIND(Facility,Each5))," ↓BMx2",IF(ISNUMBER(FIND(Facility,Each6))," ↓BMx4",""))))

    Basically, I have two things I'm measuring: BCR and BM.
    I have a list of facilities.
    I have 6 lists, "Each#".

    Each1 = ↑BCR × integer
    Each2 = ↓BCR × integer, if not on Each1
    Each3 = ↑BM × integer
    Each4 = ↓BM × integer
    Each5 = ↓BM × 2 × integer
    Each6 = ↓BM × 4 × integer

    The formula recognizes that if a facility is not on Each3, Each4, Each5, or Each6 that the value " " is reported back. I would like for it to do similar with Each1 and Each2. Specifically, if a facility is on both Each1 and Each2, I'd like it to return " ".

    In general, the Each2 function I created seems to be broken.

    Help please?

    1. I'm still holding out that you'll help me! I really cannot figure this one out on me own.

  17. I have one query:

    I need to have a formula which can determine if it passed or failed according to its type, sample:

    A: Normal/Abnormal
    B:date and time request was received
    C:date and time request was approved
    D:C-B = total hours of the time when it was approved
    E:PASS / FAIL

    FAIL: if request is normal and its approval time exceeds 72 hours
    PASS: if request is normal and its approval time is within 72 hours
    FAIL: if request is abnormal and its approval time is more than 24 hours
    PASS: if request if abnormal and its approval time is within 24 hours

  18. Hi,
    i am facing difficulties in allocating Grades to the students, please help me to write a formula in the M.S.Excel work sheet. Here is an example:

    Grading standard:
    below or equal to 39 is fail,
    40 to 49= D
    50 to 59=C
    60 to 69=B
    70 to 79=A
    80 to 89=A+
    90 and above=A++
    Students Name %obtained Grade
    Ajma 85
    Saeed 63
    Shahzad 55
    Latif 90
    Rashid 74
    Akhtar 80
    Aslam 49
    Ahmad 68

  19. Hi, is there a way to add 2 colums but not have it go over 8? I want to add f8 and f9 but I need only 8 in I9 and the rest to go in j9. Can you please help me?

  20. Hey, need help on creating formula for two numbers. Here is the current:

    If(AND(K14>=3000,M14>=10%,K14<=3000,M14<=10%),"Yes"," ")

    For some reason it is still returning blank when I put in a negative number less than -3000

    1. Hi Excelio,

      This happens because all numbers match your logical test :)

      K14>=3000 and K14<=3000

      M14>=10% and M14<=10%

      If you can specify under exactly what conditions you want to return "yes", I will try to help.

      1. After reading your comment I realized I needed to put an OR before the AND. Thank you!

        1. I also realized that I didn't put negatives in my question in front of the second 3000 and 10%. That might have solved a lot of confusion.

  21. Help please!
    I need help with greater than and less than?
    I have a solid number that does not change however week by week that data entered could be more than or less than the solid number??? I need it to return a positive or negative difference(number)?
    The formula Ive tried if =IF(B1B6,(B1-B6)) it works but only one way? I have tried rules,
    thanks im overwhelmed :)

  22. Please help!
    I want to mark a cell "pass" when the score is equal to or above 14, and "fail" when the score is lower than 14.

    I cannot figue it out :(

    1. Hi Magda,

      You can use a formula similar to this:

      =IF(A1>=14, "pass", "fail")

  23. Hi SWARUP,

    You can use a nested IF formula similar to this:

    =IF(A1=421, 420, IF(A1=429, 430, IF(A1=431, 440, IF(A1=443, 450, ""))))

  24. Pls help me in getting the formula that

    if A1=421 than in A2 it should reflect 430
    if A1=429 than in A2 it should reflect 430
    if A1=431 than in A2 it should reflect 440
    if A1=443 than in A2 it should reflect 450

  25. Original 12/15/2014, New Date 05/15/2015, so the date that i want in my revise column is the new date, but there will be multiple new dates that supersede the previous new date

    1. Mic,

      I think your original formula =IF(G87>E87,G87,E87) is correct. It will always return the more recent of the two dates, no matter how many times the dates in the specified cells are updated.

  26. disregard the last part

  27. Sorry for the confusion, I'm making a spread sheet with a original date with multiple revisions, I want to be able to put in a date and let excel determine if its the present date, In the Revisions through column will have multiple dates for example:

    DATE Rev. up to With Revisions Through
    12/15/14 05/15/15 05/15/15=IF(G87>E87,G87,E87)

  28. Hi,

    If i wanted a previous date to supersede a early date in multiple cells how would i format that, I have the following:=IF(G87>E87,G87,E87) but cant figure how to format multiple dates that will supersede the previous update date, if i have a newly updated date.

    =IF(05/15/15>12/15/14,G87,E87)

    Thank you

    1. Hi Mic,

      Sorry, I am not sure I fully understand the question. If you are asking how to format the returned date the way you want, you simply apply the desired date format to the cell containing your formula, as demonstrated in the following tutorial:
      https://www.ablebits.com/office-addins-blog/change-date-format-excel/

      If you are asking about something different, please clarify.

  29. Hi, kindly how to write this function:

    IF A1>=0.3 then "Good"
    IF A10.1 then "Fair"
    IF A1<=0.1 then "Poor"

    Thank you

    1. Hi Meedo,

      Here you go:
      =IF(A1>0.3, "Good", IF(A1>0.1, "Fair", "Poor"))

  30. if the sales crossed the required amount means then addition it must automatically go to next cell for that what i have to do (Eg: Batch 1 =1500 nos, Batch 2 = 2000 ok if the sales crossed 1500 means it has to go next cell like batch 2)

  31. hi,
    Vodafone Airtel Idea Market Leader
    17% 15% 18% Idea

    i would like to knwo th formula.. if in all the three values, which ever is greater like idea is 18% in all the three, so the answe will automtically comes idea. Greater values shows the text. plz reply

  32. Hi i know how to use this logical operators my problem is

    i want the result is in number value

    for Example in excel:
    A B C D
    1 Amount Days 31-120 121-180
    2 20,000 175 =A2= ans. True
    3 10,000 119 =A3= ans. False

    I want answer to be like:

    A B C D
    1 Amount Days 31-120 121-180
    2 20,000 175 =A2= ans. 20,000
    3 10,000 119 =A3= ans. 0

    Hoping you could find the solution

  33. here is what I came up with accidently, its working but I am not sure where I got the "False" from, what does that mean?

    =VLOOKUP(B4,'SUBWAY MOVEMENT'!$A:$H,8,FALSE)

  34. Thank you so much Svetlana for your help thus far, your work on this forum have been amazing.

    1. Hi Tim,

      Thank yo so much for your kind words.

      It's difficult to suggest a proper formula without seeing your data, but I think VLOOKUP is the right function for your task.

  35. Hi Svetlana,

    if I have a spreadsheet with values in comumb one, but I have another tab that has information from 1-1000 with data, rather then looking at 1000 cells to find the matching data for one cell in columb one, how can I set up a formula to search all 1000 cells on the separate tab, to find the matching value, then add the requested information into the selected cells? make sense? I am having trouble trying to articulate this.

  36. Hi, I need a formula for the following:

    If today() is greater than a date in a cell by 4 days, return a result

    thanks for your support.

    1. Hi Irshad,

      Here you go:

      =IF(TODAY()- A1>4, value_if_true, "")

      1. Dear Svetlana,
        Thanks a lot.It works great when I replace the value_if_true with a number, e.g =IF(TODAY()-A954>4,1,2).

        But how do I replace the 1,2 by text?
        I did this, =IF(TODAY()-A954>4,TEST,TEST FAILED), but it doesnt return the text. I get #NAME? as result.

        Sorry to be a pain and thanks again for your support.

        1. Hi Irshad,

          Just enclose the text in double quotes, like this:
          =IF(TODAY()-A954>4,"TEST","TEST FAILED")

          1. Hi Svetlana,

            Thanks loads for your help and support!:)

            Best Regards

  37. Hi

    I am trying to colour code using conditional formatting. My example is:

    Student A has a target grade of "B"

    I want it to be:

    Hits target e.g. B = Light Green
    Above target e.g. A = Dark Green
    Below target e.g. C = Orange
    2 below target e.g. D = Red

    Can this be done with conditional formatting and if so, how?

    Thanks

    Gareth

  38. im trying to figure out a formula? if cell a1 is less than 13 the value in b1 is 26 if its greater than 13 the value is 24 any help would be great!!

    1. Hi Steve,

      And what if A1=13?

      Anyway, you can use the following formula:
      =IF(A1<13, 26, 24)

      And you can replace A1<13 with A1<=13, if needed.

  39. Svetlana, you've been a huge help on this forum. Thank you so much for all you've done.

    I'm trying to draft a formula to find the number of items that were created on or after a certain date (Held in Sheet1, column N) but not resolved before the date held in Sheet1 column Q. A list of every day of the year is listed in column B, with B3 starting at 1/1/2015, B4 at 1/2/2015, etc.

    e.g. We're needing to track the number of bugs that are in an active state each day, as well as track the number of bugs in a resolved state every day. Column N holds the created date of every bug and Column Q holds the resolved date of every bug. I know how to do a countif to determine if a bug's creation date was the same as the date in column B, but I can't figure out how to make it check if it's still open.

    1. Hi Aaron,

      Thank you for your kind words.

      Does an "open case" mean no date in column Q or the date in column Q is less than today's date?

      If the former, you can check column Q for empty cells, e.g. =COUNTIF(Q3:Q100,"<>"&"")

      If the latter, you can compare a date in column Q with today's date:
      =COUNTIF(Q3:Q100,"<"&TODAY()) You can find more formula examples in COUNTIF in Excel - count if not blank, greater than, duplicate or unique.

      1. Svetlana, thank you for the help. No, it means that the date each case is opened is in column N (N3:N100000)and the resolved dates are in column (Q3:Q100000). I'm needing to find for every day since January 1st how many cases are open on each day and how many are resolved on each day, so we can see how quickly we're reducing the case load. So I need to find, for each date (listed Sheet3!B3:B1000) if a bug was created on before that date, but not resolved (because if it's created January 2nd but not resolved until January 31st it's still active from Jan 2-Jan30), or if it was created on or after that date and also resolved on or after that date.

  40. =IF(G26<=A14,A14-G26,"MET") I'm trying to get my formula to say MET if its = to. But it only reads Mets if I'm a penny or more over. what am I doing wrong?

    1. Hi Walter,

      Since you are using the "less than or equal to" operator in the logical test, the formula reruns the difference A14-G26 if G26=A14. If you want it to return "met" in this case, replace "<=" with "<" like this: =IF(G26<14, A14-G26,"MET")

  41. i want to calculate incentives and i need to find a value such as
    if A1>B1 minus with D1 if the result is equal/less than D1 come back as 0, and if result is greater than D1 minus with D1 and come back as positive and multiply by 150.

  42. E2 = 23% and M2=-23% I need a formula to highlight a tolerance. If M2 is +5% over E2 it's orange, if M2 is +10% over E2 it's green. If M2 is equal to E2 it's just clear. If M2 is -1% or less it's RED.

    Can anyone help out, it's driving me nuts

    1. Upon a second thought, that actually makes no difference :)

      You can create 3 conditional formatting rules, say for M2:M1000, with the following percent change formulas:

      Orange:=AND(($M2-$E2)/$E2>=5%, ($M2-$E2)/$E2<10%)

      Green:=($M2-$E2)/$E2>=10%

      Red: =($M2-$E2)/$E2<=-1%

      1. THANKS! worked perfectly

    2. Hi Edd,

      Do you have amounts of percentages in E2 and M2?

  43. Svetlana, Question, is it possible to add formulas to a tab on a spreadsheet?

  44. how do i take a blank cell, and have that cell equal to the large dollar amount of 4 other cells? so if i have 4 cells with different dollar amounts, i want the blank cell to know to choose the larger amount of the 4 cells to be the value? how do i do this?

    1. Hi Tim,

      You can use the MAX function similar to this:
      =MAX(A1:A4)

      1. Ahh, did not think about that MAX formula. can I call myself an excel wiz... Thank you so much Svetlana

  45. I want to link a document so that if it is greater than today it =A4 and if it is less than today it =A3. Say the document with A3 and A4 is called pizza. What would this formula look like?

  46. I am looking for a little help with excel please.

    I have two columns (ex: E and F) in which I need to find all rows in which column E is greater than F.

    Is there a formula for finding this?

    ANY help would be greatly appreciated!

    Thank you

    1. Hi Maria,

      To count such cells, you can use an array formula similar to this:

      =SUM(--(E1:E100>F1:F100))

      Remember to press Ctrl + Shift + Enter to enter the array formula correctly.

      To highlight such rows, you can create a conditional formatting rule with the formula =$E2>$F2 where row 2 is the top-most row with data.

  47. I found a SUMPRODUCT for a cell (E8), the Total Cost, using two separate arrays. One that I identified by cell range numbers (B8:D8) the other I identified using the cell range by name (Cost, which was cells B3:D3). I got my numbers for column E just fine, but now I am looking to write a function for the column F that calculates the shipping. I want to find the shipping for each Total Cost at 2%, unless 2% of that total cost is $10 or greater, then I want it to insert $10. I wrote the following function: =IF(E8*0.02>9.99,E8*0.02,10), but for a total cost of $457.00 it's returning $10. But $457*.02=$9.14, which is what it should be putting in the box instead of $10. Where am I going wrong?

    1. NVM, I had a greater than sign instead of a less than sign in the equation. So stupid!

  48. Hi Svetlana,

    I'm looking for a formula where if the value of a certain cell is less than or equal to $50 then the next cell over produces the number 1. If the value of the same cell is less than or equal to $100 the next cell over produces the number 2 and so on and so forth.
    Example:
    If the value of K3 is less than or equal to $50 than M3= 1.
    If the value of K3 is less than or equal to $100 than M3=2.
    If the value of K3 is less than or equal to $150 than M3=3.
    If the value of K3 is less than or equal to $200 than M3=4.
    I would need the formula to continue until about $600.

    1. Hi Dan ,

      You can use nested IF functions like this:

      =IF(K3<=50, 1, IF(K3<=100, 2, IF(K3<=150, 3, IF(K3<=200, 4, ""))))

      1. Thank you very much!

  49. Hi Svetlana,

    I'll looking for a formula that will give me the greater of either 20% of a nummber or $5,000 ie $10,000 *20% = $2,000 but $5,000 is greater

  50. Hi Svetlana, I've been looking for a formula to compare two figures and to divide accordingly. The formula is to create a spread sheet in order to calculate incomes.

    If A1 is more than B1, then average B1 by 12. if A1 is less than B1, then add A1 and B1 and divide by 24.

    1. Hi Carlos,

      I am not sure I understand what "average B1 by 12" is :) Anyway, you can add a proper calculation instead of ... in the below formula:

      =IF(A1>B1, ..., IF(A1<B1, (A1+B1)/24, ""))

      Also, please note that the formula will return an empty string if A1=B1.

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