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

  1. I'm trying to create multiplication math worksheets for young student. I can easily generate random numbers between specified values but I would like to always have the greater value as the top number. Young students get confused if their multiplication problem looks like this:
    2
    x15

    How do I randomly generate a number between specified values that will be greater than a randomly generated number in another cell?

    Thanks

  2. I need to know about the formula of below statement.

    count if cell value equal to (-,blank,0) also
    i need contact # like @@@-@@@@@@@ in a cell if value or digits missing then cell not allowed.

    Thanks

  3. I need to be able to input a number into cell A2 and have that compare to cell A1 value - if the value of the 'new' number is greater than the compared value, i need to add the difference to the existing value - example: i enter 150 in A2, which is greater than 25 in A1, but the difference is 125 - i want to add 125 to the value in A1 and keep the entry in A2, I have tried the IF in various forms with no luck, would appreciate any help.

  4. I need to have a formula for this,

    if the difference between a3 and b3 is less than 800,the results is true if the absolute value of the (a3 and b3) is 200. And if the the difference between a3 and b3 is greater than 800, the results is true if 30 percent multiply by the difference of (a3 and b3) otherwise false

  5. I need to get a result for the following;

    Example1:
    Value X = 1.2 (This value can change)

    Value Y = 5

    Result;
    X1.5<2=0

    Example2:
    Value X = 1.6 (This value can change)

    Value Y = 5

    Result;
    X1.5<2=Y which is 5

  6. MY TASK:

    My calculator description:

    Condition: Minimum fee 70 EUR.

    1 employee = 5 EUR

    1 operation = 1 EUR

    1 cash register service 40 EUR (not included in the minimum fee)

    Example:

    The number of your company's employees

    3 work * 5 EUR

    Issued invoices and invoices

    10 pcs * 1 EUR

    Invoices and invoices received

    5 pcs * 1 EUR

    Advance payment checks

    15 pcs * 1 EUR

    Cash registers

    1gab * 40 EUR

    Monthly service fee *

    15 + 10 + 5 + 15 = 45, but not less than 70 EUR, plus cash register 40 EUR. Total amount of 110 EUR

    Help to write formula for this please

  7. How do use a in excel to identify a certain text in another cell?

    Example:
    Column A has "42A"
    Column B has "31B/42A/88M"

    I want to say if column A is in column B than Column C display "Qualified"

  8. i have a very confusing question with.
    Question:
    if Housing is < 15,000=10,000 else 15,000

    NAME BASIC SALARY HOUSING ALL. FEEDING ALL.
    MUSA 300,000 60000 if Housing is < 15000=10000 else 15000
    JOHN 150,000 27825
    IBRAHIM 150,000 26250
    JAMES 200,000 33740
    STEPHEN 200,000 35300
    please check and give me a nice answer need this for an exam.

  9. Here's the formula I'm trying to build:

    An employee who works the "night" shift (any number of hours between 8pm and 8am) is paid $10/hour. The employee working the day shift (8am-8pm) earns $20/hour. I have 4 columns: "time in", "time out", "hours worked" and "rate of pay/hour". How should I write the formula that populates the "rate of pay" column based on the shift and number of hours worked?

    Thank you!

  10. Dear all

    This is the Name sentance SHERBHADURKHAN , this three name SHER BHADUR KHAN how we can spacing in excel sheet through formula , please help me.

    Thanks

  11. Figured it out thanks anyway

  12. I meant >=16 then.........

    1. Hello, David,

      it looks like you could use conditional formatting with IF function.

      You can read more about the rules of conditional formatting in this article.
      If you don't know how IF function works, feel free to learn its usage here.

      Hope you'll find what you need!

  13. I am looking to be able to have a statement that will end result in a highlighted box. I want to be able to say- If (A1*B1*C1).= 16 then.................

  14. Hi Thanu, Not sure your reply is for my question... If so, then have no idea what your response means. Remember, I have basic knowledge of working with formulas for excel. I'll try again...

    Cell 'H2' should only carry the total hours from cells 'A2:G2', which in the example above has 42 hours. I just wanted a way for cell 'H2' to only reflect 40 hours of the 42, and in cell 'I2' reflect only the 2 remaining hours. This is the only way I can explain what I am after. Thanks

  15. Hi,
    I hav 1 doubt, in the cell value is greater than 100, then "a". If the value is less than 100, then "b". If the value is lesser than or equal to zero, then "c". How can i create formula for this...

  16. Hi, I have basic knowledge here, so please help... I have 7 horiz. cells 'sun thru sat' (A1:G1) that will each have the total hours worked that day (A2:G2), then I will have the combined total of those 7 cells in the very next cell 'H2'(=SUM(A2:G2). What I want to do is get the number hours in cell 'H2' that are greater then 40 into the next cell 'I2'. Can you please help with that formula... Thanks
    A B C D E F G H I
    1 sun mon tue wed thu fri sat hrs OT
    2 9 8 0 9 0 8 8 42 2

  17. Hi,

    Column D E
    1 C1
    2 C2
    3 C3
    4 C2
    5 C3
    6 C4
    7 C5
    8 C6

    I want to put the value in column E if C1=10, C2= 20, C=30, C4= 40, C5= 60, C6= 70. What will be the formula??. I tried by using IF(OR fomula but failed. Would you help me?

  18. GOT IT

    =IF(A67=B67,C61,IF(AND(MATCH(A67,A63:A69,0),MATCH(B67,A63:A69,0)),C61,""))

  19. I have a formula that determines if a date is within a date range.
    I want it to be TRUE IF the date falls between a selected start and end date, as well as IF they start and end date is the same date.
    I'm using the AND function, to determine if it's equal to or greater than the start date, AND equal to or less than the end date.

    =AND('Form Responses 1'!$A8>=$A$4,'Form Responses 1'!$A8 and <, correct?

    1. not working

  20. Dear I need Your help.
    i have a query in excel.
    if A=B the value come from c. but if A not equal to B, but if cell A and B contain any value from below list then value also come from cell c.
    list:- skt, lyp, lhe, khi

    example:
    A = SKT
    B = SKT
    C = 10
    it is easy FORMULA=if(a=b,c,"")
    but if
    A = SKT
    B = LYP
    C=10
    Both value are from list. therefore result should b c.
    FORMULA:??

    kindly advice formula if possible.
    looking for goods response

  21. Cumulative Random Number
    665 4,680
    1,584 13,031
    2,995 21,382
    3,658 29,733
    4,089 38,084
    5,253 46,435
    5,687 54,786
    6,676 63,137
    7,381 71,488
    8,116 79,839
    8,793 88,190
    11,253 96,541
    13,331 104,892
    13,918 113,243
    14,369 121,594
    How can i find random number equal or just greater than 4,680 in cumulative column by using formula?

  22. Hi

    I would like to write a formula in a cell1 to show that any value equal to or less than cell 1, written in cell 2

    Could some one help!

    Thanks

    Harry

  23. Hi,

    I need a formula where I can Min percentage of different cells but ignores the zeros. For example, D4=89%, D15= 70%, D25=0% and D36=60%. How do I get the Min from this?

    Thanks,
    Di

  24. Hi, I have one sheet where i want to calculate total GST based on percent.

    for example :

    1 Brass Nozzel 7419 1000 10.00 28 10000.00
    2 brass Nipple 1000 100.00 18 100000.00
    3 Brass Valve 1000 100.00 28 100000.00
    4 Brass Cap 1000 10.00 28 10000.00
    5 Brass connector 1000 10.00 18 10000.00
    6 Brass Reducer 1000 100.00 18 100000.00

    here i want total for all amount for 28% in single formula
    and total for all 18% amount in another formula.

    Please help me if possible.

    Thnks in advanced.

    Regards,
    Amit

  25. hello There,

    try this..

    IF(J1860,0,J18))

  26. I need a formula for the below statement:

    If J4 is between 30 and 60 then enter the number in G4. If not enter 0

  27. Dear fren,

    can u all help me for this formula? even the result below -15% and above 15%, its follow the condition at range 10%.

    =IF(AND((-10%)>=Z10=Z10>(-10.001%)),"NEED IMPROVEMENT",IF(AND((-100%)<=Z10<(-15.001%)),"BAD",IF(AND(1%<Z10<=10%),"GOOD",IF(AND(10%<Z1015%,"BAD",FALSE))))))

    thanks

    1. (-0.001%) to (-10%) GOOD
      (-10.001%) to (-15%) NEED IMPROVEMENT
      >(-15.001%) BAD

      0% to 10% GOOD
      10.001% to 15% NEED IMPROVEMENT
      >15.001% BAD

  28. I don't know if this is possible but I want to get a cell to fill a certain color if another one is equal to or less than a certain number and vise versa i.e. if D5 is equal to or less than 84% than B5 will fill a certain color. Thanks.

  29. I am trying to find out a formula for this one
    eg :
    1st 2nd 3rd 4th 5th 6th 7th
    21.80 61.40 120.80 200.00 299.00 417.80 556.40

    if answer has come greater than 300, value should '0'
    in this eg 6th & 7th one should 0.
    if it is come less than 300, value should true value, like 21.8, 61.4, 120.80
    so please help me

    1. 1st = 21.8
      2 = 61.40
      3 = 120.8
      4 = 200
      5 = 299
      6 = 417.8
      7 = 556.4

  30. Hi Friends,

    I am facing a problem when I write a formula in Excel.
    The data: when value is upto $200,000 then 3% of the value, more then $200,000 but upto $1,000,000 then 4% and more then $1,00,000 upto $10,000,000 then 5% of the value, how can I write the formula? please help me. its urgent. thanks to all

  31. HI MADAM

    1 - 5m3= 1Sample
    6 - 15 m3 = 2Samples
    16 - 30 m3= 3Samples
    31 - 50 m3= 4Samples
    51 m3 and above = 4 plus one additional Sample for each additional 50 m3 or part thereof

    HOW TO INPUT VALUE FOR PARTICULAR CUM QTY IN EXCEL

    (IF(AND(I120),6,IF(AND(I125),9,IF(AND(I1215),12,IF(AND(I1230),15,IF(AND(I1250),18,IF(AND(I12100),21,(IF(AND(I12150),24,"VALUE")))))))))

    I TRIED ABOVE WAY I DIDN'T GET RESULT

  32. =(IF(AND(I120),6,IF(AND(I125),9,IF(AND(I1215),12,IF(AND(I1230),15,IF(AND(I1250),18,IF(AND(I12100),21,(IF(AND(I12150),24,"VALUE")))))))))

    IN THIS FORMULA RESULT VALUE NOT GETTING BLANK IS SHOWING
    TELL ME SOLUTION FOR THAT ....

  33. =(IF(AND(I120),6,IF(AND(I125),9,IF(AND(I1215),12,IF(AND(I1230),15,IF(AND(I1250),18,IF(AND(I12100),21,(IF(AND(I12150),24,"VALUE")))))))))

    THIS FORMULA RESULT NOT GETTING TELL ME SOLUTION FOR THAT ....

  34. Hi

    100
    200
    300
    400
    500
    600
    700

    I wnat formula for 100 at time up to 1000

  35. how can we write this in excel formula "All student whose mark is more than 200 and less than 250 label it as “average” ".

    1. Hi Sheryl,

      we'll keep it simple-
      Assume Column A is the students name and Column B contains their grade.

      in C1 type;
      =IF(AND(D3>200,D3<250),"Average","")"

      Hope this helps!

  36. HI,
    I am making a spread sheet for conditional formula with if function.
    if A10=250 and B10= opc (or Any text) then write specific number in third cell C10
    Example: A10 = 250 , B10 = OPC or (Any text) , C10 = 170

    Formula =if(A10=250 & B10=OPC,"170","0")

    Actually i have cement Weight 250 in Cell A10 and Cement Type in OPC in Cell B10 Now i want that if i write 250 in Cell A10 and OPc in Cell B10 Then Right Price exact 170 in Cell C10
    can you give me formula or any solution plz.

      1. Thanks its works

  37. Hello, I need help to compare two cells like this. Want to compare cell B1 to A1 and soo on and I need to count every equal result, every A smaller then B and every B smaller then A.

    A B Miks S = 1 Result
    Miks M = 2 A = B = ?
    Miks M Miks L Miks L = 3 A B = ?
    Miks S Miks S
    Miks L Miks M

    1. A B
      Miks M - Miks L
      Miks L - Miks M
      Miks S - Miks S
      Miks S - Miks L

      Miks S = 1
      Miks M = 2
      Miks L = 3

      Need to count every equal, smaller and bigger.
      A = B = ?
      A B = ?

  38. Hello! I have read through many of the comments and have a question:

    I am working on a spreadsheet and if the number of years is equal to 0, I need it to be a value, if the number is between 1 and 5 I need a value, if it is 6 to 10 I need a value and if it is 10 to 15 I need a value. Can you help me figure this out?

    =0 = $10.50
    1 to 5 = $10.75
    6 to 10 = $11.15
    10 to 15 = $12.75

    Thanks so much

    1. You have to use the AND function, so something like =IFS((A1=>1)AND(A1<=5)),10.75), etc.

      1. I have a similar issue but I need it to be if zero equals A5, if 1-5 equals B5, if 6-10 equals C5 and 10+ equals D5.

        Can I do the ranges of numbers/years and have the formula grab the answer from calculations in the other cells?

  39. I have one problem please help me

    i have 2 value cell D6 value & E6
    if value D6 >= E6+(E6*0.5%) answer is E6
    if value D6 <=E6-(E6*0.5%) answer is E6
    if value D6 < E6-(E6*0.5%) answer is S6

    thanks & regards
    Niranjan

  40. I have the following formula and Excel continues to give me errors. Help, please!

    =IF(B126>20,"A",IF(AND(B126=0),"B",IF(AND(B126(-20)), "C"), IF(B126<(-20), "D")))

    1. Hello, Matthew,

      first of all, you don't need to use AND function because you specify only one criterion after it. Then, you missed the logical operator in B126(-20): should B126 be equal to -20? Also, there's no need to use round brackets for negatives in this case.

      So, try this formula:
      =IF(B126>20,"A",IF(B126=0,"B",IF(B126=-20,"C",IF(B126<-20,"D")))) Please note that you didn't specify what to return when B126<0 but greater than -20; when B126=20 and when B126>0 but lower than 20. Logically the formula requires these criteria too.

      But I do hope that the formula above is what you were looking for :)

  41. Hi Svetlana,

    Scenario:
    F8 = 1
    D10 = 0
    E10 = 7

    I want to create an IF formula to ask if F8 is greater than D10 but less than E10, then return a value of 1. If not, then return a blank.

    I created the following:
    IF(F8>D10<=E10,1,"")

    but this returns a blank instead of 1. I am not sure why.

    I am sure it is probably quite simple, but can't seem to solve it - I would be grateful for your help.

    Many thanks!

    1. Hi Aron,

      you need to use AND in the condition of your formula. Try the following:
      =IF(AND(F8>D10,F8Excel IF tutorial.

  42. Hi,

    I have a problem if somebody can help me it would be very appreciable. I had wrote many formula but did not get results.

    We have data like:

    Age Actual Value Exempted Value
    50 30000
    50 14500
    50 25000
    60 30000
    60 19100
    60 35000

    We want to get results:
    Age Actual Value Exempted Value
    50 30000 25000
    50 14500 14500
    50 25000 25000
    60 30000 30000
    60 19100 19100
    60 35000 30000

    Note: There are upper cap for 25000 60

  43. Hi,

    Need help on this'

    1-24 = A
    25-30 = b
    31-48 = c

    This is my formula but not working
    =IF(A1=25,A1=31,"C")))

    many thanks,
    necie

  44. Hi Guys
    some one help me for fixing up this issue,
    formula for:-
    If the amount is less than 50,00,000 then its 0%, if the amount is between 50 lakhs and 1 cr its 10% and above 1 cr its 15%.

  45. Hi can someone help me in this.
    I need to add numbers between given two numbers
    Eg.
    if I give first number 18 and last number 20 in different cell
    the number should generate Automatically in this sequence
    18.25
    18.50
    18.75
    19.00
    19.25
    19.50
    19.75
    20.00
    the sequence should stop to given last number.
    please help me out
    thank you

  46. Hi,
    Can someone assist me with the following request please.

    In Column A1, if someone types or pull the following hours of data from the list (8,16,24,32,40,48,56,64,72). The max is 72 hours.

    Column B1 should provide the following:
    1. Remaining hours left

    Column C1 should provide the following:
    1. Percentage of hours left from 100%
    2. Color scales of stop light (Green, Yellow, Red)

    I hope that I was able to make a sense of my request! Thank you in advance!

  47. I am seeking assistance on a formula: If C3-B3 is greater than 51% then E14 is C3-B3*.10

    If 100-35 > 51, then 65*.10

    Thank you in advance!

  48. Hello,
    I'm trying to setup a formula within the conditional formatting. If i have two cells that have the same name in them, then I'm hoping to have the conditonal formatting kick in and shade the a cell a certain color. Where I get stuck is getting the formula to refer to the conditional formatting instead of giving me a text output as the result.

    For example, =IF(AND(A1=Bob,A2=Bob),X,X) Where X represents the instruction to shade or not shade a cell.

    I know I can put a formula in a conditional format rule but not sure how to make the true/false variables refer to the shading.

    Any advise/help would be appreciated!
    Thanks,
    Corey

  49. The formula is:
    IF((D3="Men"),(E11>19,E1115,E11<25),RED,NO FILL)

    Thanks for help provided.

    John

  50. i have a sheet with a drop down list of "Men" and "Women". i have put the following IF formula based on the criteria of BMR between the range for Men >19 but 15 but 19,E1115,E11<25),RED,BLUE)

    I am getting all wrong somewhere. Please rectify the same.

    Thanks in advance.

    John Sanil

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