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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Thank you

  31. Hi

    I got an excel sheet that contains data of soil moisture content (h), Field Capacity (hfc) permanent wilting point (hpwp) and evapotranspiration (ETp)
    in order to calculate actual evapotranspiration (ETa), the following condition has to be build in excel

    ETa = ETp when h ≥ hFC
    ETa = ETp * ( h-hWP / hFC - hWP ) when hWP< h < hFC
    Et = 0 when h ≤ hWP

    Thank you

  32. Delivery Time Receving time

    08-08-2016 13:15 08-08-2016 10:00
    12-08-2016 15:02 10-08-2016 13:20
    15-08-2016 18:05 15-08-2016 14:40
    25-08-2016 15:05 20-08-2016 9:55
    30-08-2016 18:03 28-08-2016 9:55
    1.delivery Time always greater then Receiving time(Date Also) then nothing count.

    2)If delivery time below 1 hour compare to Receiving time then count it.
    3)Delivery time and receiving time gap is 1 hour no issues but below 1 hour and above should be count.

    it means that i need all variable conditions please help me.

  33. HI,
    I'm trying to create a formula to ask(if a1=1 & b1=2 return the contents of b16. Please help.
    Thank You

    1. Hi Frank,

      Here you go: =IF(AND(A1=1, B1=2), B16, "")

      1. Thank You Svetlana for the swift reply.
        I have 10 variables in cell A1 and 3 variables in cell B1.
        How do I include any variation of them in cell B16.
        The 10 & 3 variables are already in a drop down list
        The formula in your reply works well for a single variation.
        Thank You again

  34. Hi,
    I am new to the world of excel and I am trying to figure out how to do the following:

    If value in cells A1:A9 are between 500 and 3000 then total the figures and multiply by cell B to give me the total sum figure. I can't figure this out. can anyone help please?

  35. i want same number in 2 different cell and also if i edit one cell value the other should automatically happen.
    How should I do it????

    you need to be use a formule Equal2

  36. I have multiple lines that have different surcharges bases off of over all weight. I am looking to have the cost read in a cell when weight is in that range and be 0.00 when out of the range. Below at 501# price should be 16.25 and the 10.00 should now be 0.00.
    $10.00 501 $10.00 1-500
    $16.25 501 501-1000
    $25.00 501 0.00 1001-1500

  37. Hello,
    I need a formula that looks at a cell and determines the following :
    If the cell is less than 1 or less than -1 then return the text "balanced" ... otherwise return "not balanced".
    Thanks so much,
    Wayne

  38. hi
    if cell A 700 then cell B indicates Thousand amount so 0,Cell C indicates hundred amounts like 7(cell a=700) Cell D indicates 00
    If u have any formula to divided cash amount in following format
    1245=1,2,4,5

  39. i want same number in 2 different cell and also if i edit one cell value the other should automatically happen.
    How should I do it????

  40. Hi,

    I have two columns of data. The 1st is the actual arrival time, and the second is the ETA. A customer can arrive 30 minutes either side of their ETA time, i.e ETA of 11:00-11:30 - customer can arrive between 10:30-12:00. I need a formula that will identify all customers that arrived outside of their ETA window.

    Can anyone help???

    11:17:57 a.m. 11:01 - 11:30
    9:58:36 a.m. 09:31 - 10:00
    7:39:17 a.m. 07:31 - 08:00
    11:31:42 a.m. 11:01 - 11:30
    8:24:52 a.m. 07:31 - 08:00
    9:48:32 a.m. 09:31 - 10:00
    2:20:45 a.m. 02:01 - 02:30
    10:34:43 p.m. 22:01 - 22:30
    10:07:01 a.m. 10:01 - 10:30
    10:30:45 a.m. 10:31 - 11:00

  41. i am trying to fix a formula for if multiple cells value to-gather if values are positive

    trying like this =SUMIF(AN16,AP16,AR16,AT16,AV16,AX16,AZ16,BB16,BD16,BF16,BH16)">0"

    but not working .. unable to set correct formula for it

  42. IF(C11>=Sheet1!I4,Sheet1!$I$4:$I$43)

    Hi
    how to used formula in excel two coluam for greater than but not equal to

  43. Good Day Ma'am,

    I am trying to get a cell to produce 60 if a "material code" entered in one cell is CS and checks against the thickness of the material. So if the thickness is greater than 0.034, or less than 0.135, or equal to 0.5 and the "material code" is CS the cell gives me a 60. However I also have another cell that will check the "material code" for CS and the thickness of the material to be less than 0.034, to produce 48. The problem is if I have 0.03 in the thickness cell I get a 48 and a 60. Do you have any ideas to go about this? Thank you for your time and help.

    Sincerely,
    Andrew Edwards

    1. Hi Andrew,

      For us to be able to help you better, can you post the current formula you are using?

  44. i was trying to compare the data in the particular cell, I used 'if' calculate the out come.
    i.e If the value in cell is greater than or equal to '4' the result should be '30' in display cell, else (any character or letter or 0 in cell) it should be '0'
    I tried this, =IF (A1>=4,"30", "0") it is working out for numeric value but when any letter or any special character is fed to the comparison cell it gives out "30" as results.
    would you help to explain, why it is working out so?

  45. Need help figuring out how to write a formula in Excel 2016.
    I need to have the following formula's answer round up to 3 if it is less than 3.
    =SUM((B16/12)*(C16/12))

    1. Hi Sara,

      Try this one:
      =IF(SUM((B16/12)*(C16/12))<3,3,SUM((B16/12)*(C16/12)))

  46. Hi, very interesting thread thank you.
    Just wondering how would I show the cell value instead if true/false. Currently I have =IF(F15<40,)
    But i have 4 different categories I have 0 to 39, 40 to 56 ect...I want to say if the value is less than 39 show cell value if not show 0.
    Thanks in advance!

  47. Can you help me with this. Date that less than 60 days write as "LESS" more than 60 days write as "MORE". Thanks

  48. Can you please give me a formula for depending on which date is greater than another. 180 days will be added to the greater than date. Thanks!

    1. Hi James,

      You can use a formula similar to this:
      =IF(A1>B1, A1+180, B1+180)

      Where A1 and B2 are the dates to compare.

  49. I just want to rounded up to next 10 but will remain neasest integer if the unit place is 0.As for example: 187.6.1 will 190 and 120.3 will 120.

    1. Hi Puspen,

      Try this one:
      =IF(RIGHT(TRUNC(A1,0), 1)*1<>0, CEILING(A1, 10), INT(A1))

  50. How to check 2 values for 50 % difference?

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