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 →

Table of contents

Comments page 16. Total comments: 1256

  1. The score - 21-16 = 5, 16 - 21 = -5 ok but I would like to put "D" instead of blank when the score is draw (21-21)

    Thanks in advance

  2. If cell a has 5 and cell b has 4 I want to know if cell a is greater then cell b …….BUT only if it is only great then 1

  3. Hi everybody,
    I am using a simple Excel function
    IF(A5=B5,"OK","False")

    up to a certain amount it says OK but if the amount of A5 and B5 goes above a certain amount it says False although they are the same.

    For examole:

    A5=500 B5=500 OK
    A5=600 B5=600 False
    A5=700 B5=700 OK

  4. Hello Mam,

    I would like to know how to formula for date value is equal or greater than or less then in two cells.
    example:
    03/04/2017 03/04/2017 true
    03/04/2017 03/04/2017 true
    03/03/2017 03/04/2017 greater than
    03/06/2017 03/04/2017 Less than
    03/05/2017 03/04/2017 Less than
    03/08/2017 03/03/2017 Less than

  5. Hi, Need help!

    If Cell A is grater or less than cell B by 10 units say "check" if not say "ok"
    So would look like:
    if A1 = 42 and B1= 42 then "OK"
    if A1=53 and B1=42 then "check"
    if A1 = 31 and B1 = 42 then "check"

  6. Greetings,
    Can someone explain to me how I would interpret this?
    I understand the TEXT and the RIGHT syntax, but how are the 4 cells being compared? What is the result?
    =TEXT(RIGHT(F12,2)>RIGHT(F21,2)>RIGHT(F32,2)>RIGHT(F41,2))

    Thank you!

  7. I need a solution for this
    I want to add 1 to cell h1 if value in cell k11:k2000 is greater than 0.

  8. Can we apply a formula greater than equal to when i have dates in two different rows.

  9. Hi

    Im looking for a formula that if
    A2 or A3 are less than or equal to 3 but also greater than or equal to 1, D8 will equal 125. If either one is greater than 3, it will equal 0.

    Please help :) Thank you

  10. Hello,

    I need help with a formula. I need it to say the following

    If SUM A1:A15 >= 20,000 Then return 5% of SUM A1:A15
    If 10,000 <= SUM A1:A15 < 20,000 return 4% of SUM A1:A15
    If SUM A1:A15 < 10,000 return 3% of sum

    How do I do this???

  11. Hi
    I need formula if I travel >=100 kms, it should calculate as Rs 600, If it's <100Kms it should consider Rs 350, please help

  12. Hi there! I'm trying to do what I thought was something simple. I have a column that has 4 possible text inputs: T1, T2, T3, T4. I want the cell to be red if the text is either T3 or T4. How do I set that up? I can easily do conditional formatting if I use just *one* text input, but I can't figure out how to do it with an OR operator. I appreciate any guidance, from anyone!

  13. Hi Svetlana

    Can you help?

    If CELL A1 is greater than Value 75, show Value 4.50

    Any ideas?

    Many thanks in advance.

  14. Hi

    Need a formula

    if we cosider 3 cells A1, B1, C1

    lets suppose c1=100

    i want to have A1+B1=100(c1) all the time

    if i change A1 to 60 then B1 should automatically display 40
    and if i change B1 to 30 then A1 should automatically change to 70.

    please help me out.

  15. Hi
    Please help. How to make condition monitoring with 2 conditons:
    Column Z5 = RELMNC (text)
    Column AA5 = 10 (number)

    In column AA5 I want :
    if left Z5;3 = "REL" , then value >=7, color fill Red
    if left Z5;3 = "REL" , then value <=7, color fill Green

    Thank you.

  16. 1 A B C D
    2 Date1 Date2 Date3 Date4
    3 1/23/2017 1/22/2017 1/21/2017 1/20/2017
    I want to retrieve the status if A>B, B>C, C>D, "OK", ELSE "NOT OK"

    PLEASE HELP ON THIS.

  17. by using excel work sheet

  18. I have the question;

    how to calculate attendance percentage of student who sit to all course assessment and semester exam

  19. want a formula for .

    if A >= B , IN 90 DAYS , THEN GOOD OR BAD .

    PLEASE SOME 1 CAN HELP PUT THIS IN A FORMULA .

    REGARDS,
    MARK

  20. Hi Svetlana,

    If I wanted to perform a mathematical function on a "<" less than number, what would I enter for the formula? e.g. <0.5 * 0.002 = <0.001

  21. A B
    1 0
    -1 0
    0 0
    13 1
    5 0.5

    I need the usage of if condition like above answer
    if less than 5 = 0
    if greater than 5 and less than 13 = .5
    if equal to 13 = 1

  22. Hello,
    I have some Amount like as 20,50,70,100,1000,2000,5000,10000. I am calculating with 10% for each data,but i want (%) value will not less than 10 and not more than 100. How can it possible?

  23. Hi,

    How to write in if formula that a cell is greater than 31/12/2015 and less than 01/01/2017. I can't use IF(AND function because I already used AND function for another argument. Or is there any way to specify a particular time period in IF function?

    Thanks in advance.
    Bharat

  24. Hello,

    I have a little problem. I have the following formula:

    =COUNTIFS(Doigahama!H5:H214,"=0",Doigahama!P5:P214, ">=0")

    However, the result I get is as if formula said only

    =COUNTIFS(Doigahama!H5:H214,"=0",Doigahama!P5:P214, "=0")

    The > is ignored. If I change the order of the signs to =>, then the = is ignored and I get only the values over, but not including 0. How can I fix this? I was searching for advice, but everywhere I look it seemed that the way to write greater or equal was like that.
    Just in case i also tried using ≥, which did not work either, it was not recognised at all.

    Any advice would be welcomed.
    I am using Office 2010 with Windows 7.

    Thank you,
    Sincerely,

    Florencia

  25. Hi Svetlana,

    Need a formula based on below criteria:
    I have two cells, A and B,
    if the difference btw A & B is less than 6 or equal to 6, "No Change"
    if greater than 6 and less than 9, "Slight Change"
    if greater than 10 "Significant Change"

    Also the formula should override the difference with positive numbers, even the B is larger than A, vice-versa

    Please help!!
    Reply

  26. Hi Svetlana,

    Need a formula based on below criteria:
    I have two cells, A and B,
    if the difference btw A & B is below 6 or equal to 6, "No Change"
    if above 6 and below 9, "Slight Change"
    if above 10 "Significant Change"

    Also the formula should override the difference with positive numbers, even the B is larger than A, vice-versa

    Please help!!

  27. Hi, i need help...is there a formula where i can add a number if a cell is bigger than another cell by a certain number EXAMPLE...

    if A is greater than B by 2 then add 3, if A is greater than B by 3 then add 5

    Also is there a formula where if C contains "draw" add 1, if C contains "win" add 2

  28. Hi,

    i want automatic sort out the all values less then 9.8 an other sell. so plz tell about formula.

    thanks

  29. Hi,
    I need the formula to show the output below

    0-29,999*7%= ?
    any amount over 30,000*8%= but does not include under 30K amount
    thanks

  30. Excellent article. I was struggling to figure out why I was getting the false value when I was using TRUE in quotations "TRUE". I didn't realize TRUE and FALSE behaved differently. THANK YOU!

  31. Need help. Creationg daily budget. Need formula to deduct from previous day IF I exceed that day's limit. I want that previous day's value to filter to the next day IF the value is less than zero. Any suggestions?

    Thanks,
    Brian

  32. Hi, i am trying to find a formula in which if a specific value is greater then another (if A1>B1 ), it will input how much larger the value is in another cell ( if A1 > B1 by 5, it will input 5 in C1 for e.g. )

  33. Hi,
    can you help to sort.
    if the cell (B2 or C2)= zero value then move it to B3 & C3.
    if the cell (B3 or C3)> 0 value then move it to B2 & C2.
    (like largest to smallest)
    for Exm:
    Apple 0 0
    Mosambi 3 12
    Watermelon 0 0
    Banana 0 17
    Orange 4 8

    expected as below

    Mosambi 3 12
    Orange 4 8
    Banana 0 17
    Apple 0 0
    Watermelon 0 0

  34. Hello,

    Could you please help me with the following IF function:

    In cell F9, use an IF function to test if the value in cell D9 is less than or equal to 8. If this condition is true, then multiply cell D9 times cell E4 (the regular rate). If the condition is false, then multiply 8 times E4 and add to that E9*E5 (the overtime rate). Make sure you use absolute cell references in the formula where needed.

  35. hi,

    im working on excel and in 1 cell i have 3digit number example 345 then i want only to disply in cell is last digit only 5.

  36. Hi,

    I have 3 columns of numbers that I need to compare, ideally they need to be equal to but there is some leeway.

    I'd like to be able to say if A1 is equal to A2 and A3 TRUE, but also if A1 is within 10% of A2 and A3 also TRUE.

    Is this possible?

    Thanks,
    Hannah

  37. hello,

    I'm working on excel and the formula I need to come up with is "a function to calculate the number of days that are equal to or below 25"

  38. I'm working on this formula to where column "C" contains a value of 8 chars for example "6246FQ76"

    =IF(LEFT(C2,4)>6274,0,IF(N2="CANCL",IF(F2="Y",(I2*E2)*-1,(J2*E2)*-1),IF(F2="Y",I2*E2,J2*E2)))

    I'm trying to create a formula to where if the left 4 values of C2 is less than 6274 then put a zero, if greater than use the formula. the formula works without the IF(left). any recommendations? Thank you.

  39. I'm not sure about this problem, I can't decide what the answer is. If Jeremy is selling m of his models on the Internet for $12.50 each, then which one would describe the amount he will be paid? Is it a)$12-m. or b)$12.50 divided by m. or is it c) m times 12.50. PLEASE HELP I'M SO CONFUSED

  40. Hi,

    I have these commission Payout % :
    10% Of revenue for reaching target
    12% Exceeding target by ≥ 10%
    14% Exceeding target by ≥ 20%

    for example if the :
    Revenue = £2,474
    Target = £2,000
    To Target % = 124%

    How can excel automatically calculate "Commission payable" using the "If FUNCTION" ?

    Thank you a lot for your help.

  41. Hi, im trying to get the criteria for this: i want to get the number of ranges that falls in 31-60 days. Or should i say "less than or equal to 60 but not less than 31" ???????

    Formula: COUNTIF(ranges,"___________")

    Hope you can help me.. Thank you very much.

  42. Svetlana,

    I whant to compare and take the value of a cell, but this have a restriction,

    where

    A "If the material is +45kg or -100kg = $2.50/kg"
    B "If the material is +100kg or -250kg = $2.00/kg"
    C "If the material is +250kg or -500kg = $1.5/kg"

    My bulks have weight as:

    Bulk A = 89 kg
    Bulk B = 184 kg
    Bulk C = 349 kg

    How can I use a formula that can take the correct value/kg?

  43. I need a formula where a retainer payment is less than $5,000, the referral fee would be 25%; if the retainer is more than $5,000, then the referral fee would be 33%.

  44. Hi,

    Need some help:
    Want to leave H3 blank if K3 is less than 1.
    Want to use calculation =K3*1.2-G3 if K3 is greater or equal to 1

    What I have so far:
    =IF(K3>+,"",(K3*1.2-G3))

    1. Hi Derek,

      Try this one:

      =IF(K3<1, "", K3*1.2-G3)

  45. Hye,
    How if I want put two string condition? I'm using this [=AND ($Q9="Open" + $I9="A")] but its not working.

    refer to: 3. Logical operators in Excel conditional formatting

    1. Hi Aqilah,

      Here's the correct syntax:

      =AND($Q9="Open", $I9="A")

  46. Hello

    i have a some Data and i want to achieve this formula can you please help.

    if Cell A1="A,B,C,D" and B1='B,C,D,A then C1="Pass" Else Fail )

    thanks

  47. Having trouble getting a formula to work that will give me an outcome for a number between two values, tried this but no luck, what am I missing:

    =IF((A1<=100),11,0)+IF(100.1<=A1<=140,12,0)

    I want it to show "11" if the number in A1 is less than or equal to 100, I want it to show 12 if between 100.1 and 140.

    Thanks.

    1. Never mind the last post, I figured it out.

      Thanks,
      Avon

      1. Hi I too am trying to solve this may I ask how you did it

        Thanks

        1. Hi Chris,

          You can use nested if's, like this:

          =IF(A1<=100, 11, IF(A1<=140,12,0))

  48. Hi All,

    I have the following problem:

    A2=First name, B2=Last name, B3=Full name - for example:

    A2=Joe, B2=Hart, B3=Joe Hart

    What i need is if B3 contains B2 = OK - unfortunately i can only find the exact formula but not an partial recognition.

    Hope you can help me somehow. Thanks

    1. Hello Joe,

      The IF function does not recognize wildcard chars, therefore you have to use isnumber/search like this:

      =IF(ISNUMBER(SEARCH(B2,B3)), "OK", "")

  49. If column C1=B1 then show the value of C1. How is this done?

  50. I am trying to subtract 1 from time if the sum is greater than 5.. So I am using as example =SUM(G5-F5)*24-1 now I only want to subtract 1 if the number is greater than 5. Essentially I am doing a schedule that will calculate hours worked and will automatically subtract 1 hour for a lunch if the person has worked 5 hours to get a lunch..

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