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

  1. Hi need help with a formula.

    I'm trying to figure out how to make this happen and hope I can explain correctly...I want the formula to look at several cells and say if this cell has 0.00 then look at the cell before it, and if that cell also has 0.00 then look at the cell before it, and so on for several cells. However if any of them are greater than 0.00 then input that first cell amount and don't look at any further cells.

    1. Hi Janet,

      What you need is nested IF functions similar to this:

      =IF(A1<>0,A1,IF(A2<>0,A2,IF(A3<>0,A3,IF(A4<>0,A4,""))))

  2. Hello,

    I'm working on an inventory and order guide for a restaurant. It's set up so it totals the amount of cases ordered for the week and I'd like to set a condition that if we order 16 cases or more we pay $10.00 per case and if we order less than 16 we pay $13.00 per case. It sure would be convenient if I could have this automatically calculated.. :)

    Any ideas? Thank you!

    1. Hi Tito,

      You can do this using an IF formula. For example:

      =IF(A1>=16, A1*10, A1*13)

      Where A1 is the amount of cases.

  3. I am trying to have date cell turn colors when over 30 days unless next cell has date in it?

  4. Eg, I got a starting time at 9am and an ending time at 11am and would like to condition that if the number of hours is less than 4 hrs, it will give me 4hr and if more than 4 hours, it should give me 8 hrs. Can anyone help? greatly appreciate any help as I tried all means and unable to solve this. A thousand thanks

  5. I am trying to create the following condition:

    If there are any Adjust Hours Listed, then the

    Adjust Pay = SM pay - the calculated EE Med LOA pay - MAT LOA pay. If there are no Adjust Hrs listed, than the Adjust Pay cell will be blank.

    SM Pay Adj Hrs EE Med LOA Pay MAT LOA Pay Adjust Pay
    $4,1791.17 46.67 0.00 1,928.85 2,250.32

    Is there a formula for this?

    Thank you.

  6. Hi, here is the thing, i have a list of items and i build them, but i need to know how many of those items are part of the requisition and how many are not, lets say i have to build 250 cars at $10 each i aready have built 240, then i build 10.... there im ok but if instead i build 25 i have to know in one cell how many of those are within my requsition (10) and how many are out of budget (15)

    thxs.

  7. Currently we have a formula of =IF(P584-P586<100;"RIGHT";"WRONG"). The idea of this formula is to find the difference between the field that has a sum in and the other field which is the posting in our accounting system. But the difference is okay as long as it is no more than 100.

    This formula covers one angel of the working but I would also need to do IF(P586-P584<100;"RIGHT";"WRONG") as well to ensure the differences are located from both angels.

    How would I be able to combine those two formulas for it to work?

  8. Hi,
    I have a query regarding a formula.
    I want to have the following but I don't know how to put that into an excel formula:

    If b20 = greater or equal to g31 I want it to say accept.

    I hope someone can help me with this!

    Thank you!!

    1. Hi William,

      Try the following IF formula:

      =IF(B20>=G31, "accept", "")

      1. Hi Svetlana,

        Thank you for your reply, but unfortunatly it is not working..
        Do you maybe have another idea?

        Thank you!

        1. William,

          The formula is so simple that it simply cannot not work :)

          Please have a look at this example (the formula is in A1). If you want something different, please clarify.

          1. Great, I got it!

            Thank you very much!

  9. If cell A is less than or equal to cell B and less than "2" than its true. Example:

    Time to Close (Days) SLA Business Days Met SLA
    1.3 1 FALSE

    If time to close is less than 2 days I want to "Met SLA" to be True.

    I know this much of the formula to get my true or false statement (=I2>=H2) but can't find how to add the less than 2 without creating another column.

    1. Hi Ash,

      What you need is the AND statement like this:

      =AND(I2>=H2, H2<2)

      1. OMG thank you so much!!! I was about to pull all my hair out!! LOL You're awesome!!

  10. Hi Svetlana,

    Would you be able to help out here? The beginning of the formula works, the rest does not.

    =IF(D4>=2.51,3,IF(2.51>D4>=2.01,2.5,IF(2.01>D4>=1.51,2,IF(1.51>D4>=1.01,1.5,IF(1.01>D4>=0.51,1,0)))))

    The logic behind it is as follows:

    If value in cell D4 is bigger than 2.51 round to 3
    If the value is between 2.01 - 2.51 round to 2.5 and so on

    Many thanks in advance for your help!

    1. please can tell me the Sum formula of category wise Die. 1 to 40 is Window and 41 to 43 is Door then Die .44 is Window how can i apply logical formula to identify Door and window by entering Die Number??

      1. Sorry, I do not fully understand the task.
        As it's currently written, it's hard to tell exactly what you're asking.

    2. Hi Kasia,

      You don't want expressions such as 2.51>D4 and the like :)

      =IF(D4>=2.51,3,IF(D4>=2.01,2.5,IF(D4>=1.51,2,IF(D4>=1.01,1.5,IF(D4>=0.51,1,0)))))

  11. I need a formula to do the following in cell J6

    If Cell J46*0.03 is greater than J26 then J6 is = to J26 if not it is equal to J46*0.03

    1. Hi Stephanie,

      Here's a formula for cell J6:
      =IF(J46*0.03>J26, J26, J46*0.03)

  12. I need a formula to do the following:
    if cell A1 is >0 then return value in cell A6 but if cell A1 and cell B1 are >0 then return value in B6 but if cell A1, B1 and C1 are >0 then return value in C6...
    Thanks!

    1. Hi Claude,

      You need a nested IF formula like this:
      =IF(AND(A1>0, B1>0, C1>0), C6, IF(AND(A1>0, B1>0), B6, IF(A1>0, A6, "")))

  13. Hi dear,
    I am using a formula =IF(AS$6>=$C135,IF(AS$6<=$D135,($DE135),(0))) to represent the data if in between two dates, if this condition not true show 0....this formula working right. But the issue is that when AS$6<$C135...this shows falls rather than 0....please some 1 suggest me a best possible way

  14. Can you please help me write the a formula to do the following?

    If A is =30 but =60 but =90: Return "SR"

    1. Disregard, my text didn't display correctly, but I figured out the formula anyway.

      Thanks

  15. I want to be select if less the 7500 then select 175 and above 7500 then select 200 how to use the formula use

    1. Hi Pankaj,

      It may be as simple as =IF(A1>7500, 175, 200)

  16. Hi,
    Is it possible to use two function in same sell like =MIN(IF(A1:A5=0, "", A1:A5)) and =Round(A1,0) ?

    1. Nope. Only one formula per cell is allowed.

  17. Iam trying to come up with a formula to fibure materials for a project.
    The project is round columns Our material is 62" wide so and diamater of the material over 29" mean I have to figure the full width of the sheets

    I am trying to have a column for dia of column example
    24" column x 3.146 /2 = 37.752 since this is greater than 29 the formula should read 62" I have used this and made work for greater but it did not work when I had a 18" column that would have been less than 29

    =IF(A2=29,"OK",A1)

    Does this make sense what am I doing wrong?

    1. Hi Gordon,

      I am not sure if the cell references are correct because I don't know your data structure. The following formula returns "OK" if the value in A2 is equal to or less than 29, the value from A1 otherwise. Is this what you are looking for?

      =IF(A2>=29,"OK",A1)

  18. Amazing - thank you so much!

  19. Hi, I have 3 separate dates in 3 different cells. I need the formula to bring back "first" if the date in cell 1 is the most recent date, "Second" if the date in cell 2 is the most recent date or "third" if the date in cell 3 is the most recent date. The spreadsheet has multiple dates which are not defined. Hope you can help. Thanks

    1. Hi Jason,

      You need a nested IF formula like this:

      =IF(AND(A2>B2,A2>C2), "first", IF(AND(B2>A2, B2>C2), "second", IF(AND(C2>A2, C2>B2), "third", "")))

      1. Thanks Svetlana - can you also help with another formula please. I need the formula to show cell ap3 if "first" is in cell cf3 or show cell l3 if "second" is in cell cf3 or show cell bk3 if third is in cell cf3. Can you help please?

        1. I think i've managed to figure the formula thanks. =IF(CF3="FIRST",L3,IF(CF3="SECOND",AP3,IF(CF3="THIRD",BK3)))

  20. Hello.. I'm not very advanced with excel so I hope my query makes sense. I'm trying to create a formula to provide a result in cell E1. The E1 result should be either:

    If A1 equals 'USD' then multiply cells B1 & C1 to provide the result in E1, but if A1 does not equal 'USD' then the result in E1 should be equal to cell D1.

    Hope you can help! Thank you!

    1. Hi Gail,

      Here's the formula for E1:

      =IF(A1="USD", B1*C1, D1)

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

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

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

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

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

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

  27. 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,

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

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

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

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

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

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

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

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

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

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

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

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

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

  41. Hello,

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

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

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

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

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

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

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

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

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

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

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