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

  1. Hi,

    Need a little help with a logical if condition if you have time.

    I have three groups of ranges each with 3 questions, and they can have a value of high, medium or low i.e.
    range 1 - H / H / M
    range 2 - H / M / M
    range 3 - H / L / L

    If all of the values in range 1 = high, and the values in range 2 and 3 = high or medium, then return high
    If any of the values in range 1 = high or medium, and 2 or more in range 2 and 3 = high or medium, then return medium
    If any of the values in range 1 = low, and 2 or more in range 2 and 3 = low, then return low

    Thanks for any help possible.

  2. I am trying to build a formula that takes data from one sheet and transfers it to another sheet in the spreadsheet if the value in the column indicated on the first sheet equals "Yes". Please help.

    1. I'm also trying to figure this out. I would imagine it would be an If equals sort of thing, but I'm not sure how to do it.

      1. You can use the VLOOKUP function to pull matching data from another worksheet and the IF function to check if the condition is met.

        For example, the following formula checks if the value in B2 is "yes", and if it is, it pulls the data for "Product1" from column C on Sheet1; otherwise it returns an empty string:

        =IF(B2="yes", VLOOKUP("Product1", Sheet1!A2:C10, 3, FALSE), "")

        Instead of "Product1", you can refer to the cell containing your key value, say A2:

        =IF(B2="yes", VLOOKUP(A2, Sheet1!A2:C10, 3, FALSE), "")

  3. Hello, Need some one help to apply logical if condition. There is data in column i want to apply if condition....

    A2 B2 Result should be like
    5 (1550)
    6 (1900)
    4 (1200)
    8 (2600)
    If A2 is less than equal to "4" multiple by 300. OR if its greater than equal to "4" multiple by 350.

    1. Hi Mohd,

      You can use the following IF function:

      =IF(A2<=4, A2*300, A2*350)

      1. Thanks!

  4. I have part numbers in a column, some contain the letter V at the end and some do not. I want a formula that indicates the part numbers that contain the V to simply display the value in another cell, and the ones without a V to calculate a simple multiplication.

    Thanks
    Vic

  5. A CPA associate answered my question: =IF((ISTEXT(B8)=TRUE),0,B8)*IF((ISTEXT(D8)=TRUE),0,D8)
    easy peasy thank you for your concern.

  6. I have a simple formula B8*D8, there are times when I want to include various text messages in the cells B8 or D8 and I want the formula to ignore the text and not create an error message. I have tried IF(B8=XXXXX,0,B8)*IF(D8=XXXXX,0,D8) where I have put the x's I have tried everything I can think of to describe any text with no success.

    1. Hi Jeff,

      The following formula returns an empty string if an error occurs, otherwise the product of B8 and D8:
      =IFERROR(B8*D8, "")

      Is this what you are looking for?

  7. Hi! I tried to use this formula but the result is wrong, or it marks the formula as inconsistent... How can I change this to work?
    =COUNTIF(C3:C42,">6<12")

    1. Hi Fany,

      Since you have 2 criteria, you should use the COUNTIFS function and define each condition separately:

      =COUNTIFS(C3:C42, ">6", C3:C42, "<12")

  8. Sorry the above arguments is not complete. Here's the complete one.

    1. If C3>=(10),then 10 days and above behind schedule
    2. If C3=B3, then on schudule

  9. Hi I'm trying to monitor the days of the delivery date if it is on schedule or behind schedule. Can anyone help me please, below are the arguments. Thanks!

    1. If C3>=(10),then 10 days and above behind schedule
    2. If C3=B3, then on schudule

    1. Hi Mushy,

      Here you go:

      =IF(C3=B3, "on schedule", IF(C3>=10, "behind schedule", ""))

  10. Hi There,

    I am working on a report where I have to compare three columns to match a Tier 1-5 based on dollar value in a second column and a risk rating in a third.

    i.e Col c is Tier, Col f is Risk (High, medium, low, blank), col H is total cost $value. I need to be able to filter out errors where if it says tier 2, it could have any risk rating but must fall in to threshold of $10MM to $49,999,999.99 OR if it is rated "high" risk and falls between threshold of $1MM to $9,999,999.99.

    I need to show times when there is improper matching of tier and dollar value and highlight those records.

    Any formula suggestions would be helpful!
    Thanks!

  11. Hi,
    I am trying to prepare aging report in one column there are amounts related to different years. For example
    1- 12-Jan-2013 8,000
    2- 12-jan-2014 9,000
    3- 12-jan-2015 10,000

    so I want amount related to 2013 in One column, for 2014 in next means each year in different column.

  12. I'm trying to get a formula to do the following.
    If B3 matches any cell exactly in a list say K3 through K30 then multiply G3 by .0223. I would like to have a few criteria ranges but if I can get help with the first one, I should be able to replicate it for the other criteria.

    1. Hi Michael,

      Try the following array formula (remember to press Ctrl+Shift+Enter):

      =IF(SUM(--(B3=K3:K30))>=1, G3*0.0223, "")

  13. Hi,

    How can fix this formula. Pls revert. thanks in advance

    Value is 31
    Week 1- (Normal Hrs*Rate+OT@Rate)Value is 1>=7, Ruselt will be colomn 1
    Week 2- (Normal Hrs*Rate+OT@Rate)value is 1>=14,Ruselt will be colomn 2
    Week 3- (Normal Hrs*Rate+OT@Rate)Value is 1>=21,Ruselt will be colomn 3
    Week 4- (Normal Hrs*Rate+OT@Rate)Value is 1>=28,Ruselt will be colomn 4

  14. Hi there,
    I am trying to make a formula based on the cell number falling within a certain range. The problem is: if the number is between 1 and 120 I need it to multiply that number by 11.2. If the number is between 120 and 200 I need to it multiply the number by 10. If the number is between 200 and 280 then I need that number multiplied by 8.5. If the number is between 280 and 450 I need the number multiplied by 7.2, and if the number is over 450 then it needs to be multiplied by 6.6. The number is created by the data I add in other cells. I have tried nested IF's, but the formula only seems to stay true if the number is over 450, The others don't add up to what they should. Can someone please help?
    thanks,
    Cheryl-Ann

    1. Hi Cheryl-Ann,

      The following formula seems to work just fine:

      =IF(A1>450, A1*6.6, IF(A1>=280, A1*7.2, IF(A2>=200, A1*8.5, IF(A1>=120, A1*10, IF(AND(A1>1, A1<120), A1*11.2, "")))))

  15. thank you

  16. I am trying to create a formula using if statement that takes 2 values and devides them. If the result is greater than 100 I want it to put 100 in the field, if the result is less than 100 I want it to put the result of the division. this is what I have and it is not working

    =IF(D3/C3>100, 100, D3/C3)

    Can somebody help me with the correct formula.

    thanks

    1. Hi Kevin,

      The formula is correct. However, if C3 is blank or contains a zero, it returns #DIV/0 error because you cannot divide by 0. Is this the case?

      As a workaround, you can wrap your formula in the IFERROR function, like this:

      =IFERROR(IF(D3/C3>100, 100, D3/C3), "")

      If C3 is 0 or blank, it returns an empty string. Or you can type any message within "".

  17. It will be appreciated if you could assist with a formula I already have.

    I am already using formula:

    =IF(A17>25000,(270+((((ROUNDUP(A17,-3))-25000)/1000)*6)),IF(1000.0125000,(30+((((ROUNDUP(A17,-3))-1000)/1000)*10)),30))

    My questions is: What I am missing when the value is zero the fee should be zero, but I am getting $20

    Based on contract price:

    A. Up to and including the first $1,000.00 of contract price or fraction thereof, the fee shall be $30.00.

    Thereafter:
    B. Add $10.00 per each additional $1,000.00 up to $25,000.00 of contract price or fraction thereof. $30 +($24(k) x $10) = $270.00
    Thereafter:
    C. Add $6.00 per each additional $1,000.00 after and over the initial $25,000.00 of contract price or fraction thereof.

    $30 + (24(k) x 10) = $270.00 + $6.00 for each additional $1,000 after $25,001 and over.

  18. Trying to do conditional formatting on a column for if column K2 is greater than J2 highlight K2. The problem is that the values in the K column are all less than values (example <0.0045) so it highlights all of them regardless of the value. Is there any way to do a conditional formatting formula that will understand the values in the K column?

    1. Hi Tad,

      Try creating a rule for column K with the following formula:

      =$K2>$J2 Where 2 is your first row with data.

      Make sure the rule does not apply to the header row, otherwise the formatting will get shifted 1 cell up.

      1. I have a worksheet where I am trying to do if I4<J4 (an each subsequent row) then highlight, but I've tried:
        1) highlight the cells in column I, not including any header rows
        2) conditionally format rule "=$I4<$J4" (and then choose my formatting)
        3) it says applies to "$I$1:$I2413"
        However, it is not highlighting correctly (some cells where II are highlighted)

        1. Hi Alison,

          You should write the formula for the top-most row of the range to which you want to apply the rule.

          For example, if you want the rule to be applied to $I$1:$I2413, then the right formula is =$I1<$J1 If you want to highlight values beginning with row 4, then make sure the rule based on your formula (=$I4<$J4) applies to $I$4:$I2413.

  19. Dear Please tell me the formula for the bellow condition

    I am trying to set up a result based on the score achieved, example if some one got <80 should study Starter book and if some one got < 105 should study book 1 and if some one got <120 should study book 2 and so one tell book 5, please help me in this regard. ASAP.

    1. Hi Mohammad,

      You can use nested IF functions like this:

      =IF(A1<80, "starter", IF(A1<105, "book 1", IF(A1<210, "book 2", "")))

      Where A1 is the score.

  20. Hi,

    Please help me to find the solution

    A2 = Day or Week or DayWeek
    B2 = Arive date
    C2 = Depart date
    E2 = The number of days

    Thsi is a hotel and guest stays the charges are days and weeks, lets say a guest stays for 7 days the then the charges should be calculated for 01 week and balance days, so how to calculate this if A2=Day the answer total days, if A2=DayWeek the answer the total days but should be reduced by 07 days (01 week)

    Eg
    A2 => Day or Week or DayWeek
    B2 => 01/04/2015
    C2 => 10/04/2015
    E2 => if A2 is day the 10 days
    E2 => if A2 is Week then 01 week
    E2 => if A2 is DayWeek then 03 days (total days - 01 week)

    I want to know the formula to calculate if A2 is DayWeek then the answer should be remain days which reduced the week from total days (10-7=03days)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  38. Amazing - thank you so much!

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

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

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

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

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

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

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

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

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

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

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

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

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