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

  1. Help Please.
    How do I compare a cell value in a column range?
    ex. Col C1: C4 contains 5000, 5100, 5200, 5400.
    Cell D1 is 5300.
    Cell F1 formula : D1+40 is less than C1 or C2 or C3 or C4 say 0 else 1.
    Here I want the formula to check D1+40, which is 5340, in column range C1:C4 and if it contains a number greater than 5340, Cell F1 should say 0. If all are less than 5340 then it should say 1. I tried IF and countif separately and together and didn't get a result.
    Thanks,
    Ravi

  2. In one row I have set formula which calculate Sale per square foot
    In the same row their are some row empty as the sales is missing for upcoming date
    Now when I set formula to pick Min sale from the whole row its autmoatically calcualting zero as minimal value.
    I need help to set formula in which it should not take Zero value but only take value where their is more then Zero value and give me minimal sale

  3. Hi,
    If decimal in cell more than 0.20 = 0.50;
    If decimal in cell more than 0.50 = 1;
    If decimal in cell less than 0.20 = 0

    How to workout with excel formula for a cell to match the above 3 things in order to get the result ??

    Example :
    USD 134.63 = USD 134.50;
    USD 238.84 = USD 239
    USD 73.19 = USD 73

    1. Hey Lance,
      You can do this with the following formula (with your value in cell A1):
      =IF(A1 - TRUNC(A1) >0.5, 1, IF(A1 - TRUNC(A1) > 0.2, 0.5, 0))+ TRUNC(A1)
      Hope this helps!

  4. please help me on this--
    col.1| col.2| col.3
    A | A | SET1
    A | B | SET2
    B | A | SET3
    A | B | SET2
    C | B | SET4
    B | A | SET3
    C | B | SET4
    And so on...
    how can i get get different sets in col.3 according to data in col.1 & col.2.

    thanks

  5. how i set formula between 0-2000=100, 2000-3000=125, 3000-4000=150, 4000-5000=175
    so once i keyin 1800 it will show 100. and 2500 will show 125.

  6. Hi,
    I have a scenario related to this in my office, i just want to count all cells other than Null/spaces and "Not Contactable" through Sum product...how could i do so, Any idea??
    ----------------
    A
    Not Contactable

    A
    b
    d
    d
    f
    w
    w
    g
    s
    f

    Not Contactable
    Not Contactable
    Not Contactable
    Not Contactable
    Not Contactable
    Not Contactable
    s
    d
    t
    h
    s
    g
    s
    h
    r
    h
    sd
    f
    h
    d
    h
    d

  7. how do i write formula for...
    if c21 is more than 40, multiply what is more than 40 by 15.00

    1. So what happen if c21 is no greater that 40?. Well, below formula leave the c21 if it's not grater than 40
      =If(c21>40,c21*15.00,c21)

  8. if greater a1 less than or equals to 2 then e2 is 0.05 if a1 is greater than 2 then e2 is 0.03

  9. i have this excel formula involving sumif with 2 columns, the test column is comprised of one letter having 3 possible values in column, the sum column has numbers

    the formula is this =SUM(K13:K2005)/(SUMIF(J13:J2005, "", H13:H2005))
    j colums is the test column with the letters, h is the value colums added with the numbers

    my question is what "" means in this instance

    please help
    thank you

    1. Hi Cristian,

      "" means an empty string. In other words, your SUMIF function says: if a cell in column J is empty, then add up a number in column H in the same row.

  10. I need help please. I have a formula currently set up to calculate total hours, =((J18+TIME(0,30,0))-(H18-TIME(0,30,0)))*24. Now I need to set it up so that if that results in less than 3hrs for the cell to display 3 but if it is greater than 3 hrs to display the answer

  11. I want to some data in other sheet as greater than 1100 to less than 1500
    1000
    1100
    1200
    1300
    1400
    1500
    1600
    1450
    1375
    1890
    1225
    2550

  12. the criteria is
    0% = 15, Between 0% and 3% = 9, Greater than 3% = 0
    I tried the formula:
    =IF(Q3=0,15,IF(AND(Q3>0,Q33,0),0)))
    But it doesn't read the last argument which those that have more than 3% should be 0. It still shows 9.

  13. please anyone immediate help me. i have some trouble in one formula is that :-
    =IF(A1*0.2%-LEFT(A1*0.2%,2)>0.5,ROUNDUP(A1*0.2%,0),ROUNDDOWN(A1*0.2%,0))
    this formula is correct. but i want to add one more condition is that the result value not greater than 25. i want that (this formula + not greater than 25) how to combine both them.
    please please help me and if possible email me

  14. Hi,
    can you please explain how can i type greater than and less then symbol in excel formula or i have to download this symbol.
    Regards
    Abdul Wahab

    1. Hi Abdul,

      The 'greater than' and 'less than' symbols (also called angle brackets) are found on all computer keyboards. On U.S. keyboards, greater than (>) is usually located on the same key as the period and less than (<) on the same key as the comma.

  15. Hi there,
    I have 2000 rows of survey response data that is numerically coded : e.g. 1=Positive and 2=Negative. I want to enumerate the number of positive and negative responses. My thoughts are to use an IF equation that says IF the cell =1 assign it a one and if the cell =2 assign it a zero. Then I can enumerate the sum total number of positives and deduct from all responses to get the number of negatives at the bottom of the columns.

    Could you advise what formula I could use to get my numeric responses recoded as 1 and 0 instead of 1 and 2.

    Many thanks

  16. I want a formula for:
    If A1 is equal to or less than £50,000, then A2 = £60.
    If A1 is equal to or less than £100,000, then A2= £120.
    And so on and so forth...
    £150,000=£240
    £200,000=£360
    £300,000=£480
    £500,000=600
    £700,000=720
    £1,000,000=£840
    £2,000,000=£1,000
    £3,000,000=£3,000
    £5,000,000=£5,000
    <£5,000,000=£7,500

  17. Hello,

    I am trying to do a comparison between serial numbers. To simplify my case, I would like to compare two numbers and to know if 00001 is superior to 50000.
    The formula is =IF(00001>50000,2018,2019)
    The result that I obtain is wrong and always returns 2018. Is there a way to do compare these two numbers in excel?

    Thanks for your help on this.

    Kind Regards,

  18. I am very happy to find out solve my problem from your mathematical logical part.
    Thank you

  19. If B2 contains a particular text then it should update E2 as Alert(text), how to update this logic

  20. WOW! It did it again....

    1. Sorry for this. Our blog engine occasionally mangles formulas with "greater than" and "less than" operators, and our web-master can do nothing about it. Aargh...

  21. That is so weird - that bottom one is not the formula I wrote - it changed when I hit enter... It said: =IF(AND(B435>=1,B435=1,C435=1,D435<=5),1,0)

  22. It has been 20 years since I last took a boolean class...
    I have a simple basic combination of IF(AND( >= to problems for you....
    I am trying to make a simple entry to say: if the number in these three locations are ALL greater than 5 - then count it as one.

    =IF(AND(B439>5,C439>5,D439>5),1,0) -

    But here is the catch: I need zero to be counted as a ten place holder. So I need it to count all numbers in these locations which equal 6, 7, 8, 9, & 0.

    At the other end of the stick - I also need it to count everything greater than 0 and less than 6. So I need it to count all numbers in these locations which equal 1, 2, 3, 4, & 5. Ignoring the zero...

    But for some reason the combinations of Greater than and equal to refuse to give me the answer I need.... Help....

    Can't seem to ever find the right examples of multiple combinations of nested Boolean...

    1. I tried this - but it doesn't work:
      =IF(AND(B435=0,B435>=6,C435=0,C435>=6,D435=0,D435>=6),1,0)

      This does seem to work with the count 1 to 5:
      =IF(AND(B435>=1,B435=1,C435=1,D435<=5),1,0)

      Not sure what th difference is - but there must be one. The bottom one works and the top one does not...

      1. MANY MANY THANKS! I was trying all kinds of versions - even played with NOT commands nothing worked. Your solution is GOD send.

      2. Hi Steven,

        The difference is that the logical test in the first formula can never be TRUE because a number cannot be equal to 0 and greater than 6 at the same time :)
        You need a combination of OR and AND statements in this case:
        =IF(AND(OR(B435=0,B435>=6), OR(C435=0,C435>=6), OR(D435=0,D435>=6)), 1, 0)

  23. How can I conditionally format a cell if it requires 2 conditions to be meet. Also, both of the cells have to meet a secondary condition.

    example:
    Cell A1 to be formatted: Cell D3 must be greater than C3, and Cell T3 must be grater than cell S3.

  24. trying to come up with a formula stating if cell 1 = cell 2 and cell 3 then cell 4 is the answer

  25. =IF(I18500,I18-500*0.1+40))

  26. =IF(I18500,I18-500*0.1+40))

    Pls help

  27. 0-4, 7-10, 11-15 these are days
    SECURITY DEBIT BAL 0-4 7-10 11-15 16-20 21-25 26-30 31-60 >60 Payment
    264000 152717 152717
    519000 413382 2500 410882
    634000 2402376 703575 757620 776181 80000 70000 15000 835245
    433000 143055 143055

    hi excel we unable to less the one payemnt (835245) in diffrent coloums, kindly help. which formula i have to use

  28. Hi All,
    I need to create a formula Like. If cell2 value is greater than 50% of cell1 value. Like cell1 value is 20 and if cell2 value is greater than 10 (which is greater than 50% of cell one value) then it says yes. Kindly assist me.
    Thank you!

  29. Hi
    I'm trying to make a formula for if B3 is > 0= "Post Firm" , B3 is <0 = "PRE FIRM"
    Basically if its less than zero PRE FIRM, greater than 0 POST FRIM will populate.

  30. I trying to create 'conditional formating' to highight the cell red if it's not empty and lower than 3. However I got error when I "use a formula to determine which cell to format" as below:
    =$J5"",$J5<3

    I must have missed some basic rules and get the error. Looking for your kind assitance to advise the correct method.

    Thanks

  31. Dear Concern,

    Good Morning.

    I need a help regarding the issue.

    Amount TK
    1 to 400 5
    401 to 1500 10
    1501 to 5000 15
    5001 to above 25

    Thanking you,

    Kaisar

  32. how to round if Rs 7851.50/- is 'above' round to Rs.7860/- and round if Rs. 7850.40/- below round to Rs.7850/- in excel formula

  33. Formulas IF Cell A1 IS NOT EQUAL TO Cell B2 or Cell A1 is blank then it is to be considered as Cell C1 data

  34. Hi!
    Need Ur Help .
    IF cell A1 is having date is not equal to B1 having date or cell A1 is balnk then it is to be considered as C1 cell date

  35. Dear mam, I am a beginner for excel formula. My query is that i have many values in in column C in different rows. My condition is that to find out this value as minimum of 30 or equal to 30 but not less than 15 in column D. it's a value not true or false ie
    If in a row a value is 9 in column c, then come to 15 in column d, if it is 22 in column c, then come to 22 in column d and if 45 in column c, then come to 30 in column d. how and what formula, i can use for it.
    thanks with regards,
    JK Singh

  36. Hi
    My current formula is =IF(C21,D21/C21,"") what I need is if cell C21>D21by .50 then my percentage should be 100

    Ex.
    9hr worked / 8.50hr booked then 100% Completion

  37. Hello,

    How do i go about this; I am using this function =IF(I7=1,"NPE",IF(I7=2<2.9,"BAE",IF(I7=3<3.9,"AE",IF(I7=4<4.9,"SE",IF(I7=5,"EE",))))) and i am getting '0' as the 'value if true'. Pls help.

  38. My case is for my item analysis in school.

    E.g. 35 students in a class

    The table says if:
    75% above of the class size is M
    <75% to 50% of the class size is NM
    <50% below of the class size is LM

    Can you help me to create a formula for this case. Thank you.

    1. Hello, James,
      Please try the following formula:
      =IF(A1 >= 75%, "M", IF(A1 >= 50%, "NM", "LM"))

      You can learn more about Nested IF function in Excel in this article on our blog.
      Hope you’ll find this information helpful.

  39. Hi,

    I am trying to convert positive $ value to negative value if that positive value for an account number greater than 4 but less than 5. Can you please let me know what formula can I use to get that result?
    Thanks

  40. Hi,

    I have two results in excel: 37.9% and 29.8%, how to type formula to say that if both of them are within 1% then report mean, if the difference is greater then the preferred result is taken as lower of the two values.

    Thank you

  41. I have a data four cell in excel now i want to summation both of data but if i input one cell less then 9 or equal to any character the output will be zero. How can i solve this issue. plzz do the ans for the question.

  42. Hi Svetlana,

    First I want to mention that I respect if you do not have the time to help and since you actually are making an income working with Excel I understand if you do not are able to help out.

    I have searched on Internet "everywhere", but do not find the correct "programming" that works for me in my Excel sheet.

    To start with, I work with time and a sheet using 24 hour and beyond this. Time can start at 21:00 and finish at 28:00 or 14:30 ending at 25:20 (that is the data I have (working hours and the extra amount of money we get working late)).

    19-22 and 22-30
    The rules I try to set is to find time that passes 19:00 and ends before 30:00 (06:00). Also I want to know if how much time is between 19:00 - 22:00 and how much is between 22:00 and 30:00.

    Break (no extra payment)
    For an example working hours are 14:00-23:30. Then we get three hours of extra payment between 19:00-22:00 and a higher amount of money between 22:00-23:30. But we do not get paid that extra money, below I have added a break between 18.30-19:30.

    First I want to extract the 19:30 into one cell, 22:00 to the next, after that also 22:00 separately and at the end 23:30.

    This is how I want it to look...

    C2-14:00 Work starts (actual data)
    C3-23:30 Work ends (actual data)
    C4-18:30 Break starts (actual data, no payment from this time)
    C5-19:30 Break ends (actual data, no payment to this time)
    C6-19:30 Start time of lower payment (=IF... ) to get this result
    C7- 22:00 End time of lower payment (=IF... ) to get this result
    C8-22:00 Start time of higher payment (=IF... ) to get this result
    C9-23:30 End time of higher payment (=IF... ) to get this result

    Also to make sure the formulas work if the working hours are between 16:00-25:02 break at 21:20-22:40, 21:30-29:50 break at 25:00, 24:01-31:00 or 23:40-28:00 breaks embedded at any time...
    I have tried this, but could not make it work.

    Will this take to much time to write formulas that makes this work?

    Best regards

    Tom S

  43. Hi Svetlana,

    First I want to mention that I respect if you do not have the time to help and since you actually are making an income working with Excel I understand if you do not are able to help out.

    I have searched on Internet "everywhere, but do not find the correct "programming" that works for me in my Excel sheet.

    To start with, I work with time and a sheet using 24 hour and beyond this. Time can start at 21:00 and finish at 28:00 or 14:30 ending at 25:20 (that is the data I have (working hours and the extra amount of money we get working late)).

    19-22 and 22-30
    The rules I try to set is to find time that passes 19:00 and ends before 30:00 (06:00). Also I want to know if how much time is between 19:00 - 22:00 and how much is between 22:00 and 30:00.

    Break (no extra payment)
    For an example working hours are 14:00-23:30. Then we get three hours of extra payment between 19:00-22:00 and a higher amount of money between 22:00-23:30. But we do not get paid that extra money, below I have added a break between 18.30-19:30.

    First I want to extract the 19:30 into one cell, 22:00 to the next, after that also 22:00 separately and at the end 23:30.

    This is how I want it to look...

    C2-14:00 Work starts (actual data)
    C3-23:30 Work ends (actual data)
    C4-18:30 Break starts (actual data, no payment from this time)
    C5-19:30 Break ends (actual data, no payment to this time)
    C6-19:30 Start time of lower payment (=IF... ) to get this result
    C7- 22:00 End time of lower payment (=IF... ) to get this result
    C8-22:00 Start time of higher payment (=IF... ) to get this result
    C9-23:30 End time of higher payment (=IF... ) to get this result

    Also to make sure the formulas work if the working hours are between 16:00-25:02 break at 21:20-22:40, 21:30-29:50 break at 25:00, 24:01-31:00 or 23:40-28:00 breaks embedded at any time...
    I have tried this, but could not make it work.

    Will this take to much time to write formulas that makes this work?

    Best regards

    Tom S

  44. I need a formula that figures out how many entries in a range and then divides by that about of entries. Example if A1= 0, A2, = 3, A4=5 then divide by 2.

    Thank you for your help. I actually have 7 entries - 1 for each day of the week, and want to divide only by the days that actually have a number greater than 0. So what I need is the sum of the 7 days then divide by only the number that had an actual value greater than 0.

    Thank you!

    1. Hi Pauline, I hope you already received a response, but if not here's my answer. You would use the Countif function. It would look like this:
      =COUNTIF(B1:B3,">0") the function looks at the cells B1 to B3 and returns a number between 0 and 3 depending on how many of the cells have a value higher than 0.

  45. Que. If A1>B1 & C1=D1 then bonus will be (A1*0.002), but If A1>B1 & C1=D1 and F1>G1 & H1=I1 then bonus will be ((A1+F1)*0.002*2). Please suggest correct formula to be used?

  46. If I got many data to lookup, how to enter the data by using if formula?

    Ex: =if(A1>=1000,900,if(A1>=900,850........and so on)

    Please assist me, thanks.

  47. i need a formula for a completion time for each department for example IF any department goes 20 min ahead or behind produce which is F2 highlight Red if all departments are going to finish within 20 min of each other highlight Green my range is F2-F8

  48. Hi,
    I have a set of number in a columns from 12 to 30 & I want result for them in 2 word
    that can be more than 15 or less than 15. Is there a way I can get a formula result for these number. thank you

    1. Abdul:
      If I understand your question an IF THEN OTHERWISE statement will work for you. Where the first number is in A2 enter this in B2:
      =IF(A2<15,"Less Than 15","More Than 15")
      It says, if the value in A2 is less than 15 then display "Less Than 15" otherwise display "More Than 15".

  49. I have a column that has taken the difference between two columns.

    I want to extract any variances greater than 10K (so more than 10K or less than negative 10K) AND greater than 10% or less than negative 10%

    I just want it go return that number if both conditions are met - or return a blank

    What is the easy way to look at any difference more than a certain amount - whether that be negative or positive?

  50. Hi there,

    trying to write an excel formula for the following:-

    if a1 is greater than or less than b2 by more than 5% then colour cell? Any help would be greatly appreciated.

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