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

  1. I wrote this formula =IF(OR(F2>=3,F2<=17),H34*E34,0) I am trying to get H34*E34 to calculate if F2 is between 3 and 17 and if not to put 0 in the cell. What did I do wrong?

  2. I want to get a value greater than or equal to certain CELL VALUE from a column in form of a numeric value as added already in that cell of a column.
    Ex:
    A B C
    1 456 250 Want a only 1st value greater than 250 from A in no ?
    2 789
    3 121
    4 89

  3. Hi,
    I am trying to use the IF function to validate a number in a cell which is a calculated field. Example:

    Function within G2 field: =E2-F2
    If function: =IF(G2=0,"VALID","INVALID").

    For some reason this IF function is returning VALID no matter what the calculated value in cell G2 is.

    Additionally, the E2 and F2 fields are calculated (but working correctly), one is a VLOOKUP function returning a number, and the other returns a number based on a mathematical calculation.

    Can you please help?

  4. Hi Svetlana,

    Can you please help in creating a formula as below:

    suppose 154 days, a formula to classify 0-60, 61-120, 121-180, 181-360.

    I'm unable to create the formula =If(154>60,61120,121180,181360,"<60","61-120","121-180","181-360")

    it should be 154days 121-180

    Thanks

  5. Hello,
    I have rows of numbers, some negative some positive. I need a formula to count the value of only the negative cells and sum them up at the end of the row. Please help -thank you

  6. Have the >= and =0.93,"A",=90,"A-",=87,"B+",= 83, "B",=80,"B-",=77,"C+",=73,"C",=72, "C-", <70,"YOU FAILED")

    It's for a grade calculator, in which E24 is the grade as calculated by points earned divided by possible points. Anyone see my error? Psyched about this new SWITCH function.

    1. Okay, that didn't post right...What I meant is have the = functions been incorporated into the SWITCH function yet? That formula is NOT what I had. It was a lot more complicated than that and had a lot of operators in it. Oh, well! Cest la Vie!

  7. I want to return a True or False answer if Week 4 team selection has already been selected in another Week.

    Week 1 Week 2 Week 3 Week 4 Week 5
    Liverpool Arsenal Arsenal Liverpool

  8. hello
    I need help with a formula please:
    I use a formula to calculate how much I pay for an item, that is: =(91.2/2)*2*b12*4.3. Depending on what the figure in b12 is the sum can be very high and I want to cap the result at a dollar figure of 1200. What can I use to show a result of 1200 if the sum of the formula exceeds 1200?

  9. How to find the text greater than 40 char.
    Like:-
    Shop No 01 Shree Shankar Sadan Chawdi Naka, Samelpada, Nallasopara (w) Mumbai

    I want to split it into two cells first 40 char in one cell and greater than 40 in another cell.

  10. what is the formula for "condition if F4 IS EQUAL TO OR MORE THAN 100 USE 70 OTHERWISE USE THE SAME VALUE IN F4

    1. Hello Belal,

      Use this formula:

      =IF(F4>=100, 70, F4)

  11. what is the formula for "condition if Column A is more than Column B, use Column A-Column B*0.40"

    1. Hello Mahesh,

      Here you go:

      =IF(A1>B1, A1-B1*0.40, "")

  12. a1,50

  13. Tyre code Tyre desc C1 C2 C3 C4 C5 Total STK 01-02-2016 02-02-2016 03-02-2016 04-02-2016
    3IND5050810810 5.00 - 8 IT 30 8 PR TT TVS 40 20 60 80 200 22 0 0 15 35
    3IND5650810810 5.00 - 8 8PR TT MARANGONI 55 65 75 195 10
    3IND5050821010 5.00 - 8 IT 45 10 PR TT TVS 60 32 42 134 30

  14. what is the formula for "condition if Column A is more than Column B, use Column B"

    1. Hi Charlie,

      You can use a formula similar to this:

      =IF(A1>B1, B1, "")

  15. HELP!!
    I am trying to find what formula I should use.

    IE:
    A B
    1 john Paris
    2 smith London
    3 rose Milan

  16. Hello friends,

    I am struggling with a formula. please help me out.

    If a cell value is greater and less then a given value then "OK" and "Not ok"

    for Example. IF cell A2 is greater then 12 but less then 22.8 . If cell A2 meet this criteria then "Ok" otherwise "Not Ok".

    Thanks

    1. Hi Sumit,

      You can use the following formula:

      =IF(AND(A2>12, A2<22.8), "OK", "Not OK")

  17. HELP!!
    I am trying to find what formula I should use.

    IE:

    1-9 multiply by .07
    10-19 multiply by .08
    20-29 multiply .10
    30-39 multiply .12
    40 or greater multiply .14

  18. IF cell A=Apples and cell B10 but 20 but <30, then return cell C=500.
    IF cell A=Grapes and cell B10 but 20 but <30, then return cell C=300.

  19. Trying again...

    IF cell A=Apples and cell B10 but 20 but <30, then return cell C=500.
    IF cell A=Grapes and cell B10 but 20 but <30, then return cell C=300.

  20. IF Cell A=Apples and cell B10 but 20 but <30, then return cell C=500. IF cell A=Grapes and cell B10 but 20 but <30, then return cell C=300.

    1. I don't know why I'm not able to publish my complete question. there is more to the above logic that I'm looking to add.

  21. For some reason, it didn't post my complete question.

    IF Cell A=Apples and cell B10 but 20 but <30, then return cell C=500. IF cell A=Grapes and cell B10 but 20 but <30, then return cell C=300.

  22. I meant,

    IF Cell A=Apples and cell B10 but 20 but <30, then return cell C=500.
    IF cell A=Grapes and cell B10 but 20 but <30, then return cell C=300.

  23. Hello, looking for formula for the below logical statement,

    IF cell A=Apples and cell B10 but 20 but <30, then return cell C=500.

    IF cell A=Grapes and cell B10 but 20 but <30, then return cell C=300.

    Appreciate your help!!

  24. cell A1=K, cell A2=L, Cell A3=K
    Cell B1=10-Feb-2016, Cell B2=02-Feb-2016, cell B3=15-Feb-2016.

    I need equation at cell A4 to show the maximum date relevant to item K (In column A) from the dates in column B, which is 15-Feb-2016.

    Please advise me which formula should I use.

    Thank you in advance.

  25. iN ONE CELL"32*34*36" ,WANT TO MULTIPLY IN ANOTHER CELL WITHOUT RETYPING .iS IT POSSIBLE?

  26. ***HELP!!***
    I need a formula for the following. It's kind of a multi-step process:

    1. If A1=A2 and B1=B2, then return value TRUE.
    2. If A1=A2 and B1B2, then return value FALSE.
    3. But if A1A2, then disregard formula/leave blank.

    1. I meant to say:

      1. If A1=A2 and B1=B2, then return value TRUE.
      2. If A1=A2 and B1 doesn't equal B2, then return FALSE.
      3. But if A1 doesn't equal A2, then disregard formula/leave blank.

      1. I know it's confusing, but I ONLY want the B values to equal each other if the A values are equal... If the A values aren't equal, then I want the B values to forget that command and revert to only like values...Here's an example of what I mean:

        A1:A7 all have the same value. If B1=B2=B3...=B7 then great! If B1:B7 DON'T have equal values, then I need an error notice. After A1:A7, there is another group of DIFFERENT like-values...A8:A15. For all of these, I need B8=B9=B10...=B15. If they don't, I need an error. Hope that helps.

        1. Cameron,

          My previous formula works for 2 cells only (A1 and A2). To compare groups of 7 cells, try the following one:

          =IF(AND(COUNTIF($A$1:$A$7, $A1)=7, COUNTIF($B$1:$B$7, $B1)=7), TRUE, IF(AND(COUNTIF($A$1:$A$7, $A1)=7, COUNTIF($B$1:$B$7, $B1)<>7), FALSE, ""))

          If cells A1:A7 have the same values and B1:B7 have the same values, the formula returns TRUE.

          If cells A1:A7 have the same values but B1:B7 have different values, the formula returns FALSE. You can change this to any error message you want.

          Otherwise, the formula returns an empty string.

          1. Hi Svetlana!

            Thank you so much for your quick replies. When I tried to enter this last formula, it gave me a notice for a circular reference? I clicked ok, and the formula returned a zero. Any ideas what this means/how to fix?

            Secondly, I have an even more complex question for you (if that wasn't already hard enough). What if my first group is A1:A7 but the next grouping is A8:A18? What if it isn't always groups of 7, but it varies group size randomly down column A? Is there any way I can enter a formula to predict this, and always have the corresponding B values equal? (Where B1:B7 are equal and then B8:B18 would be equal, etc.) Again, I just need the formula to return True or False for these groupings. Thanks!!

      2. Hi Cameron,

        Try the following formula:
        =IF(AND(A1=A2, B1=B2), TRUE, IF(AND(A1=A2, B1<>B2), FALSE, ""))

  27. If one date is greater than another date I need a return value of true and a count of the difference. Example if 02/01/2016 thru 02/06/2016 equals 5 days then true if not false. Also how many day greater or less? I will need the formula to continue throughout the year.

  28. hi,
    i want the cell to compare a number with another cell and copy the cell with greater value

  29. I am struggling with this simple task .. can you pls help ?

    I have an excel sheet with over 20,000 products with weight in decimals. I am trying to do this :

    if weight is greater than 0.00 and/equal 1.0 then "0-1 lbs"
    If weight is greater than 1.10 and/equal 2.00 then "1 and 2 lbs"

    I am using =IF(AND(A2>0,A2<1),"0-1 lbs","") but this is not working for decimals

    examples :

    Weight (lbs)
    0.10
    0.01
    0.02
    0.03
    0.04
    0.05
    0.06
    0.07
    0.08
    0.09
    0.10
    0.11
    0.12
    0.13

    1. Hi Vinay,

      Try this one:
      =IF(AND(A2>1.1,A2<=2),"1 and 2 lbs", IF(A2>0,"0-1 lbs",""))

  30. Hello I have a weird request. So here is what I can tell you about this problem we have a system that if we reach 10.00 that is 100% if it falls below that then it is greater than 100% if it is greater than 10.00 then it would be lower than 100%. Is there a formula to do in excel that can figure this out or not really?

  31. Hi there, need some help with between formula below is the formula I am trying to create

    =if(r38 is between 0.5 and 8.5,U42,if(r38>9,0,if(r38 is between 9 and 14.5,v42,if(r38>14.5,0,if(r38 is between 15 and 18.5,w42,if(r38>18,0,if(r38=18.5 +,X42

    Thank you,

  32. the formula type out wrong...
    it's
    =IF(B18011200;40%*B1)

    1. Hi Celeste,

      Our blog engine often mangles formulas in comments, sorry for that.

      Try the following formula:
      =IF(B1>=1201; 40%*B1, IF(B1>=801; 30%*B1; 20%*B1))

      1. It worked! Thank you Svetlana!!!

        1. I need a help guyz..for eg when x>20 should proceed with a formula or when x<20 takes the other.please help me to create a formula buddies

          1. What formulas you want to see when one of the conditions is met? Also, is 'x' in 'x>20' is a cell reference? Give us more details, so we would be able to help you out.

  33. Hi Svetlana!
    I need to automatically calculate, with a formula, for discounts in cell B1:
    20% discount: less than $800 orders;
    30% discount: orders from $801 to $1,200; and
    40% discount: orders over $ 1,201.

    I've used:
    =IF(B18011200;40%*B1)
    and I get the 20% and 40% discount calculation, but the 30% calculation is wrong. Can you correct it for me?
    Thanks!

  34. I want to multiply 2 cells, A11 and L11, but if the product is <=0 I want to enter Cell A11.

    1. Hi Marlone ,

      Try the following formula:

      =IF(A1*L11<=0, A11, A1*L11)

  35. Hello Svetlana could You please help
    In cell H-4 I have an amt. 139629.11
    In cell I-4 I need if formula showing down the column when copied
    if the amt in h4 is same 00 if its grater show the difference if its less show the difference.
    I am just an amateur mathematician but I do not know how to bit it
    something is missing there
    Thanks for Your help in advance. Jozef

  36. Hi Svetlana!
    In cell A1 I have the total sum of the order.
    I need to automatically calculate, with a formula, for discounts in cell B1:
    20% discount: less than $800 orders;
    30% discount: orders from $801 to $1,200; and
    40% discount: orders over $ 1,201.
    Can you please help me?
    Thanks!

  37. I wanted to align my stock price bandwise with and if & and formulas please suggest

    suppose Cell E Contains following and i wanted priceband 0-2, 2.1-5, 5.1-8, 8.1-12, 12.1-18 & 25+

    100000
    99990
    299900
    387666
    789000
    900000
    1015000
    1815000
    1215000
    1315000
    2500000

  38. Hi, I'm stuck and looking for help please.

    I need to take a range of cells with dollar values and compare them to one cell with another dollar value. The range of cells which has the least dollar amount I need to show up as highlighted in a different colour. For example:

    A1 = $100
    Compare values in A2-A10, if A6 has the least (cheapest) value ($99) then this cell is highlighted in a stand out colour

    Any help is greatly appreciated.

  39. how I calculate in excel , If I dont need amount less than 1500 in a coloum.

  40. Problem with conditional formatting using greater/lower than:

    how do I skip a blank cell and compare with the previous cell?

    Example: A1= 5, B1= blank, C1= 2

    I need to know if C1 is greater/lower than B1, but if B1 is blank then I want to skip B1 and instead get result for C1 greater/lower than A1. What formula do I use to do this?

    Thanks in advance!

    1. Hi Niksua,

      You can create 2 conditional formatting rules with the following formulas:

      If B1 is not blank, highlight C1 when it's greater than B1:
      =AND($B1<>"", $C1>$B1)

      If B1 is blank, highlight C1 when it's greater than A1:
      =AND($B1="", $C1>$A1)

  41. bonus calculation suppose B4*0.0833 but not greater than 583
    what is the formula than

  42. Looking for how to return a value for a number that falls within certain ranges. For example:

    If H10 is greater than 750, return 1
    If H10 is between 650 and 749, return 2
    If H10 is between 550 and 649, return 3
    If H10 is less than 550, return 4.

    So that if in cell H10 a number is entered, then in cell H11 a value of 1, 2, 3 or 4 will be displayed.

    Any help would be appreciated. Thanks!

    1. IF(H10=>750,1,IF(H10=>650,2,IF(H10=>550,3,4)

  43. Suppose A1=A+B+C+D & A2=B+A+D+C , i apply =exact(A1=A2) but result is "False".
    In Excel any other function for this. Reply me as early as possible.
    Thank you...

    1. Hello Bhanu,

      EXACT is the right function for this task. Just note, the correct syntax is =EXACT(A1,A2)

      You can test it in the following way. Enter some values in another column, say column C, and then type the following formulas:

      In cell A1: =C1+C2+C3+C4

      In cell A2: =C2+C1+C4+C3

      Then enter the formula =EXACT(A1,A2) in some cell, and it will return TRUE.

  44. say for example i have 2 colums with a lot of numbers they are not by each other how do I put all the numbers which are equal to each other in excel

  45. AMAZINGLY CLEAR EXPLANATION, THANK YOU!!

    I've only used the double unary operator trick several times over my 20 years of daily Excel use because no one has ever able to effectively explain what the two negative symbols did.

    Now I can add this to my repertoire and actually KNOW what it does. PRICELESS!

  46. I'm going crazy on this..

    I need to create a conditional formatting meeting several criteria.

    If a1>0 and = d1 OR b1>0 and = c1

    1. Hi Mauriccio,

      Try creating a conditional formatting rule with the following formula:

      =OR(AND($A1>0, $A1=$D1), AND($B1>0, $B1=$C1))

  47. Hi Svetlana, regarding to operator larger or smaller, I would like to know is there any operator to interpret data as below:

    for example I have:
    account code listed on column A: 1, 2, 3, 10, 20, 30, 100, 200, 300
    Amount listed on column B: 1000, 2000, 3000, 4000, 5000, 60000, 7000, 8000, 9000

    I want to use sumifs statement to summarize the amount when the account code is under 1XX (e.g. the sum of account codes 1, 10 and 100), in this case I don't think sumifs criteria using larger or smaller operator can sort out the account codes that I want....Do you have any idea on this?

    Many thanks.

  48. no worries, got it

  49. okay about to give up. I need the number entered in one cell (which is D18 on my SS) to decide what range and assign the appropriate value. for example if the number is 1-8 then 3 would display in cell G18, if 9-16 than 6, if 17-25 than 9, if 26-34 than 12, 35 plus would be 15

  50. previous post was incorrect

    IF((C7>72700,9,"0"),IF(C7>227425208012143916<208012,2,"0"))

    Need help with this formula
    Vehicle and odometer readings
    If cell C7 is 72700 or higher it is vehicle 9
    227425 or higher 5
    208012 or higher 21
    143916 or higher 2

    1. Hi!

      You need to use nested IF functions and the correct syntax is as follows:

      =IF(C7>=227425, 5, IF(C7>=208012, 21, IF(C7>=143916, 2, IF(C7>=72700, 9, 0))))

      1. Hello,
        Thank you for your prompt response. I very much appreciate it!

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