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

  1. Here we go - I have a simple formula that I am using:

    =IF(F2>=K2,"yes","no")

    What I need is to make K2 a constant. I've tried making it an absolute (ABS), but I think I'm just not putting it in the right place in the equation. One, it's a fixed number in the spreadsheet, and two, it will prevent the formula from going to K3,K4,K5,etc as I pull it down.

    Any help would be great.
    -PK

    1. Hi!

      Why not simply use an absolute reference? :)

      =IF(F2>=$K$2,"yes","no")

    2. Aha! I got it figured out. I was trying too hard. Thanks, though!

  2. how do I find matches between column's. column a contains 5000 items and column b contains 6000.

  3. Its working
    Thanks.

  4. Hi,

    If A5 is less than 10 then value become in B5 should be 0
    And if If A6 is 11 then value become in B6 should be 50
    And if A7 is 12 then value becomes in B7 is 100.

    Please help.

  5. Hi,

    I have used the "or" function to test missing fields, with a output of True or False. True if required fields are entered and False if any of the Fields are missing. I would like to know if there is way excel can List which fields that are missing which result in the False output

  6. I want to learn a formula for
    if A1 is greater then B1 then show the result in C1 and but A1 is less than B1 than 0 or Nill will show in C1

    i am trying
    =if(A1>B1,A1-B1,Nill)
    But its not working

    Could you help me out.
    Thanks In Advance

    1. Hi Noor,

      Please put the text value in your formula in double quotes or replace it with 0, i.e.:

      =IF(A3>B3,A3-B3,"Nill")

      Or

      =IF(A3>B3,A3-B3,0)

      Hope this will help.

  7. Good day

    What is the formula for: if F2 is smaller than or equal to 40 then subtract 5 from F2 and if F2 greater than or equal to 50 add 10 to F2.

    Regards
    M

  8. Desperate for help. Have a spreadsheet in which complaints have been given numerical value. How do I get the actual text value to come up in a new column?

    Complaint
    1 Broken Tube (pre-meal)
    2 Broken Tube (post-meal 1st hr)
    3 Broken Tube (post-meal 2nd hr)
    4 Broken Tube (post-meal 3rd hr)
    5 Broken Tube (post-meal 4th hr)
    6 Patient Nausea
    7 Missing Spirulina/Egg
    8 Missing Mixing Cup
    9 Missing Straws
    10 Missing Utensils
    11 Missing Crackers
    12 Missing Water Cup
    13 Missing Test Req Form
    14 Unpleasant Taste
    15 Patient Vomited
    16 Prescription/Medicine Interaction
    17 Data Transfer Issue
    18 Gastro Intestinal Issues (burping)
    19 Broken tube return shipment (Multiple)
    20 Barcode Mismatch
    21 Missing Return Label
    22 Missing Breath Tube
    23 Discrepency In Reporting

    So that if a line item notes for instance that complaint number 22 was issued, then in cell K3 Missing Breath Tube would show... and exclude all others.

  9. =IF(J8>89,”A”,IF(J8>79,”B”,IF(J8>69,”C”,IF(J8>59,”D”,”F”))))

    is this formula correct?

    1. Hello, Colin,

      Yes, your formula looks absolutely correct.

      If it doesn't work, then please send us your workbook with the data and formula to support@ablebits.com. I kindly ask you to shorten the table to 10-20 rows/columns and include the link to your blog comment.

      We'll look into the issue and try to help.

  10. Dear sir
    Que.1

    A<b (A*3/100)

  11. How to tell following criteria in if function

    Employees who are in grade 1 are subject to 1% of basic salary
    Emoloyees in grade 2 are subjected to 2% of basic salary
    Employees in grade 3 are subjected to 3% of basic salary

    Calculate the service cost for each vehicle using the “Logical IF” considering table below. (All values in LKR)

    Car Van Lorry Bus Truck Motorbike
    Full 3500 4000 5000 5500 6000 1350
    Wash 500 500 750 800 750 350

  12. Hi, can you assist me to find the formula as under

    I want to create a formula: if B1 is greater than 44000 but B1 is less then 46000 then computer show the result (in Excel) as 45200

  13. how do i say this in one formula for cell E9

    IF THEN E9

    E8>80 THEN PASS
    E8<80 THEN FAIL
    E8=inc THEN INC

  14. 816
    357
    492

  15. sorry in my previous..... if number 4 comes..and then 6 ,in orden the rows and columns to be equivalence the next number should be 5...So the formula must choose!that number that makes columns and rows equivalence

  16. Imagine a magic square 816
    357
    492
    and there is a lucky wheel which is rotating and contains those 9 numbers.What i need is to count each column and row.(for ex.if number 8 comes i put 1 on first row and first column,if then 4 comes i put 2 at first column and1 on first and 1 on third...so is like counting rows and columns:))T
    The question here is the Equivalence!!.I want aformula that understand the equivalence.(for example if anumber for comes and then 5,in order to have 111 in arow and 111in columns what the next number should come is number 5,yes?so ineed the formula to understand the equivalence andCHOOCE that square.

    thank you
    George

  17. Hello,

    We are using Letter "M" to indicate monitoring in 1 column. At the bottom of the column we use =COUNTIF(R2:R1515,"m") to count the number of projects monitored. This shows a number of 813. When I select the column from R2-R1515 usuing mouse the number shows 816. I have made sure all "M" are M and not M with a space. What am I missing? How do I correct?

  18. Please can you assist me to find a formula as follows:
    I'm using symbols like OD for Off-day and A for Absent in a cell above the daily rate cell. When you type in OD or A above the daily rate cell, the daily rate should change to zero. When you type in PL for Paid Leave or SL for Sick Leave the daily rate stays the same. I also fill in the hours worked in the symbol cell with the daily rate that should change to the amount of hours worked.

    1. Hello,
      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.

  19. I have a question. I want an Excel sheet to give me this.

    In my cell I am subtracting A5-A6. If the answer is less than 60, I want to return 0. If it is 60 or more, I want the correct subtraction. How do I do that?

  20. ="=<"C3

    What does it means

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

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

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

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

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

  26. Dear Sir/Madam

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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