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 38. Total comments: 1256

  1. Hi mam, can you please help me with the formula to compare 3 rows details for eg.,
    A B C D
    54 54 54 Equal
    54 32 54 Not equal

    1. Hi Lavanya,

      Here you go:
      =IF(AND(A1=B1, A1=C1), "equal", "not equal")

  2. Hi,
    Please help me workout the following:
    Determine if value in cell A1 is equal to the value in cell B1, return the difference in values (whether it is positive or negative value).

    1. Hi Melik,

      If my understanding is correct, you need a simple calculation like =B1-A1

      Please correct me if you are looking for something different.

  3. Hi
    I am trying to set up a training data sheet, I have put the date ie todays date in A1, in another cell =IF(I7<TODAY()-335,TRUE,FALSE) I have entered this data. In theory when i enter a trained date within this time it goes green, but if I want it to turn amber with 30 days to go how would I input this data before it goes to red.
    I have input the data in to the conditional formatting as new rules but can not get it to change to amber when it has 30 days before it expires, can you help

    Thank you

    Craig

    1. Hi Craig,

      I am sorry I do not quite understand the condition. But we have an article that explains different aspects of Excel conditional formatting for dates. It includes a lot of examples on how to highlight dates within a certain date range. Hopefully you will find a solution there:
      How to conditionally format dates and time in Excel

  4. Hi, i am deepikababu. i am having five price of a similar product like 100, 150,125,115,110. which formula will use to find the minimum price of a product in to the next cell. please advice me. iam waiting for your reply.

    Thank you

    1. Dear = Min(100,125,115,110) then enter

    2. Hi Deepikababu,

      You should use the MIN function. For example =MIN(A1:A5) will return the minimal value in cells A1 through A5.

      1. Hi, thank you for your reply. i have one doubt , iam working on costing sheet.Actually for each product 5 prices i need. but in some products have 3 price, and some others have 5 price. i want to put one common formula to find the minimum price. if i put the min formula , zero or - came. so please help me to put one common formula.

        Thank you

        1. If you want to ignore blank cells, then enter:
          =MIN(IF(A1:A5="", "", A1:A5))

          To ignore zeros (0), use:
          =MIN(IF(A1:A5=0, "", A1:A5))

          Please note both are array formulas, so you have to press Ctrl+Shift+Enter to enter them.

          1. hi,
            thank you verymuch for your valuable reply. i tried it and get result. once again thank you

  5. Hi Svetlana,

    I want to change the numbers of one column. Like any number that is greater than 1 should be changed to 1. Can you please advise if there is any way to achieve that.

    Thanks

    1. Hi Jack,

      I don't think this can be done with a formula, because a formula can return something only into the cell where it is entered.

  6. Hi,

    Could you advice me, if we use the equal to formula with color changes.
    2=2 then "True" in green color otherwise "FALSE" in Red Color.

    Thanks..

  7. I am working on a very special project that has to deal with multiple dates where one of three date is missing intermittently. I am dealing with Assignment Date, Interim Status Date, and Current Status Date. The project requires me to show the number of days required from Assignment Date to Interim Date and Interim Date to Current Status Date. I am able to determine all the dates needed provided all three dates are populated in the spreadsheet. But, it appears that often Interim Status Date is missing. I need help in order to create one formula for the whole spreadsheet that will give me a result for number of days an employee needed to perform a particular job. Thank you,

  8. I have a large table and I need If CellH2 is Y then CellI2 equals the value of CellG2 or if N then Cell I2 is blank. I think I understand how to create the formula =IF(OR(H2,="Y",H2=""),"(G2)","")
    But the only thing appearin is (G2) in all of my cells regardless of Hcolumn value. And I don't want "G2" as text, I want the actual value of what is in cellG2 to appear.

    1. I was able to figure it out on my own. I was making it way too hard on myself. I was able to get it by using the insert function button!

      1. Hi Alicia,

        It's great that you have figured it out!

        The formula is much simpler indeed :)

        =IF(H2="Y",G2, IF(H2="N",""))

  9. @Svetlana Cheusheva, 2nd formula worked for me.. thanks

  10. I am working with what I think should be an easy conditional formula, but I can't figure it out. I have two sums arrived at using different methods. I want to check that the two sums equal each other, within a small margin of error for rounding. I want to use an IF scenario so I can get an "Error" message if the variance between the two sums is larger than 5 (for example).

    1. Hi Ndiya,

      You don't want the IF function in Excel conditional formatting. You can use a simple formula like: =ABS($B2-$A2)<=5

      Where A2 and B2 are the first cells with data in the columns you want to compare. The ABS function returns the absolute value of a number without its sign.

      For more info about Excel conditional formatting, please check out this article:
      Excel formulas for conditional formatting

  11. @Svetlana Cheusheva.. please also help me to check my formula and suggest where it wrong..

    1. Hi Raza,

      I am not sure I fully understand your formula. But the following part:
      LEFT(G20,1)=("H","K","A")

      Should read as follows:
      OR(LEFT(G20,1)="H",LEFT(G20,1)="K",LEFT(G20,1)="A")

  12. I need to use a greater or less than in a COUNTIF formula... how can I put it in?

  13. Hi, please help me on below formula, SME is not reflecting if i "F0421-B0001" change this into "H0421-B0001" and same as for "B0421-B0001" Telesales..

    F = Biz Dealer
    H,K,A = SME
    B = Telesales

    IF(LEFT(G20,1)="F","Biz Dealer",IF(LEFT(G20,1)=("H","K","A"),"SME",IF(G20=G22,"Outsource Telesales",IF(LEFT(G20,1)="B","Telesales","-"))))

  14. A B C
    10 8 2
    8 9 -1
    6 3 3
    4 11 -7
    2 12 -10
    I have numerical values in column A and B.In column C, I need defeerence of two column,I mean with out negative sign, can you please provide the formula?

    1. Hi Mariy,

      Try using the ABS function that returns the absolute value of a number:

      =ABS(A1-B1)

  15. Oh wait... I just tried =if($F11=$B$9) and it say $b$9 can't be used for whatever reason.

      1. Hi Svetlana....
        Pls help me if condition for good or bad if less than 50% , make ''Good'' if above or equal 70%, and excellent if above 70 and not more than 85%

  16. I got one for you.

    I'm trying to not waste cells and use use conditional formatting.

    conditional format if b1=0 format this cell this way. Excel really only wants to compare cells to cell rather than a cell value to a static value.

    I know I could hide a 0 in a cell someplace and have all the formulas reference it but I'd rather do it the right way.

  17. Hi,
    I have a row of data with past and future dates (and other text values) in columns AW:IX. I need to return the text in the column heading eg. "Actual date1" for the date that is the max of <=Today().
    Any ideas?

    Thanks

  18. Trying to create a formula that will add or substract another number depending on number size.
    Example: If cell a is greater than 50 add 1, greater than 300 add 1.5, greater than 1000 add 3, etc or if cell a is less than 50 subtract 1, less than 300 subtract 1.5, etc

    1. Hi Randy,

      In this case, you need to use nested IFs, for example:

      =IF(A1>50, A1+1, IF(A1>300, A1+1.5, IF(A1>1000, A1+3, IF(A1<50, A1-1, ""))))

  19. Hello

    I need to highlight the cells of a row, if the text in one of the cells is equal to one of the other cells.

    ex.

    DOMV ABGO MASR ABGO

    ^^ ABGO should be highlighted.

    DOMV ABGO MASR LABL

    ^^ none should be highlighted.

    Thanks

    1. Hi Andreas,
      need some calrifications
      1)"DOMV ABGO MASR ABGO" these all are located in same cell
      or located in 4 different cells
      2)Do you want to highlight the first "ABGO" or both ABGO s

  20. Need to place a temperature value in a cell if the corresponding time stamp for that temperature record is between two points of time during that 24 hour period. Records are every hour over 2 months.

    1. Hi Confused:

      Are you wanting to place a temperature value in a specific column of a temperature record that is between two points in time during a 24 hour period; or are you wanting one value for a range of temperature records for a given date ?

      For example, if your two points of time covers three hours; do you want to place three different temperatures in three different cells or one temperature (an average temperature covering the three hours) in one cell ?

  21. Hello,

    I am stuck trying to calculate this problem. 5KM or more and less than 15KM, the cost will be 5dollars...
    thanks in advance

  22. I have a query about excel formulae

    if cell b1- cell a1 is less then 180 days than multiply by 18% and more tnen 180 days but less then 365 days than multiply by 24% and more than 365 days than multiply by 30%

    1. Hi Yash,
      You can try the following nested IF formula:

      ASSUMPTIONS:
      ============
      -- Using are using a Calendar Year (365)
      -- Cells A1 & B1 are formated as a date field
      -- Cell C1 contains a base amount in which to apply the percent (18%,24%,30%)
      -- Cell D1 contains the nested if formula

      Try this:
      ==========
      =IF(B1-A1=180),((B1-A1)<=365)),C1*0.24,C1*0.3))

      Quick explanation:
      ====================
      Condition 1 tested by ... B1-A1=180),((B1-A1)<=365))
      Condition 3 is defaulted ...

      Hope this helps.

  23. I have two tables, Tab1 is of the ages of students, Tab2 is of their test results. i want to find the average score of students between ages 18- 20. Please help with a formula.

    1. Hi yuvan you can try this formula to find solution

      =AVERAGEIFS(A:A,B:B,">=18",B:B,"<=20")

    2. Hi yuvan

      you can find the result by using this formula

      =AVERAGEIFS(A:A,B:B,">=18",B:B,"<=20")

      If you need any help please let me know

  24. I am trying to compare the values in two cells, say A1 and A2, and enter a value in another cell as below:
    For A1>A2 I want 2
    For A1=A2 I want 1
    For A1<A2 I Want 0
    What formula can I use?

    Any help would be appreciated.
    Regards,

    1. Hi Jim H,
      You can try the following nested IF function:

      =IF(A1 < A2,0,IF(A1 = A2,1,2))

      This nested IF formula explicitly test for two conditions ("less than" and "equal to")and defaults to the greater than condition; if neither of the two explicit conditions are found true.

      Hopes this helps.

  25. How to make this thing happen?

    Settled word will appear in a cell link to another cell with 1 or 100% content?

    i.e.:

    - Cell A1 contains formula: =D4
    - Cell D4 contains number 1 or 100%
    - Cell A1 will display: Settled

    Is it possible?

    Please help.

    1. Change the formula in Cell A1 to:
      =IF(D4=1,"Settled",D4)

  26. WHAT IS THE bigger than AND between FORMULA
    FOR EX.
    510
    560
    530
    532
    550
    520
    512
    MY QUESTION
    How many numbers in column A are between 520 and 540 inclusive
    And How many numbers in this list are bigger than 500

    1. I have a question, so i have 5 cells, 4 of the cells have different values in them (dollar amounts) and one is a blank cell, I need that blank cell to look at each dollar amount in the other 4 cells, and choose the large dollar amount to reside in that blank cell.

      i can do this with if formulas, but i have to create several more columns in order for this to work. is there a way to do this just with imputing a formula in the one blank cell? i need help asap!! :)

      1. Hi Tim,

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

        1. consider 5% is equal to 100% what formula should use ????

          5% is total marks of my employee and it is part of 100% but I want to give him marks in percentage

    2. Hi Ganesh,

      Try these formulas

      Q1: =COUNTIFS(A1:A7,">=520",A1:A7,"500")

      1. The system messed up my reply. Should be...

        Q1: =COUNTIFS(A1:A7,">=520",A1:A7,"<=540")

      2. Q2: COUNTIF(A1:A7,">500")

  27. can anyone help a simple addition case? i want to input number 1-10 on column j1 each number will register how many times did input the same number let say column a1 is the 1 and column b1 is how many times did i input the 1 on column j1 and same with a2 and b2 all the way a10 to b10.

    1. Hi rockmetal,

      you can try this formula
      =COUNTIF(J:J,$J1)

      if you are not getting your requirement let me know

  28. What I want is :
    If a1=2 and =3 return f3 in one cell?
    Thank you

  29. If text in cell equal than return next text which is not equal to this text after first result from same row and sheet using if and vlookup ?

  30. What is the formula for the following:
    I need the cell to count any number between 30 and 43, including 30 and 43.

      1. thanks bro, your formula helped me.

  31. how to check if Cell A1:A10 is equal to B1:B10 ?
    =IF(A1:A10=B1:B10,"TRUE","FALSE") and =IF((A1:A10)=(B1:B10),"TRUE","FALSE") is not working. help pls. thank you,

    1. Hello okin,

      You need to use an array formula, something like this:
      =IF(SUM(--(A1:A10=B1:B10))=ROWS(A1:A10),"TRUE","FALSE")

      Don't forget to press Ctrl+Shift+Enter instead of Enter to complete it.

      1. can I ask why?!. why I need to press Ctrl+Shift+Enter instead of Enter to complete it.

  32. I need help please.
    I have 5 different numbers and I need TRUE if those orders are exact or a FALSE if they don't match.

    For example, I want the if function to search cell E2 for numbers ( 1 or 2 or 3 or 4 or 5) and if in fact cell E2 is 5 then, it should say TRUE but if its 6 it should say FALSE

    1. Hi Axel,

      You can try the following IF formula with OR statement:

      =IF(OR(E2=1, E2=2, E2=3, E2=4, E2=5), TRUE, FALSE)

  33. Trying to find how many dates in a list are between 1year and 3 years old. And then between 3years and 5years. It seems like it should be fairly simple and straightforward but I'm just not getting a correct count no matter what I try.

    Thank you in advance!

  34. I want to include an or statement in this formula below so that both No and NA return the true value as 0

    =IF((Checklist!$F$60="No"),0,6)

    1. Hello Ross,

      Here you go:

      =IF(OR(CheckList!$F$60="No", CheckList!$F$60="NA"),0,6)

  35. Hi,
    I have a problem for condition my cell data is date I need to know the date is expired or not if it is less than or greater

    1. Hi Sajeer,

      You can use the TODAY() function to compare the dates with the current date.

      For example, the formula =IF(A1<TODAY(), "Expired", "") will mark all past dates (less than today) as expired.

      If you want something different, please explain in more detail.

  36. I need a1 greater than or less than or equal to A1 if it possible

  37. Hello Kinjal,

    Here you go:

    =IF(AND(A1=B1, C1="X"), E1-F1, "")

    1. THANK YOU <3 the AND part was what I exactly needed.

    2. Hi Svetlana,

      I am trying to get an excel formula to determine if a given number in a cell should be counted in one of three categories. For example:

      Category 1 is >12499
      Category 2 is >7499 but 2999 but <7500

      So I have a column of numbers that need to be put in one of the categories above. For example:

      Column A
      12800
      13000
      10000
      10000
      10000
      7000
      7000
      2500

      The answer I am looking for is as follows:

      Category 1 = 25800
      Category 2 = 30000
      Category 3 = 14000

      I would be so grateful if you can you help me with the correct formula to use. Thank you.

      1. Hi Svetlana
        first of I must say u r amazing and your article is awesome v good collection, actually my query is I want my table below
        Name id
        IISL 1
        IISL 2
        IIDL 3
        IIDL 4
        IIKL 45
        IIKL 5
        IIKL 7

        as
        Name id
        IISL 1,2
        IIDL 3,4
        IIKL 45,5,7

        thanks in advance

        1. Thank you for your kind words, Salim.

          I'd recommend using our Combine Rows Wizard. It will enable you to achieve the desired result with a couple of clicks. The above link contains the detailed instructions and download link (free trial).

      2. So sorry but for some reason the categories did not list correctly again so I will show them in a different way here!1

        Category 1 is >12499 and Category 2 is >7499 but 2999 but <7500.

        1. Can anyone help me

          I want to capture date for negative value where it is equal to its positive value.

          For example

          2000 12 sep
          5000
          4000
          6000
          -6000
          -2000. 21 sep
          -5000
          -4000
          Now i want this 21 sep date in front of 13 sep as value against 21 sep is negative value of 2000

        2. Sorry again but again for some reason the categories are not printing properly so I will show them again differently:

          Category 1 is 12500 and above, Category 2 is 7500 up to 12499 and
          Category 3 is 3000 up to 7499. I hope it came through this time. You can see from my first two questions what the rest of the question is. Thanks again!!

          1. Hi Douglas,

            I think you can accomplish your task by using the following excel formulas:

            For Category 1 (Greater or equal to 12500) use:
            =SUMPRODUCT(--(A:A>12499),A:A)

            For Category 2 (Greater or equal to 7500, but less than 12500) use:
            =SUMPRODUCT(--(A:A>7499),--(A:A2999),--(A:A<7500),A:A)

            These formulas will add all the data as well. The "--" identifies a function as Boolean (True or False), so that if it doesn't meet this criteria, it multiples the other values by 0, which in turn makes the total 0. Hope that makes sense and helps. I'm hoping you are familiar with the Sumproduct formula.

            Good Luck!

            1. Hi Douglas,
              thanks for your help, i have learned a new formula from your response

              before that i use sum if function to find this type of requirement
              like
              =SUMIF(A:A,">12500")
              =SUMIFS(A:A,A:A,">7500",A:A,"<12499")
              =SUMIF(A:A,"<7500")

              can you please explain me why we used double negatives(--)here while using this function

              Thanks in advance

              1. I want to change values in data example all values less than <0.20 with "-ve" symbol and all values graeter than <0.21 with "+ve" symbols
                how ? please help me

      3. The categories in the previous question did not list properly here is the corrected version!

        Hi Svetlana,

        I am trying to get an excel formula to determine if a given number in a cell should be counted in one of three categories. For example:

        Category 1 is >12499

        Category 2 is >7499 but 2999 but < 7500

        So I have a column of numbers that need to be put in one of the categories above. For example:

        Column A
        12800
        13000
        10000
        10000
        10000
        7000
        7000
        2500

        The answer I am looking for is as follows:

        Category 1 = 25800
        Category 2 = 30000
        Category 3 = 14000

        I would be so grateful if you can you help me with the correct formula to use. Thank you.

  38. I Have 1 query in excel formula:
    if Column A is equal to column B and Columnc C contain X then result should be E-F

    1. Hi Svetland, I need a formula for 0-4999 pay 35% for primary and 15% for secondary. 5000-24999 pay 10% primary and 15% secondary. 25000 and above pay 5% primary and 20% secondary. If secondary reaches 25000 pay 5% primary and 0% secondary. Please help

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