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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. 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 :) :) !!!!!!!

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

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

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

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

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

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

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

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

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

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

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

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

  31. If column A1,B1,C1,D1 are values and I want E1 is greater among them, then what will be the formula in exel

  32. Hi I have a simple problem. I'm not an excel expert at all.

    I have to calculate the hours worked by staff. 9 Hours per day is required, so 45 hours per week means no overtime or deduction for fewer hours worked. When the weekly hours worked is less and equal to 45 i need the answer for overtime worked to be "zero", not true or false. what formula do i use? how do i do that ? im clueless...

    1. Hi Francois,

      You can use an IF formula similar to this:

      =IF(A2<=45, 0, "overtime")

      Where A2 is the Hour column. And you can enter any other text, number or calculation instead of "overtime".

  33. Hi

    Hope you can help. I need a couple formulas:
    1. Need formula to look a LOS column lists 0 years, 0 months to 41 years, 8 months that states if the LOS is greater than 6 months, but less than 1 years, Y for yes.
    2. Need to look at text in column on one worksheet, and if matches on other worksheet which list job codes with job banding, list the job band created for the other worksheet.
    Any help would be appreciated!

  34. Hi Svetlana

    Great article, very helpful thank you. I was wondering if you could help me with two queries that I can not quite solve myself, despite your helpful article?

    1) I am looking for a calculation for the following if A1 is greater than 5823 I need it to calculate what 1% of any value is between 5824 and 42000 (in other words if the figure in A1 was 50,000 I would need the calculation to be (42,000-5824)*0.01) (if it was 25000 I would need it to calculate (25000-5824)*0.01) (If it was 5000 I would need it to recognise it was less than 5824 and insert a 0)

    2) I would like the spreadsheet to count anyone between age 18 and 67, the spreadsheet would have a list of dates of birth. Also I'm not sure if it can do this but every time I go into the spreadsheet can it recognise the same information based on that days date?

    An answer to at least one of the above would really help me?

    Many thanks

    Michael

    1. Hi Michael,

      Sorry, I am not sure if I fully understand your first task. The below formula works with the following logic:
      - if the value in A1 is greater than 5823, it calculates (A1-5824)*0.01
      - if the value in A1 is equal to or less than 5823, it returns 0

      =IF(A1>5823, (A1-5824)*0.01, 0)

      Please correct me if my understanding is wrong.

      As for the second task, you can use one of the age calculation formulas described in How to calculate age from date of birth in Excel and then use the following COUNTIFS formula:

      =COUNTIFS(B1:B15, ">18", B1:B15, "<67")

      Where column B is the age column.

      1. Hi Svetlana

        Thank you for your help. Step two is perfect I will apply this.

        as for question 1 there is one part missing. The calculation should only be based on 1% of anything greater than 5823 and anything less than 42001. So any amount above 42000 need to be ignored in the calculation?

        Thanks

        Michael

        1. I think I got it. Then we can add the following OR statement in the logical test:
          =IF(OR(A1>5823, A1<42001), (A1-5824)*0.01, 0)

          1. Not quite I'm afraid if I A1 = 50000 the calculation should be (42000-5824)*0.01 = 361.76
            The formula you provided still counts the additional 8000 above the 42000 so it calculates (50000-5824)*0.01=441.76

            I am trying to get the calculation to only take into account anything between and including 5824 and 42000

            Do you understand and can you help?

            Sorry it is quite difficult to explain thank you for your patience

            1. Hi Michael,

              Okay, let me check if I understand the conditions correctly:

              If A1>=5824 and A1<=42000, then (A1-5824)*0.01

              If A1>42000, then (42000-5824)*0.01

              If A1<5824, then 0

              The following formula works with the above logic:

              =IF(AND(A1>=5824, A1<=42000), (A1-5824)*0.01, IF(A1>42000, (42000-5824)*0.01, 0))

              BTW, instead of (42000-5824)*0.01 you can put the resulting number 361.76 directly in the formula:

              =IF(AND(A1>=5824, A1<=42000), (A1-5824)*0.01, IF(A1>42000, 361.76, 0))

              If I misinterpreted any of the conditions again, please let me know, or you can adjust the calculations directly in the formula.

              1. Svetlana

                That is perfect, thank you very much your help is greatly appreciated.

                Kind regards

                Michael

  35. I need a formula to calculate a value based on 4 value ranges. So if A1 is greater than 100, but less than 299, then B1 multiplied by set value, but if A1 if greater than 300 but less than 499, then B1 is multiplied by a different set value, if A1 is greater than 500 but less than 1999, the B1 is multiplied by a different set value and if A1 is greater than 2000 then B1 is multiplied by yet another value. Can you help??? many thanks

    1. Hi Jean,

      You can use a nested IF formula like this:

      =IF(A1>2000, B1*1, IF(A1>=500, B1*2, IF(A1>=300, B1*3, IF(A1>=100, A1*4,""))))

      1. HUGE THANKS!!!!!!!!!!!!!! You've just made my day! thanks

  36. If I wanted to show if A1 is not ABC, the Cell B1 will shows either some text or change the cell (B1) color to RED

    1. Hi Adrian,

      To show some text, enter the following formula in B1:

      =IF(A1<>"ABC", "some text", "")

      To change the color, select B1 and create a conditional formatting rule for it with this formula: =$A1<>"ABC"

  37. PLEASE HELP!
    I have two columns with raw data and they are columns D = Days, E=total pages.

    To report on the data columns I have the following:
    Column L = Total Pages
    Column M = Total line count
    Column N = Less than 3 days

    this is what I need:
    Column L
    Total Pages
    1
    2
    3
    4
    5-10
    11-15
    16-20

    Column M
    Total Invcs
    needs to equal columnColumn E if its 1 page
    needs to equal columnColumn E if its 2 pages
    needs to equal columnColumn E if its 3 pages
    needs to equal columnColumn E if its 4 pages
    needs to equal columnColumn E if its 5, 6, 7, 8, 9,10 pages
    needs to equal columnColumn E if its 11, 12, 13, 14, 15 pages
    needs to equal columnColumn E if its 16, 17, 18, 19, 20 pages

    Column N
    Less than 3 Days
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less

    1. Columns M and N are where I need the formula that I can't figure out :((

  38. *order weight column ex. 6.2 (A5)*

    The formulas i entered was

    =IF(A5>=5<=12,C5)

    1. Hi Dan,

      Excel cannot understand 2 logical operators like you use. You need an AND statement in this case.

      For example, the following formula will return a value from C5 if A5 is equal to or greater than 5 and less than or equal to 12, an empty string otherwise:

      =IF(AND(A5>=5, A5<=12), C5, "")

      If you are looking for something different, please clarify.

      1. You are an absolute star Svetlana, really appreciate you taking the time to solve my issue, our dogs will never go hungry! :)

  39. Hi great explanations on your page! I wonder if you could help me?
    I'm trying to calculate shipping costs for our dog food order. I have a delivery weight column ex. 5kg-12kg and a price column ex. £6.50 (C5) along with an order with column ex. 6.2 (A5) Using an IF formula i want for the whole range upto 200kg
    I've entered =IF(A5>=512<=24,C6) etc etc
    However it tells me that I can't compare a boolean to a number because they are different types, I tried entering the double unary you suggested "--" but without success. Can you tell me what I'm doing wrong?
    Many Thanks, appreciate your time :)

  40. Hi

    Need some help with this one: i have two columsn and want to use A1=B1 and should give answer TRUE as it is the same field but getting FALSE?

    1. Hi Adri,

      If the values in A1 and B1 are absolutely identical, your formula will return TRUE.

      If it returns FALSE, then the values are different, though they may look the same. For example, these may be decimal numbers with 1 or 2 decimal places displayed, and these places are identical, while the 4th or 5th place is different.

      There can be a lot of other differences not noticeable at the first sight. Regrettably, it's impossible to spot the exact reason without seeing your data.

  41. Hi,

    Need a little help with a logical if condition if you have time.

    I have three groups of ranges each with 3 questions, and they can have a value of high, medium or low i.e.
    range 1 - H / H / M
    range 2 - H / M / M
    range 3 - H / L / L

    If all of the values in range 1 = high, and the values in range 2 and 3 = high or medium, then return high
    If any of the values in range 1 = high or medium, and 2 or more in range 2 and 3 = high or medium, then return medium
    If any of the values in range 1 = low, and 2 or more in range 2 and 3 = low, then return low

    Thanks for any help possible.

  42. I am trying to build a formula that takes data from one sheet and transfers it to another sheet in the spreadsheet if the value in the column indicated on the first sheet equals "Yes". Please help.

    1. I'm also trying to figure this out. I would imagine it would be an If equals sort of thing, but I'm not sure how to do it.

      1. You can use the VLOOKUP function to pull matching data from another worksheet and the IF function to check if the condition is met.

        For example, the following formula checks if the value in B2 is "yes", and if it is, it pulls the data for "Product1" from column C on Sheet1; otherwise it returns an empty string:

        =IF(B2="yes", VLOOKUP("Product1", Sheet1!A2:C10, 3, FALSE), "")

        Instead of "Product1", you can refer to the cell containing your key value, say A2:

        =IF(B2="yes", VLOOKUP(A2, Sheet1!A2:C10, 3, FALSE), "")

  43. Hello, Need some one help to apply logical if condition. There is data in column i want to apply if condition....

    A2 B2 Result should be like
    5 (1550)
    6 (1900)
    4 (1200)
    8 (2600)
    If A2 is less than equal to "4" multiple by 300. OR if its greater than equal to "4" multiple by 350.

    1. Hi Mohd,

      You can use the following IF function:

      =IF(A2<=4, A2*300, A2*350)

      1. Thanks!

  44. I have part numbers in a column, some contain the letter V at the end and some do not. I want a formula that indicates the part numbers that contain the V to simply display the value in another cell, and the ones without a V to calculate a simple multiplication.

    Thanks
    Vic

  45. A CPA associate answered my question: =IF((ISTEXT(B8)=TRUE),0,B8)*IF((ISTEXT(D8)=TRUE),0,D8)
    easy peasy thank you for your concern.

  46. I have a simple formula B8*D8, there are times when I want to include various text messages in the cells B8 or D8 and I want the formula to ignore the text and not create an error message. I have tried IF(B8=XXXXX,0,B8)*IF(D8=XXXXX,0,D8) where I have put the x's I have tried everything I can think of to describe any text with no success.

    1. Hi Jeff,

      The following formula returns an empty string if an error occurs, otherwise the product of B8 and D8:
      =IFERROR(B8*D8, "")

      Is this what you are looking for?

  47. Hi! I tried to use this formula but the result is wrong, or it marks the formula as inconsistent... How can I change this to work?
    =COUNTIF(C3:C42,">6<12")

    1. Hi Fany,

      Since you have 2 criteria, you should use the COUNTIFS function and define each condition separately:

      =COUNTIFS(C3:C42, ">6", C3:C42, "<12")

  48. Sorry the above arguments is not complete. Here's the complete one.

    1. If C3>=(10),then 10 days and above behind schedule
    2. If C3=B3, then on schudule

  49. Hi I'm trying to monitor the days of the delivery date if it is on schedule or behind schedule. Can anyone help me please, below are the arguments. Thanks!

    1. If C3>=(10),then 10 days and above behind schedule
    2. If C3=B3, then on schudule

    1. Hi Mushy,

      Here you go:

      =IF(C3=B3, "on schedule", IF(C3>=10, "behind schedule", ""))

  50. Hi There,

    I am working on a report where I have to compare three columns to match a Tier 1-5 based on dollar value in a second column and a risk rating in a third.

    i.e Col c is Tier, Col f is Risk (High, medium, low, blank), col H is total cost $value. I need to be able to filter out errors where if it says tier 2, it could have any risk rating but must fall in to threshold of $10MM to $49,999,999.99 OR if it is rated "high" risk and falls between threshold of $1MM to $9,999,999.99.

    I need to show times when there is improper matching of tier and dollar value and highlight those records.

    Any formula suggestions would be helpful!
    Thanks!

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