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

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

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

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

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

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

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

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

  8. no worries, got it

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

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

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

  12. I need to create a function based on a decimal place
    ex. 7.341, if the the decimal value is greater or lesser than .4, the value of "7" would be unknown lets say, if the number has a decimal point equal to .4 or greater the value of "7" stays the same, if the number has a decimal point of .39 or less than, we will subtract 1 from the "7". How do I put this into a function especially since the value is dependent on decimal points.

    1. Hi Justyna,

      If my understanding of the task is correct, one of the following formulas should work a treat (where A1 is the decimal number).

      Return a decimal number:
      =IF(A1-TRUNC(A1,0)>=0.4, A1, A1-1)

      Return an integer:
      =IF(A1-TRUNC(A1,0)>=0.4, TRUNC(A1,0), TRUNC(A1,0)-1)

  13. I have to select multiply based on below conditions
    If"value"600, then O.D+600

    "value" cell is diameter of pipe
    result of "then" statement is " width of trench"
    required to bury that pipe.
    the "width of trench" varies with pipe diameter.

    can u help me write formulae for above
    input"value" IS DIAMETER
    output"Width"

  14. I'm trying to create a formula to automatically compare two scores and return whether the game was a win, loss, or tie for the team in question. I understand how to handle just wins and losses [for instance, =IF(BS10>BR11,"W","L")], but the possibility of a tie complicates things. I've tried using the IF formula with the if true value set to "T" and the if false value as the equation above, but it is invalid. Is there a way to do this?

    1. Hi Jake,

      I think you can use the following nested IF's:

      =IF(BS10>BR11,"W", IF(BS10<BR11, "L", "T"))

      1. Hi,

        can you help me our please

        if G3 is greater than H3 I want to have these 2 cells to be subtracted as G3-H3, if G3 is lesser than H3 I want to have these 2 cells to be subtracted as H3-G3

        i had used the below formula but error occurred so can you help me please

        =IF(G3>H3),"G3-H3",IF(G3>H3),"H3-G3"

  15. Many Thanks Svetlana,
    in need:
    L9 = 3000 if k9=(100% to 120%)
    L9=4000 IF K9 =(121%-130%)
    L9=5000 IF K9 greater than 131%

    J8= 0% IF E8=(0% TO 50%)
    J8= 10% IF E8=(51% TO 60%)
    J8= 15% IF E8=(61% TO 80%)
    J8= 20% IF E8=(greater than 81%)

    Best Regards
    Majed

  16. How would you type an IF statement for a cell that is equal to 50 , 500, or 550 to be multiplied by formula x but for all other values to be multiplied by formula y.

    1. Hi Taquana,

      You can use a formula similar to this:

      =IF(OR(A1=50, A1=500, A1=550), A1*x, Z1*y)

  17. Hi Svetlana,
    i need value in H2 = E2 when it< or = the value in G2
    if this value greater than the value in G2 then the value in H2=the value in G2
    THANKS,

    1. Hi Majed,

      Here's the formula for H2:
      =IF(E2<=G2, E2, G2)

      1. Many Thanks Svetlana,
        in need:
        L9 = 3000 if k9=(100% to 120%)
        L9=4000 IF K9 =(121%-130%)
        L9=5000 IF K9 greater than 131%

        J8= 0% IF E8=(0% TO 50%)
        J8= 10% IF E8=(51% TO 60%)
        J8= 15% IF E8=(61% TO 80%)
        J8= 20% IF E8=(greater than 81%)

        Best Regards
        Majed

  18. Hi
    I wonder if you could help me please, I need to set up a formula which will allow me to charge based upon numbers;
    i.e
    100 or less = £35
    101 to 200 = £45
    201 to 300 = £55
    301 to 400 = £65
    401 to 700 = £75
    701 to 1000 = £100

    Many Thanks,

    1. Hello Susan,

      You can use the following nested IF functions:

      =IF(A1>700,100, IF(A1>400,75, IF(A1>300,65, IF(A1>200,55, IF(A1>100,45,35)))))

  19. Hi Svetlana,
    Happy Holidays! I would like to know if you would be able to help me with my request no. 248, above. I appreciate it very much!

    Sincerely,
    Lorena

      1. Thank you so much, Svetlana! I am looking forward to trying this.

        Sincerely,
        Lorena

  20. Please help.
    This is my problem:

    start time 08:00 am End time 19:00 pm
    Start time 18:30 pm End time 22:00 pm

    If the shift starts after 18:00 or ends after 18:00 a night allowance of R15,00 is payed.

    How do I do this function.
    this is what I have come up with ,but what I need is c2(start time) or d2(end time)is greater than 18:00 then the allowance needs to be paid.
    =IF(C2>0,75;G2+15;G2)

    1. Hi Lance,

      If C2 and D2 are time values, you can use a formula similar to this one:

      =IF(OR(C2>TIME(18;0;0); D2>TIME(18;0;0)); G2+15; G2)

  21. Hi Elexed.Can you put this formula
    =$G8+MIN(IF($F8<5,5000,IF($F8<10,7500,IF($F8<15,10000,IF($F8<20,12500,IF($F8<25,15000,IF($F8<30,17500,20000)))))),ROUND(IF($F8<5,0.2,IF($F8<10,0.3,IF($F8<15,0.4,IF($F8<20,0.5,IF($F8<25,0.6,IF($F8<30,0.7,0.8))))))*G8,0))

  22. I need these condition:
    1. If b=0 = a/2
    2. if b<=0, a<=0 = 0
    3. if b<=0 = 0

    I use these 2 formula :
    =IF(E1=0,"",IF(D1<=0,"")))
    =IF(E1=0,D1/2,0),elseif(E1<=0&D1=0,0)

    but when b<=0, a<=0 this is not working. that I want to mean- if both b&a cell negative or <=0 it is not working. Is it possible for those 3 condition apply in excel.

    Please Help me.
    Dider-E-Elahi

  23. Hi,

    I am trying to use IF/AND to look at two cells that each could contain either "TRUE", "FALSE" or 'blank'. and return either "G" if both are TRUE ... "A" if one cell is "TRUE" and the other either "FALSE" or 'blank' ... "R" if neither contain "TRUE" or both are "FALSE" or 'bank'etc..

    I have tried this formula
    =IF(AND(Sheet2!B11="TRUE", Sheet2!B14="TRUE"),"G", IF(AND(Sheet2!B11="TRUE", Sheet2!B14="FALSE"),"A", IF(AND(Sheet2!B11="", Sheet2!B14="TRUE"),"A", IF(AND(Sheet2!B11="", Sheet2!B14=""),"R", IF(AND(Sheet2!B11="TRUE", Sheet2!B14=""),"A", IF(AND(Sheet2!B11="", Sheet2!B14="FALSE"),"R", IF(AND(Sheet2!B11="FALSE", Sheet2!B14="FALSE"),"R", IF(AND(Sheet2!B11="FALSE", Sheet2!B14=""),"R", IF(AND(Sheet2!B11="FALSE", Sheet2!B14="TRUE"),"R")))))))))

    But the only permutation that returns a result is if they are both blank.. the rest return "FALSE"

    Please help

  24. Hi!Kindly affix one formula in proposed salary column which may taken into account tenure in Hospital, present salary & auto increase & %age of present salary as per given policy schedule & pick the figure which is lower side & then apply the same amount as Marginal Increase in salary. In short i want to implement the policy schedule for proposed increase in salary.
    1 A-0412 2/1/2014 29D.10M,1Y 34,225 (34,225)
    2 A-0416 1/27/2015 3D.11M,0Y 33,472 (33,472)
    3 A-0431 1/1/2007 29D.11M,8Y 38,824 (38,824)

    Range of Years Worked Maximum Limit of Increase in Salary
    Below 5 Lower of 5,000 or 20 % of present salary
    5-10 Lower of 7,500 or 30 % of present salary
    10-15 Lower of 10,000 or 40 % of present salary
    15-20 Lower of 12,500 or 50 % of present salary
    20-25 Lower of 15,000 or 60 % of present salary
    25-30 Lower of 17,500 or 70 % of present salary
    30-35 Lower of 20,000 or 80 % of present salary

  25. Hello,

    I have a bunch of numbers that are updating in column B (around 100 rows of percentages and their symbol in column A) every fifteen minutes. I want to display only the ones that are less than -5% or greater than 5% with their symbol. Is there a way to do this and display it in a new list so it only shows these values in a condensed list?

  26. Hi Svetlana

    Now it my issue is resolved, You are "GENIUS", Thanks a lot, Have a Great Happy Life and Happy New Year, God Bless You.

    I really appreciate and thank you again.

    Asad

  27. Hi Svetlana

    I mean to say, for example if I receive 10 items in "QTY RECEIVED" in Col-A and issue 11 items in "QTY ISSUED" in Col-B then your formula is showing -1 in "QTY BALANCE" in Col-F, I want that is should also show "error" in stead of -1.

    Thanks

    Asad

    1. Hi Asad,

      Here you go:
      =IF(AND($B2<=$A2, SUM($C2:$E2)=$B2), $A2-$B2, "error")

  28. Hi

    How to check greater than 30 but less than 60 in a cell. Imagine value is in Cell A1.

    1. Hi Ihsan,

      You need to use an AND statement like this: AND(A1>30, A1<60)

      For example, =IF(AND(A1>30, A1<60), "OK", "Not OK")

  29. Hi Svetlana

    can you please help me about find out formula against my Question No. 246, I will really appreciate.

    Thanks

    asad

    1. Hi Asad,

      If my understanding of your task is correct, the following formula should do a trick:

      =IF(SUM($C2:$E2)=$B2, $A2-$B2, "error")

      1. Hi Svetlana

        Thanks a lot for your help, it is almost done, formula is working except a little more help, if value of column A and B does not match then it should also show error.

        Thank you again.

        Asad

        1. Hi Asad,

          My understanding was that A and B can be different values, because we calculate their difference. Please clarify what you mean by "A and B does not match".

  30. Hi

    I need a formula when, whereby the value ranges from -0.03 to 0.03, it should appear as True. And if there are other values, it should appear as False. Please help with this. Thank You.

  31. Hello,
    I'm working on a spreadsheet for grades in my class. I have a column that includes the total points earned by each student. I would like to set up a rule that would give an output if the total points are within a particular range. For example, if the total points are between 360 and 400, the output would be an "A," If the points are between 320-359, the output would be a "B" etc. I was able to make it work with an IF(AND but once I added a third condition, it wouldn't work. I'm also not sure how to format the rule when I want the cell I'm referencing to be in between a set of numbers. For example 319<Cell<360. What do you recommend?

    This is my grading scale in case it helps.
    A 400-360
    B 359-320
    C 319-280
    D 279-240
    F 239-0

    Thanks for your help!!

  32. I am trying to find a formula that will take a (24 hour) time in one cell, compare it to a (24 hour) time in a different cell and if the second cell is greater than "13:00:00", enter "13" in a separate cell.

    From my information:
    if 24:00:00 (in cell e9) is greater than 13:00:00 (in cell f9), then cell h9 will equal "13".

  33. Hi Svetlana

    I am making inventory sheet and facing problem about calculation the Balance QTY. let me explain here; I have made total 6 columns;

    Col A - 10 QTY. Received
    Col B - 7 QTY. Issued
    Col C - 4 QTY. Installed
    Col D - 1 QTY. Returned
    Col E - 1 QTY. DEAD
    Col F - QTY. Balance

    I have purchased 10 items and issued 7, that means 3 items should be shown in balance, but my inventory record justifying total 6 items of column C,D and E. So it should show error in column F until I find 1 missing item? Because I have issued total 7 and if total items of C,D and E matches with column B then 3 items should show in column F Balance.

    I will really appreciate for your help.

    Thanks

    Asad

  34. i want compare Column F2 & E2 for 10% difference and display it fg in windings3 in column C hoow can this be achieved?

    This is what i have at the moment.

    =IF(F2=E2*0,9,F2<=E2*1,1),"fg","i"))

    1. =IF(F2=E2*0,9,F2<=E2*1,1),"fg","i"))

      1. Hi!

        You probably meant this one:
        =IF(AND(F2>=E2*0.9,F2<=E2*1.1),"fg","i")

        Alternatively, you can embed the percentage change formula in the logical test:

        =IF((F2-E2)/E2<=0.1,"fg","i")

  35. Hi Svetlana,

    I'm trying to calculate the following:
    If cell A is greater than or equal to cell B, and cell C is greater than or equal 930, cell F will pay x amount.

    Thanks in advance for your help.

    Al

    1. Hi Al,

      You can use a formula similar to this:
      =IF(AND(A1>=B1, C1>=930), 10, "")

      Where 10 is x amount.

  36. Hi Svetlana

    I am making inventory sheet and facing problem about calculation the Balance QTY. let me explain here; I have made total 6 columns;

    Col A - 10 QTY. Received
    Col B - 7 QTY. Issued
    Col C - 4 QTY. Installed
    Col D - 1 QTY. Returned
    Col E - 1 QTY. DEAD
    Col F - QTY. Balance

    I have purchased 10 items and issued 7, that means 3 items should be shown in balance, but my inventory record justifying total 6 items of column C,D and E. So it should show error in column F until I find 1 missing item? Because I have issued total 7 and if total items of C,D and E matches with column B then 3 items should show in column F Balance.

    I will really appreciate for your help.

    Thanks

    Asad

  37. Ok, I have multiple formulas going on.

    Cells A:A I input different numbers from 0 to 38.

    Cell ak1 =0, al1 = 1, am1 = 3 etc.

    Under each cell example (ak2, I have =countif (a1:a120,ak1) giving me the results of how many times ak1 (0) appears in column a1:a120.

    Each of these results in different amounts. I would like to sort the greatest amounts to the lesser amounts displaying the number in ak1 not ak2 thru cells b2:b39

    For example if 9 shows up 7 times and is the most I want 9 to show in cell b2 and then then next lesser number to appear in b3, etc.

  38. =IF(G10>=0=1000=1000=6000=9000,"EXCELLENT")

  39. Hi I need to compare Data in 2 sheets on a daily basis. I have a list of products names (in Alphabetical order) in Row A that have best before dates (CODE LIFE) in row I. I need to know if the best before date changes at all by + or - 5days. However the products in row A may not always be in the same order or even there as we may have sold out of the product under that BBD or Sent more product! today I may have 3 lines of Product A but tomorrow I may only have 2?? could you help please??

  40. HELP!! I need help on a formula
    If D15 is less than 21, but greater 101, then c5+c6+c7+c8*d15

    thanks in advance!!

    1. Hi Christa,

      No number can be less than 21, but greater than 101 :) Do you mean greater than 21 and less than 101?

  41. I need help on a formula

    If J3 is less than 65 then A
    If J3 is equal to 65 then B
    If J3 is greater than 65 then C

    Thanks

    1. Hi!

      You can use the following nested IF's:

      =IF(J3<65, "A", IF(J3=65, "B", "C"))

  42. i need help on this its about paye(pay as you earn)
    want to work this out
    if B11 is greater than 25000 it must subtract 25000 from it and the remainder multiply by 30% and add 750, if less than 25000 but greater than 20000 it must subtruct 20000 and multiply the remainder with 15% and if its 20000 below it must do nothing

  43. 0.75 <= x < 1.25
    please some body explain it to me

    1. Hello Mehmood,

      In Excel formulas, you express this condition using the following AND statement: AND(A1>=0.75, A1<1.25)

  44. hallo

    i have token system
    how to use which formula for token calculate.

    value is
    150-600 is 1
    601-1000 is 2
    1001-2000 is 3
    2001-3000 is 4
    3001-4000 is 5
    40001-Above 6

    Please reply fast.

  45. Need to clarify the above question again

    i have to calculate c21*d21 to f21, if f21 value is equals to or less than "20" then f21 should show as "20" (20 is the minimum value) if the result is greater than "20" then the actual value should show in F21

    1. Hello Joy,

      Here you go:
      =IF(C21*D21<=20, 20, C21*D21)

  46. Hi Svetlana Cheushev,

    I have to calculate c21*d21 to f21, if f21 value is = 20 then f21 should show the actual value in F21

    can you help please.

  47. my previous post doesn't show everything I typed:

    I would like to have a well report a p value asterisk according to the following criteria:
    >0.05 "ns"
    less than or equal to 0.05 but greater than 0.01 "*"
    less than or equal to 0.01 but greater than 0.001 "**"
    anything <=0.0001 "***"
    Can anyone help?

  48. I would like to have a well report a p value asterisk according to the following criteria:
    >0.05 "ns"
    0.01 "*"
    0.001 "**"
    anything <=0.0001 "***"

    Can anyone help me write this function? Thanks!

  49. Hi i would like help with my formula
    If value1 is lower than 30 and value2 is lower than 30 equals 24,
    if not value1 is greater than 30 and value2 is greater than 30 equals 22,
    if not value1 is greater than 60 and value2 is greater than 60 equals 20.

    What i want is to check the size of something and give a range according to 2 measures, i mean if size is 24x28 the value is 24; if size is 24 x 40 the value is 22; if value is 60x24 the value is 20. It all depends on both sizes.

  50. Want to put age groups against age collected in a column. Was using if(A1>=20&A1<=25,"20-25","OKAY","NOT IN AGE LIMIT"). but not getting answer. Wanted to use nested if for more options.

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