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

  1. Help please!
    I need help with greater than and less than?
    I have a solid number that does not change however week by week that data entered could be more than or less than the solid number??? I need it to return a positive or negative difference(number)?
    The formula Ive tried if =IF(B1B6,(B1-B6)) it works but only one way? I have tried rules,
    thanks im overwhelmed :)

  2. Please help!
    I want to mark a cell "pass" when the score is equal to or above 14, and "fail" when the score is lower than 14.

    I cannot figue it out :(

    1. Hi Magda,

      You can use a formula similar to this:

      =IF(A1>=14, "pass", "fail")

  3. Hi SWARUP,

    You can use a nested IF formula similar to this:

    =IF(A1=421, 420, IF(A1=429, 430, IF(A1=431, 440, IF(A1=443, 450, ""))))

  4. Pls help me in getting the formula that

    if A1=421 than in A2 it should reflect 430
    if A1=429 than in A2 it should reflect 430
    if A1=431 than in A2 it should reflect 440
    if A1=443 than in A2 it should reflect 450

  5. Original 12/15/2014, New Date 05/15/2015, so the date that i want in my revise column is the new date, but there will be multiple new dates that supersede the previous new date

    1. Mic,

      I think your original formula =IF(G87>E87,G87,E87) is correct. It will always return the more recent of the two dates, no matter how many times the dates in the specified cells are updated.

  6. disregard the last part

  7. Sorry for the confusion, I'm making a spread sheet with a original date with multiple revisions, I want to be able to put in a date and let excel determine if its the present date, In the Revisions through column will have multiple dates for example:

    DATE Rev. up to With Revisions Through
    12/15/14 05/15/15 05/15/15=IF(G87>E87,G87,E87)

  8. Hi,

    If i wanted a previous date to supersede a early date in multiple cells how would i format that, I have the following:=IF(G87>E87,G87,E87) but cant figure how to format multiple dates that will supersede the previous update date, if i have a newly updated date.

    =IF(05/15/15>12/15/14,G87,E87)

    Thank you

    1. Hi Mic,

      Sorry, I am not sure I fully understand the question. If you are asking how to format the returned date the way you want, you simply apply the desired date format to the cell containing your formula, as demonstrated in the following tutorial:
      https://www.ablebits.com/office-addins-blog/change-date-format-excel/

      If you are asking about something different, please clarify.

  9. Hi, kindly how to write this function:

    IF A1>=0.3 then "Good"
    IF A10.1 then "Fair"
    IF A1<=0.1 then "Poor"

    Thank you

    1. Hi Meedo,

      Here you go:
      =IF(A1>0.3, "Good", IF(A1>0.1, "Fair", "Poor"))

  10. if the sales crossed the required amount means then addition it must automatically go to next cell for that what i have to do (Eg: Batch 1 =1500 nos, Batch 2 = 2000 ok if the sales crossed 1500 means it has to go next cell like batch 2)

  11. hi,
    Vodafone Airtel Idea Market Leader
    17% 15% 18% Idea

    i would like to knwo th formula.. if in all the three values, which ever is greater like idea is 18% in all the three, so the answe will automtically comes idea. Greater values shows the text. plz reply

  12. Hi i know how to use this logical operators my problem is

    i want the result is in number value

    for Example in excel:
    A B C D
    1 Amount Days 31-120 121-180
    2 20,000 175 =A2= ans. True
    3 10,000 119 =A3= ans. False

    I want answer to be like:

    A B C D
    1 Amount Days 31-120 121-180
    2 20,000 175 =A2= ans. 20,000
    3 10,000 119 =A3= ans. 0

    Hoping you could find the solution

  13. here is what I came up with accidently, its working but I am not sure where I got the "False" from, what does that mean?

    =VLOOKUP(B4,'SUBWAY MOVEMENT'!$A:$H,8,FALSE)

  14. Thank you so much Svetlana for your help thus far, your work on this forum have been amazing.

    1. Hi Tim,

      Thank yo so much for your kind words.

      It's difficult to suggest a proper formula without seeing your data, but I think VLOOKUP is the right function for your task.

  15. Hi Svetlana,

    if I have a spreadsheet with values in comumb one, but I have another tab that has information from 1-1000 with data, rather then looking at 1000 cells to find the matching data for one cell in columb one, how can I set up a formula to search all 1000 cells on the separate tab, to find the matching value, then add the requested information into the selected cells? make sense? I am having trouble trying to articulate this.

  16. Hi, I need a formula for the following:

    If today() is greater than a date in a cell by 4 days, return a result

    thanks for your support.

    1. Hi Irshad,

      Here you go:

      =IF(TODAY()- A1>4, value_if_true, "")

      1. Dear Svetlana,
        Thanks a lot.It works great when I replace the value_if_true with a number, e.g =IF(TODAY()-A954>4,1,2).

        But how do I replace the 1,2 by text?
        I did this, =IF(TODAY()-A954>4,TEST,TEST FAILED), but it doesnt return the text. I get #NAME? as result.

        Sorry to be a pain and thanks again for your support.

        1. Hi Irshad,

          Just enclose the text in double quotes, like this:
          =IF(TODAY()-A954>4,"TEST","TEST FAILED")

          1. Hi Svetlana,

            Thanks loads for your help and support!:)

            Best Regards

  17. Hi

    I am trying to colour code using conditional formatting. My example is:

    Student A has a target grade of "B"

    I want it to be:

    Hits target e.g. B = Light Green
    Above target e.g. A = Dark Green
    Below target e.g. C = Orange
    2 below target e.g. D = Red

    Can this be done with conditional formatting and if so, how?

    Thanks

    Gareth

  18. im trying to figure out a formula? if cell a1 is less than 13 the value in b1 is 26 if its greater than 13 the value is 24 any help would be great!!

    1. Hi Steve,

      And what if A1=13?

      Anyway, you can use the following formula:
      =IF(A1<13, 26, 24)

      And you can replace A1<13 with A1<=13, if needed.

  19. Svetlana, you've been a huge help on this forum. Thank you so much for all you've done.

    I'm trying to draft a formula to find the number of items that were created on or after a certain date (Held in Sheet1, column N) but not resolved before the date held in Sheet1 column Q. A list of every day of the year is listed in column B, with B3 starting at 1/1/2015, B4 at 1/2/2015, etc.

    e.g. We're needing to track the number of bugs that are in an active state each day, as well as track the number of bugs in a resolved state every day. Column N holds the created date of every bug and Column Q holds the resolved date of every bug. I know how to do a countif to determine if a bug's creation date was the same as the date in column B, but I can't figure out how to make it check if it's still open.

    1. Hi Aaron,

      Thank you for your kind words.

      Does an "open case" mean no date in column Q or the date in column Q is less than today's date?

      If the former, you can check column Q for empty cells, e.g. =COUNTIF(Q3:Q100,"<>"&"")

      If the latter, you can compare a date in column Q with today's date:
      =COUNTIF(Q3:Q100,"<"&TODAY()) You can find more formula examples in COUNTIF in Excel - count if not blank, greater than, duplicate or unique.

      1. Svetlana, thank you for the help. No, it means that the date each case is opened is in column N (N3:N100000)and the resolved dates are in column (Q3:Q100000). I'm needing to find for every day since January 1st how many cases are open on each day and how many are resolved on each day, so we can see how quickly we're reducing the case load. So I need to find, for each date (listed Sheet3!B3:B1000) if a bug was created on before that date, but not resolved (because if it's created January 2nd but not resolved until January 31st it's still active from Jan 2-Jan30), or if it was created on or after that date and also resolved on or after that date.

  20. =IF(G26<=A14,A14-G26,"MET") I'm trying to get my formula to say MET if its = to. But it only reads Mets if I'm a penny or more over. what am I doing wrong?

    1. Hi Walter,

      Since you are using the "less than or equal to" operator in the logical test, the formula reruns the difference A14-G26 if G26=A14. If you want it to return "met" in this case, replace "<=" with "<" like this: =IF(G26<14, A14-G26,"MET")

  21. i want to calculate incentives and i need to find a value such as
    if A1>B1 minus with D1 if the result is equal/less than D1 come back as 0, and if result is greater than D1 minus with D1 and come back as positive and multiply by 150.

  22. E2 = 23% and M2=-23% I need a formula to highlight a tolerance. If M2 is +5% over E2 it's orange, if M2 is +10% over E2 it's green. If M2 is equal to E2 it's just clear. If M2 is -1% or less it's RED.

    Can anyone help out, it's driving me nuts

    1. Upon a second thought, that actually makes no difference :)

      You can create 3 conditional formatting rules, say for M2:M1000, with the following percent change formulas:

      Orange:=AND(($M2-$E2)/$E2>=5%, ($M2-$E2)/$E2<10%)

      Green:=($M2-$E2)/$E2>=10%

      Red: =($M2-$E2)/$E2<=-1%

      1. THANKS! worked perfectly

    2. Hi Edd,

      Do you have amounts of percentages in E2 and M2?

  23. Svetlana, Question, is it possible to add formulas to a tab on a spreadsheet?

  24. how do i take a blank cell, and have that cell equal to the large dollar amount of 4 other cells? so if i have 4 cells with different dollar amounts, i want the blank cell to know to choose the larger amount of the 4 cells to be the value? how do i do this?

    1. Hi Tim,

      You can use the MAX function similar to this:
      =MAX(A1:A4)

      1. Ahh, did not think about that MAX formula. can I call myself an excel wiz... Thank you so much Svetlana

  25. I want to link a document so that if it is greater than today it =A4 and if it is less than today it =A3. Say the document with A3 and A4 is called pizza. What would this formula look like?

  26. I am looking for a little help with excel please.

    I have two columns (ex: E and F) in which I need to find all rows in which column E is greater than F.

    Is there a formula for finding this?

    ANY help would be greatly appreciated!

    Thank you

    1. Hi Maria,

      To count such cells, you can use an array formula similar to this:

      =SUM(--(E1:E100>F1:F100))

      Remember to press Ctrl + Shift + Enter to enter the array formula correctly.

      To highlight such rows, you can create a conditional formatting rule with the formula =$E2>$F2 where row 2 is the top-most row with data.

  27. I found a SUMPRODUCT for a cell (E8), the Total Cost, using two separate arrays. One that I identified by cell range numbers (B8:D8) the other I identified using the cell range by name (Cost, which was cells B3:D3). I got my numbers for column E just fine, but now I am looking to write a function for the column F that calculates the shipping. I want to find the shipping for each Total Cost at 2%, unless 2% of that total cost is $10 or greater, then I want it to insert $10. I wrote the following function: =IF(E8*0.02>9.99,E8*0.02,10), but for a total cost of $457.00 it's returning $10. But $457*.02=$9.14, which is what it should be putting in the box instead of $10. Where am I going wrong?

    1. NVM, I had a greater than sign instead of a less than sign in the equation. So stupid!

  28. Hi Svetlana,

    I'm looking for a formula where if the value of a certain cell is less than or equal to $50 then the next cell over produces the number 1. If the value of the same cell is less than or equal to $100 the next cell over produces the number 2 and so on and so forth.
    Example:
    If the value of K3 is less than or equal to $50 than M3= 1.
    If the value of K3 is less than or equal to $100 than M3=2.
    If the value of K3 is less than or equal to $150 than M3=3.
    If the value of K3 is less than or equal to $200 than M3=4.
    I would need the formula to continue until about $600.

    1. Hi Dan ,

      You can use nested IF functions like this:

      =IF(K3<=50, 1, IF(K3<=100, 2, IF(K3<=150, 3, IF(K3<=200, 4, ""))))

      1. Thank you very much!

  29. Hi Svetlana,

    I'll looking for a formula that will give me the greater of either 20% of a nummber or $5,000 ie $10,000 *20% = $2,000 but $5,000 is greater

  30. Hi Svetlana, I've been looking for a formula to compare two figures and to divide accordingly. The formula is to create a spread sheet in order to calculate incomes.

    If A1 is more than B1, then average B1 by 12. if A1 is less than B1, then add A1 and B1 and divide by 24.

    1. Hi Carlos,

      I am not sure I understand what "average B1 by 12" is :) Anyway, you can add a proper calculation instead of ... in the below formula:

      =IF(A1>B1, ..., IF(A1<B1, (A1+B1)/24, ""))

      Also, please note that the formula will return an empty string if A1=B1.

  31. I am trying to create a formula for 2 columns that have different sets of numbers

    Column A Column B
    99205 99204
    99212 99214

    1st I need a formula to tell me if column A and B match and if not I need to know if the number in column A increases or decreases and by how much. I need to to say, if it doesn't match, something like "Increased by 2 levels" or Decreased by 1 level". Is that possible?

    1. Hi Alyce,

      Supposing that increasing/ decreasing levels is just the difference between columns A and B, you can use a formula similar to this:

      =IF(A1=B1,"match",IF(A1>B1,"increased by "&A1-B1&" levels",IF(A1<B1,"decreased by "&B1-A1&" levels","")))

      If A1=B1, it displays "match"

      If A1>B1, it displays "increased by X levels"

      If B1>A1, it displays "decreased by X levels"

      1. Thank you very much!!

        2nd question could I combine that formula you gave me above and these 2 formulas:

        =IF(VALUE(MID(E2,4, 1)) = 0, "NEW", "ESTABLISHED")

        =IF(D2=E2,"MATCH", D2& " to " &E2)

        and make one big formula?

        1. Hi Alyce,

          I don't think it's possible because these two formulas have both value_if_true and value_if_false arguments. I.e. if D2=E2, the second formula inserts "MATCH", otherwise concatenates values in D2 and E2, there is simply no room for other logical tests.

          You can, of course, add a few more nested IF functions in my formula if you remove value_if_false arguments from the existing functions. But you have to decide on the order of logical tests, because once the condition is met, the formula won't check subsequent ones.

  32. How do you write a formula for this situation. If value in column is greater than zero but less 15 assign 1, value in the same column is more than 16 but less than 25 than assign 2

    1. Hi Amir,

      Here you go:
      =IF(AND(A1>0, A1<15), 1, IF(AND(A1>16, A1<25), 2, ""))

      Please note, the formula will return an empty string for 15 and 16 because they do not meet either criterion, if you want to include them, use <= and >= operators instead of < and >.

      1. Hi Svetlana,
        Thank you much, it worked perfectly well.

  33. still not working :(

    1. Hi Paula,

      If you can explain your criteria in more detail, I will try to help. I.e. how do you calculate regular hours and overtime?

  34. got it
    =(IF(J11>=40, IF(J11<=40,J11, 40), 40))/24

  35. I tried the formula below but it didn't work
    =IF(J11>=40,40, IF(J11<=40,J11))

  36. I am trying to create a formula with hours.
    I need to columns, regular hours and overtime.
    I am having a hard time because of the format

  37. Hello Shahbazkhan,

    You can use a formula similar to this:

    =IF(AND(B2>70, C2>70), "qualify", "")

    1. Hello Svetlana Cheusheva,

      Thank You very much for reply :) :) !!!!!!!

  38. Hello.. I have List of Record of student..My Table look like below,

    Name Percent1 Percent2

    AAA 74 60.92
    BBB 65.54 75.43
    CCC 78 52.57

    Now, i want to list of student with Percentage who have grater than 70 in both Percent1 and Percent2

  39. I have data of employees it shows date and time in same column I want to highlight the late comer and early leaver how can I set the formula say coming late after 8:30 am and leaving before 2:00 pm

    1. Hi Akhtar,

      Supposing the arrival time is in column A and departure time in column B, you can create conditional formatting rules with the following formulas:

      =TIME(HOUR($A2),MINUTE($A2),SECOND($A2))>TIMEVALUE("8:30 AM")

      =TIME(HOUR($B2),MINUTE($B2),SECOND($B2))<TIMEVALUE("2:00 PM")

  40. Suppose i want to put a comment like 80% and above is Distinction, 70-79% is Credit, 50-69% is Pass.what formula do i use so that excel will do it without typing in Dinstinction, or pass or credit or fail myself?

  41. I have a problem in using excel in this scenario, what if in column A i have values like 1.0, 1.1, 1.2,1.3, 1.4, 1.5 up to 3.0 and in column B i'd like to see the result that if values in column A are from 1.0-1.2 it should be equal to 18.00 in column B, if values in column A are from 1.3-1.5 it should be equal to 17.40 in column B, and so on... what do i have to do if i have infinitely many possible input value within a given range but should only have 1 specific outcome value? tnx.

  42. Hi there. I need to find out how to do the formula for the following.
    Column a is type of vehicle
    column b is computer time
    column c is my time.
    COLUMN E IS DIFFERENCE (EITHER POS OR NEG)
    now some times the computer is faster so what i want to say is that if column b is greater than column c then it must put in the difference. IE MY TIME FASTER

    BUT IF COLUMN B IS LESS (QUICKER) THAN COLUMN C THEN THE FORMULA MUST SUBTRACT COLUMN B FROM COLUMN C AND ENTER DIFFERENCE AS A NEGATIVE IN COLUMN E

    I HOPE I EXPLAINED THAT RIGHT. PLEASE HELP.
    MANY THANKS

  43. very good
    i am working in bescom i like your diffrent formulas

  44. Hi,

    Thanks so much dear Svetlana, it worked, I used the page your referred me to.
    This is used for a translation quality assessment rubric.
    Here it is:
    =IF(D32>=90, "Outstanding", IF(D32>=80, "Acceptable", IF(D32>=70, "Revisable", "Weak ")))

    Thanks so much! :)

  46. Hi again,

    Sorry, the reply is scrambled. Could you please fix this:
    =IF(I29 < D32 < I28, D33="Outstanding"), IF(I31 < D32 <I30, D33="Acceptable"), IF(I33 < D32 < I32, D33="Revisable"), IF(D32 < I34, D33="Weak")

  47. Hi Svetlana,

    Thanks for your wonderful support.
    If possible help me with these:
    If D32>89.99 and D3279.99 and D3269.99 and D3270 then D33 = Weak

    Thank you so much in advance. :)
    Bijîk

  48. Hi Svetlana,

    I am currently trying to write an equation for calculating how many people to do a task.

    If I need to count units in a warehouse, I need a certain amount of people on hand if there are certain quantities in a warehouse.

    Say I had these units to be counted. Following figures in units:

    A1 = 5000
    B2 = 29999
    B3 = 99999
    B4 = 1000000
    B5 = 2000000

    Between A2:B2 I need 1 person, between B2:B3 I need 2 people, between B3:B4, I need 4, people, between B4:B5 I need 10 people.

    How would I write this as an equation? There's a lot of different mixed views in the comments of the article page and I am certainly confused hence me asking to be your student for a day.

  49. I need a formulae for H Column equals the same value in E5 and K30 and E7 and K40...The values are 10221 and 12227 respectively

  50. Does anyone know what the "but no less than" formula is?

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