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

  1. Hi, this is probably an easy one for you but I can't get it right. If the cells A2-A11 have the value of B2 to display the value in C2. If the cells do not have the value from B2 the cells should be blank. I have the formula =IF(A2:A11=B2,"C2"," "). What am I doing wrong?

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(ISERROR(MATCH(B2,A2:A11,0)),"",C2)

      Hope this will help.

  2. Hello Team,

    I have Name in Column A, Mahesh, Suresh, Kailash, i.e. &
    in column B I have count of their process order count i.e. 20, 30. 40, 0

    I have 4 sheets (1-Dec, 2-Dec, 3-Dec) which is mentioned their name

    I want total individual count in 5th Sheet by their name

    Condition, it should not count if there is zero value, i.e. "0"

    1. Hello,

      If I understand your task correctly, please try to do the following:

      1. Supposing that your cell A1 on the 5th sheet contains one of the names: Mahesh, Suresh, Kailash, etc.

      2. Enter the following formula in cell B1 on the 5th sheet:

      =IF(OR(VLOOKUP(A1,'1-Dec'!A:B,2)=0,VLOOKUP(A1,'2-Dec'!A:B,2)=0,VLOOKUP(A1,'3-Dec'!A:B,2)=0,VLOOKUP(A1,'4-Dec'!A:B,2)=0),"",(VLOOKUP(A1,'1-Dec'!A:B,2)+VLOOKUP(A1,'2-Dec'!A:B,2)+VLOOKUP(A1,'3-Dec'!A:B,2)+VLOOKUP(A1,'4-Dec'!A:B,2)))

      Hope this will help you with your task.

  3. Hello Team,

    I have Name in Column A, Mahesh, Suresh, Kailash, i.e. &
    I have in column B count of their process order count i.e. 20, 30. 40, 0

    I have 4 sheets (1-Dec, 2-Dec, 3-Dec) which is mentioned their name

    I want total individual count in 5th Sheet by their name

    Condition, it should not count if there is zero value, i.e. "0"

  4. Hello Team,

    I have Name in Column A, Mahesh, Suresh, Kailash, i.e. &
    I have count of their process order count i.e. 20, 30. 40, 0

    I have 4 sheets (1-Dec, 2-Dec, 3-Dec) which is mentioned their name

    I want total individual count in 5th Sheet by their name

    Condition, it should not count if there is zero value, i.e. "0"

  5. Dear Sir,

    I have two columns of text, I understand how to find exact matches through =IF(A2=B2, "Match","No Match") but how can I find partial matches. Such as A1 = "Patient has Sepsis" and B2 = "Sepsis" how can I still report this as a Match when the text is not an exact 100% match? If any text in A1 is found in B1 it should be reported as a Match.

    Thank you for your help,
    S

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(LEN(SUBSTITUTE(LOWER(A2),LOWER(B2),""))<>LEN(A2), "Match","No Match")

      Hope it will help you.

  6. Dear Sir/Madam

    Please solve the Problem of the Calculation 0.40 How to convert to 1.00

  7. kindly tell me how to write this in excel:
    1)greater than 20 and less than 25?
    2)greater than or equal to 20 and less than or equal to 25?

    1. Hello,

      If I understand your task correctly, please try the following formulas:

      =IF(AND(A1>20,A1<25),"yes","no")

      =IF(AND(A1>=20,A1<=25),"yes","no")

      Hope it will help you.

  8. Hi,

    I am trying to apply a formula to find the greater then:
    C2 = 335
    B2 = 337.5/330.2

    =IF(C2>Right(B2,Find("/",B2)-1),"True","False")

    The output is not matching to the logic i am looking for as it giving "False"

    Could you please help me?
    Thanks

    1. Hello,

      Please try the following formula:

      =IF(C2>VALUE(RIGHT(B2,LEN(B2)-FIND("/",B2))),"True","False")

      Hope it will help you.

  9. Hello,

    I am trying to enter formula into excel to match below:
    > 95% equals 5
    90% - 94.9% equals 4
    85% - 84.9% equals 3
    75 - 85% equals 2
    <75% equals 1

    Could you pleased help me?
    Thanks

    1. Hello,

      Please try the following formula:

      =IFS(A1>95%,5,A1>=90%,4,A1>=85%,3,A1>=75%,2,A1<75%,1)

      Hope it will help you.

  10. Please i need a formula for the statement bellow
    if value in cell A6 90 & 100 & 110 & =120 print 100%.

    I write is like this =IF(10090,"80%",IF(110100,"90%",IF(120110,"95%",IF(I6>=120,"100%","0"%)))) but is not giving me what i need.

    Please I will appreciate your input.

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(A6>=120,"100%",IF(A6>=110,"95%",IF(A6>=100,"90%",IF(A6>=90,"80%","0%"))))

      Hope this will work for you

  11. Gross Pay Deduction
    Up to 5,999 150
    6,000 - 7,999 300
    8,000 - 11,999 400
    12,000 - 14,999 500
    15,000 - 19,999 600
    20,000 - 24,999 750
    25,000 - 29,999 850
    30,000 - 34,999 900
    35,000 and above 1000
    How to calculate by using IF formula in Excel please help

    thank you

    1. Hello,

      Please try the following formula:

      =IF(A1>=35000,1000,IF(A1>=30000,900,IF(A1>=25000,850,IF(A1>=20000,750,IF(A1>=15000,600,IF(A1>=12000,500,IF(A1>=8000,400,IF(A1>=6000,300,150))))))))

      Hope it will help you.

  12. i know this is probably a simple formula that i am just drawing a blank on but i need some help please. if cell A is not equal to $4 i need cell B to make up the difference. But if cell A is equal to or higher i need nothing to show up, cant have any negative numbers.

    more information to help you: cell A is already formulated to be the sum of several other amounts, but if that cell does not equal $4 i need an adjustment cell to make up the difference.

    1. Hi Arlene,

      If my understanding of the task is correct, the following formula should work a treat:
      =IF(A1<4, 4-A1, "")

  13. Why is it that I am getting a 0 value, when I've used the if condition as mentioned above i.e. =IF((C20>C18), C20-C18,C18).

    while I try to get just the greater than value between 2 cells, I get the same 0 value. Is there a mistake in my formula? =C20>C18

    Kindly help

    1. Hello Shery,

      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  14. I've done this heaps of times before but for some reason I'm struggling today. So, if I want to check if apples <= 10, then flag as"Too Low" or if oranges <= 20, then "Too Low" or if Pineapples <= 30 then "Too Low" The fruit names are in one column and the amounts in the other. I'm trying to string it all together and flag. If anyone can tell me that would be great.

    1. Hello, Vicki,

      Please try the following formula:

      =IF(AND(A1="apples",B1<=10),"Too Low", IF(AND(A1="oranges",B1<=20),"Too Low",IF(AND(A1="pineapples",B1<=30),"Too Low","")))

      Hope it will help you.

  15. This did not help at all and it is all wrong

  16. I have two cell to compare and use the smaller value.
    =b12 b13
    b12=2,500
    b13=1,500

  17. How can we say, IF 250>a value>=200 then the output would = 200

    I tried IF(250>A1>200,200,b7) but that doesn't seem to work

  18. hello..just want to know how can I get the excess night differential if employee worked 8:00am and out 16:00 but he extends his worked to 0:02 the other day, how can I get the night different if it’s start in 10:00pm to 12:00am, I can’t get the total of 2hrs in excess of night differential…thanks

  19. Hi,
    Please Help.
    I've got an array of value's in Colom B, the values are determined by a simple =C1/12 formula.
    But now I want it to do the following.

    If value is LESS than 1, it should change it to 1.
    If value is equal to or greater than 1 is should stay unchanged.

    Is this possible?

  20. Hello Svetlana Cheusheva!

    Your efforts to help students/employees concerning excel formulas are really appreciable.

    I need to know formula for the below statement:
    If we have a column having total marks of students like 60,75,25,115,150,250 etc and I want to show the grades int he next column in front of these values their grades like if less than 50 it is Fail, greater than 50 it is Fair, greater than 100 it is Good, greater than 200 it is Excellent.
    Kindly guide me to write formula.

    Thanks

    1. Hi Sheraz,

      This can be done with a classic nested IFs formula like this:
      =IF(A2>200, "Excellent", IF(A2>100, "Good", IF(A2>50, "Fair", "Fail")))

      1. thanx alot! :)

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

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

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

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

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

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

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

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

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

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

  31. Figured it out thanks anyway

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

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

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

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

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

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

  38. GOT IT

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

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

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

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

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

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

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

  45. hello There,

    try this..

    IF(J1860,0,J18))

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

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

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

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

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

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