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

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

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

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

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

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

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

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

  8. Hi,

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

    thanks

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. hello mam,
    when we can use
    if(exact(cell 1,cell 2),"true value","false value")
    formula or drag the mouse than both cell value increase but i want only cell 2 value increase or cell 1 stable.

    thanx

    1. Hello BRIJESH,

      To fix a cell reference, put the $ sign in front of the column letter and row number, like $A$1. For the detailed explanation about absolute and relative cell references, please check out the following tutorial: Why use dollar sign ($) in Excel formulas

  32. please help,

    can u give the formula,, my problem is, 1f the cell A is less than .5 the result should be, "NOT CORRECT" statement, but if cell A is higher .5 the result is CORRECT

    1. Hello Axel,

      Here is the formula:
      =IF(A1<5, "NOT CORRECT", "CORRECT")

  33. Hi Svetlana,

    I need to put formula for below condition
    IF A Scores 0-19% ,He gets -20 Marks
    A Scores 20-39%,He gets -15 Marks
    A Scores 40-50%,He gets -10 Marks.

    Kindly help me out with formula.

  34. IF (E11:E:26<299999,"1500",600000,"5000")
    Not sure why it changed my formula

    1. Hello Heather,

      Our blog engine sometimes mangles formulas in comments, sorry for that.

      As for the IF formula, write it for one cell (E11) rather than for a range, and then copy the formula down to other cells. If you use relative cell references (without the $ sign), Excel will properly adjust the formula for each row. So, here goes the formula:

      =IF(E11>=600000, 5000, IF(E11>=300000, 3000, 1500))

  35. I'm trying to figure out a formula in excel. I'm trying to say if E11:E26 is 299,999 and under then place 1500 in cell, if E11:E26 is 300,000-599,999 then place 3000 in cell, if 600000 and over place 5000 in cell. I can't get equation.. This is what I have
    IF(E11:E26=300000,"3000",>600000,"5000")

    Where am I making the mistake?

  36. hello
    I need a pretty complicated formula.
    We deal with traveling nurses and their hours and housing.
    I'm needing to know how to calculate if cell A is equal to or great than cell B then cell C stays the same. But is cell A is less than cell B then cell C will be lowering.
    So, if cell A is less than B the equation we use (on paper lol) is A/B=Total then we take the total x C = The actual total I'm needing

    1. Hello Liz,

      If my understanding of the task is correct, you can use the following formula:
      =IF(A1>=B1, C1, A1/B1*C1)

  37. I want salary sheet in this format e.g when we write its designation its daily wage rate is automatically fullfilled.
    WE insert computer update
    computer operator 500
    Clerk 400

  38. I have different value in colum A, I like to put a formula in colum B1 if A1 below 70 than show "a" if between 71 to 90 than show "b" and above 91 than show C

  39. If Prepayments' column is greater than 0 (zero), then less prepayments amount from Payables cell Total of next month.

    how this will be performed in excel 2010?

  40. I want to compare 2 cells means suppose if A1 < minus 10% and B1 < minus 2% then it should give me result "ABC" else "XYZ"

    Can somebody please help me with the formula for this.

    Thanks in advance

  41. how do i use if formula for the below
    underweight <18.5
    normal weight 18.5 to 24.9
    overweight 25 to 29.9
    obesity BMI of 30 0r greater

  42. Hi,
    I need your help. I am trying to create a formula to compare two columns - column A & B. If Column A is greater than Column B, then I'd like the cell in column A to be highlighted. I've tried to create the formula using conditional formatting, rules, "IF" and I've made no progress. Does it matter that the values in column B are the result of a formula? Help!?

  43. Hi, I need a formula. if AP column is equal to 0, "FULLY PAID", if AP column is equal to D column, "UNPAID" and if AP column is not Zero, "PARTIALLY PAID"

    Thanks! it will be a great help

  44. i need fomula
    A1>=0 B1>= 0

    i want to add A1& B! both to display in C1
    condtion : to show the value once enter in the A1 or B1 only.
    =IF(A1+B1>=0,A1+B1)
    no need false option. i need to show value 0 in C1 only when i enter A1 or B1 =0

  45. I have formula applied for true false if greater or less but if the result is in negative it is saying true but in actual it should false, please suggest

  46. i have formula for pre vat calculation for exampal dealer price 10000/- then after show the pre vat amount

  47. Hi,
    How can I find the difference between two columns using the if function?
    Thanks and Regards,
    Sarfaraj

  48. i m trying to create a formula for postal charges

    where

    1) postage upto 5000

    2% of the amount recovered or Rs 50 whichever is more

    2) above 5000

    Rs. 100 + 1% of amount exceeding Rs. 5000

    regards

    Mukesh
    7358027745

  49. How will I use "IF" formula for the following condition,
    if less than 1.49 print 1
    if greater than 1.49 and less than 2.49 print 2
    if greater than 2.49 and less than 3.449 print 3 ...and so on...

  50. Any idea in how to perform this equation with these conditions in Excel

    Thank you

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