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

  1. Hi Svetlana,

    I'm trying to calculate the following:
    If cell A is greater than or equal to cell B, and cell C is greater than or equal 930, cell F will pay x amount.

    Thanks in advance for your help.

    Al

    1. Hi Al,

      You can use a formula similar to this:
      =IF(AND(A1>=B1, C1>=930), 10, "")

      Where 10 is x amount.

  2. HELP!! I need help on a formula
    If D15 is less than 21, but greater 101, then c5+c6+c7+c8*d15

    thanks in advance!!

    1. Hi Christa,

      No number can be less than 21, but greater than 101 :) Do you mean greater than 21 and less than 101?

  3. I need help on a formula

    If J3 is less than 65 then A
    If J3 is equal to 65 then B
    If J3 is greater than 65 then C

    Thanks

    1. Hi!

      You can use the following nested IF's:

      =IF(J3<65, "A", IF(J3=65, "B", "C"))

  4. 0.75 <= x < 1.25
    please some body explain it to me

    1. Hello Mehmood,

      In Excel formulas, you express this condition using the following AND statement: AND(A1>=0.75, A1<1.25)

  5. hallo

    i have token system
    how to use which formula for token calculate.

    value is
    150-600 is 1
    601-1000 is 2
    1001-2000 is 3
    2001-3000 is 4
    3001-4000 is 5
    40001-Above 6

    Please reply fast.

  6. Need to clarify the above question again

    i have to calculate c21*d21 to f21, if f21 value is equals to or less than "20" then f21 should show as "20" (20 is the minimum value) if the result is greater than "20" then the actual value should show in F21

    1. Hello Joy,

      Here you go:
      =IF(C21*D21<=20, 20, C21*D21)

  7. I need to combine these 3 formulas into one
    1. A = less than or Equal to 5
    1. IF(AND(A15, A110,2, "0")

    thanks for your help

    1. Hi Hebah,

      I think you are looking for a nested IF formula like this:
      =IF(A1<=5, 1, IF(A1<10, 1.5, 2))

      The formula does the following:

      If A1<=5, return 1
      If A1>5 and <10, return 1.5
      If A1>=10, return 2

  8. Below formula will show me the latest date if A1 and B1 matches, if no match it show FALSE. I want the cell to be empty.

    =IF(A1=B1;MAX(F8:F100))

    1. Sorry wrong cell count F8-F100,

      Anyway I got the correct formula!

      =IF(A1=B1;MAX(B2:B100);"")

      This will make sure C1 is empty when A1 and B1 is matching

  9. Need your help... not sure what formula i need to have to get the time interval between 2 time duration in minutes. also i need to have a message box error informing me that time interval should be atleast 5 minutes...

    =IFERROR(F22-E22)*1440

    1. Hi Jay,

      You can use a formula similar to this:
      =IF((F22-E22)*1440>5, (F22-E22)*1440, "less than 5 min")

      This won't display a message box, but insert the text "less than 5 min" in the cell if the time difference is less than 5 minutes.

  10. If I am trying to compare the following data...how would I write out the IF statement?

    If B7 is the same as B22 than it needs to equal d22?

    1. Hi Ashley,

      Here you go:
      =IF(B7=B22, D22, "")

  11. Hi, I need a formula for this:

    =IF(A1="YES”, B1+C1*0.93)

    So what I am looking for is if column A says "YES" then the value from the formula B1 + C1 * .93 will show in the cell I put this formula in.

    1. =IF(A1="YES", B1+C1*0.93, ())

    2. Hi Adam,

      Your formula is correct. If it does not work as you expected, then please give an example - what values you have in B1 and C1 and what is the expected result.

  12. =COUNTIF('15 PP YR '!D5:NG5,"f")+IF(B3="FT">480,B3="PT">300)

    Here is what I have attempted. '15 PP YR'is a sheet, D5:NG5-adds up a row, if F. and then not sure which formula to use for if FT = full time the max is 480 turn red if above 480, then for PT = part time the max is 300 then turn red. Thanks for any help you can provide.

    1. I haven't gotten any assistance on my question, is there anything else you need. Thanks for any help, you can provide.

  13. Hi,
    I need help whit this:

    IF A2=1, how to autocomplete B2=0 and C2=0

    I need to type 1 in first column and the next two columns autocomplet whit 0(zero)

    Thank you!

    1. *in B2 =IF(A2=1,0,())
      *in B3 =IF(A2=1,0,())

  14. Hi Svetlana
    I've have another one for you....
    I'm trying to us an IF formula for the following
    I have a calculation to determine if material was delivered on the date it needs to be delivered
    Formula: =U2-Q2 (Actual Deliver date-Need By date)
    If =0 "On Time" If =V2=0, -1, 1 "On Time" IF > -1 or <-1 "Late"

    How do I create a formula for this?

    1. IN addition, can I create rules based on customer without having to filter or add customer shipments to another tab. Or, is this a formula specific customers to independent tab.

  15. Hi Svetlana,
    I need a formula for this:
    If cell G begins with the words "Not Ordered" and Cell J doesn't equal "JL" "RE" "XE" then output in cell W should say "Ok to proceed"

    1. Hi Amoony,

      Here you go:

      =IF(AND(ISNUMBER(SEARCH("Not Ordered",G2)), AND(J2<>"JL", J2<>"RE", J2<>"XE")), "Ok to proceed", "")

  16. Hi Svetlana I'm wanting to display the days date in a cell the day another cell goes above 0.

    If Cell F1 changes value to above 0 on the 15/10/2015 I want cell M1 to display that date.

    Hope the above makes sense?

    Thanks

    1. Hi Mark,

      I am afraid no Excel formula is capable of doing this (

  17. I would like to use an excel function to get my answer,

    if my value if greater than equals to 300, i need an answer "Yes" - how can i get this formula..

    Thank you.

    1. Hi KJ,

      Here you go:
      =IF(A1>=300, "yes", "")

  18. hi I need help with setting up price brackets
    eg
    Bronze price bracket is 50 to 150
    silver price bracket is 151 to 200
    gold price bracket is 201 to 301
    platinum price bracket 301 +
    I need a formula for the above
    it need to have the to whole of the above for each cell because the price is different for each item

    1. Hello Nino,

      You can use a nested If formula similar to this:

      =IF(A1>300, "platinum", IF(A1>200, "gold", IF(A1>150, "silver", IF(A1>=50, "bronze", "")))

  19. I need to combine these 3 formulas into one
    =IF(B7=101,(B8/(1-26%)))
    =IF(AND(B9>=26,B9<=100),(B9/(1-30%)))

    Your help is appreciated

    1. Hi Amanda,

      You probably want something like this:

      =IF(B7=101, B8/(1-26%), IF(AND(B7>=26,B7<=100), B8/(1-30%), ""))

  20. What would be formula for the following

    If A2 is greater than 2 and Less than 6, SUM(B2*6) ?

    Thanks in advance

    1. Hi Sam,

      Here you go:

      =IF(AND(A2>2, A2<6), B2*6, "")

      1. Thank you so much.

  21. Hi how to write a expression suppose if B43 is greater than 150 and less than 500 then output should be 0.2 or if its greater than 500 and lesser than 900 than 0.3 please help.

    1. Hi Anish,

      Here you go:

      =IF(AND(B43>150, B43<500), 0.2, IF(AND(B43>=500, B43<900), 0.3, ""))

  22. I need help and not sure how to put in words....
    I'm trying to figure a percentage in one of my columns but if you divide by 0 I get #DIV/0!. So, I'd like to add and IF function stating if column C = 0 make Column D 100% or calculate it based on 1.

    I'm not sure if that makes sense but if you can direct me to what function I can use it would be appreciated.

    1. I'd like to add....
      This is the formula I entered..
      =IF(AA10=0,1) and it populates as requested but if there are other numbers I want the system to continue the exiting formula I have in place which is a simple division =P25/AA25 to come up with the correct percentage. I'm looking to avoid having to change all my 0 to 1 manually and still complete the other calculation

      1. Lia,

        The easier way to fix this is to embed your current formula in the IFERROR function:
        =IFERROR(P25/AA25, P25/1)

        The formula does the following: if the division P25/AA25 results in any error, then P25 is divided by 1. And naturally you can replace P25/1 with any other calculation, value, or maybe some text that you want the formula to return instead of the #DIV/0 error.

        Alternatively, you can use the IF function:
        =IF(AA25<>0, P25/AA25, P25/1)

    2. Hi Lia,

      To make things easier, please post your current formula here, and we will wrap it in the IFERROR function or add an IF statement to avoid the #DIV/0 error.

  23. Pls help how can make this into a formula

    <75 - Not competent
    75-85 - Satisfactory
    86-95 - Competent
    96-99 - Very Competent
    100 - Highly Competent

    i used this formula but will stop at "competent" and will not reflect "very competent".

    =IF(I1274,"Satisfactory",IF(I12>84,"Competent",IF(I12>94,"Very Competent",IF(I12>99, "Highly Competent")))))

    1. Hi Jpat,

      This is because Excel checks the first condition first and if it's met, it does not check other conditions. So, you should put the conditions in the reverse order, beginning with the highest value:

      =IF(I12>99, "Highly Competent", IF(I12> 95, "Very Competent", IF(I12>74, "Satisfactory", "Not competent")))

  24. what is the formulla for if the value of the cell is equal to or grater than 20 the resultant value will be the 1, and if the value on the cell is less than 20 and the resultant value will be the ratio of the given value

    1. Hi Amrit,

      You can use a formula similar to this:

      =IF(A1>=20, 1, A1/N)

  25. Hi,
    i have a doubt how can i add the symbol of on or less than

    1. Hi Sandeep,

      If you mean "equal to or less than", use <=. If you want something different, please clarify.

  26. I need the date to stay the same as the initial date, sorry for the confusion. Can this be done?

    1. Regrettably, no Excel formula can do this. TODAY() and NOW() are volatile functions that update every time the worksheet recalculates. To enter the today date as a non-changeable timestamp, you can either use the Ctrl + ; shortcut or try to find a special macro.

  27. I am not very savvy in Excel so I would appreciate your help with a formula. I use this formula, =IF(C4"",IF(A4="",NOW(),A4),"") , to populate today's date when C4 has data. I was wondering how to adjust the formula in a different cell to populate if forms control checkbox value is True and blank if value is false. So, I have a check box in cell J4, I have it associated in cell K4, and I want L4 to populate today's date if K4=true. Hopefully youcan help.

    Thank you!

    1. Hi Tina,

      If my understanding of the task is correct, you can use the following formula for L4:

      =IF(K4=TRUE, NOW(), "")

      Also, make sure the Date format is applied to cell L4.

      1. Thank you. Will the the date in L4 stay the same if workbook closed and opened on another day or will it update to the date it is reopened?

        1. It will update to reflect the current date. The formula uses the NOW() function which updates every time the workbook is reopened or recalculated.

  28. Hi,

    I have data in cell A1, I want cell A12 to equal A1 however, if the next cell (A2) is filled out, I want A12 to equal A2 and repeat the process so if A3 is filled out A12 will read A3, is this possible? Any help will be appreciated.

    Thank you!

    1. Hi TJ,

      You can enter the following IF formula in A12:

      =IF(A3<>"", A3, IF(A2<>"", A2, A1))

      1. Thank you but how many cells can I do this for? I may have up to 100 cells that this process needs to be repeated in.. Instead of A3, it can go up o A100. How would I continue the process?

        Thank you

        1. Since the modern versions of Excel allow only 64 nested IFs in one formula, it's not the way to go in your case. You can try the following formula instead:

          =IFERROR(INDIRECT("A"&MATCH(TRUE, INDEX(ISBLANK($A$1:$A$100), 0, 0), 0)-1),"")

          Please note, the formula will work correctly only if there are no empty cells in between column A. If there are blank cells, it will return the last value in the first block of contiguous non-empty cells.

          1. Thank you! This is exactly what I needed. I really appreciate your help.

  29. Many thanks in advance to anyone who can point me in the right direction.

    I have two ranges of amounts, and I need to determine if any number in the second range is greater than any number in the first range.

    For example:

    Rate A | Rate B | Rate C | Rate 1 | Rate 2 | Rate 3

    I need either:

    is 1 greater than any cell A:C,

    OR, ideally,

    is any cell in Rate 1:3 higher than any cell in A:C

    True if so, false if not (so that I could then use conditional formatting based on the formula)

    I understand how to do 'is Rate 1> rate A', and I understand how to put that in conditional formatting, but I can't find a way to make it look at the whole range (rate 1 compared to rate a, rate b, and rate c) instead of the single cell.

    I have a 1,000+ rows by 17 columns to compare, (12 in the first set of rates and 5 in the second set), so I need to be able to apply it in large scale and not have to create the unique conditional formatting rule in every single cell.

    I am certain that someone smarter than me could make this work in a cinch, but I have read through every page and tutorial I can find and am just not able to put together a working solutions.

    Thanks very much.

    1. Hello Angela,
      You can use the MIN and MAX functions, something like min(SECONDRANGE)>max(FIRSTRANGE).

  30. I Have 1 query in excel formula:
    if Column A is greater then 0 Show (Shortage) & again apply column A is less then 0 (Excess)

    1. Hi Kashif,

      Try this one:

      =IF(A1>0,"shortage","excess")

    2. im use this formula but not work

      =IF(OR(J430,),"shortage","excess")

  31. I have two numbers listed in each cell, A and B. I need the higher number listed in C.

    1. Hi Joaquin,

      Use the MAX formula like this:
      =MAX(A1, B1)

  32. I am trying to use an IF formula to determine if a site has not screened in 3 months =IF(I3<=90,"OK","Action") The problem is that the cell it is referring to (I3) also has a formula in it =IF(ISBLANK(F2),"0",P2-F2). I had to use the ISblank formula for instances in which there were cells that did not have a date. The problem is that I think this is affecting the first formula I mentioned because anytime there is a blank cell then the formula does not work. For example I would like the formula to realize that a blank cell is = 0 which is <90 which should register as okay. Instead, what is happening is that the cell is marking it as "Action" which is incorrect. Please help!

    1. Hi Andrea,

      I think the problem is in zero enclosed in double quotes in your ISBLANK formula. Once you enclose any value in "", it's turned into a text string and Excel does not interpret it as 0 any longer.

      So, try changing the first formula to =IF(ISBLANK(F2),0,P2-F2) and I think your IF statement will start working properly.

  33. I need help with a formula.
    If A2 is equal to or less than A1 divided by 2, than A1-A2, if not than A1 divided by 2.
    I have...
    =IF(A2<=(A1/2),[A1-A2],[A1/2])
    But excel returns an error "The name you have entered is not valid".
    reasons include
    -the name does not begin with letter or underscore
    -the name contains a space or other invalid characters
    -the name conflicts with an excel built-in name or another object in the workbook

    The first instances of A2 and A1 in the logicial test "A2<=(A1/2)" have the cell values colored and correspond to those cells as normal, however the other instances of those cells in the value if true "[A1-A2]" and value if false "[A1/2]" sections do NOT have the cell values colored and do not correspond to those cells at all.

    I Want it to subtract A2 from A1 as long as A2's value is 50% or less of A1's value, if not it should divide A1 by 2 instead. Basically i want it to subtract no more than half.

    Would really appreciate any help on correcting my formula and/or the error.

    1. Hi Bryan,

      Remove the square brackets from your formula and it will work just fine :)
      =IF(A2<=A1/2, A1-A2, A1/2)

  34. I want to the if function to test value in too different cells, and display which is higher and if equal should display any of the value

    Correct answer would be appreciated.

    Thanks

    1. Hi Zack,

      You can use the MAX function, for example =MAX(A1:C1) or =MAX(A1, C1, E1)

  35. Hi Svetlana,

    I am struggling with the following challange.

    I would like to deduct in 00:30 hrs if the time difference between 2 cells is more than 6 hours.

    I have A1 12:00, B1 18:30, C1 = difference A1 and B1, BUT if the difference = more than 6 hrs I want C1 to deduct 00:30

    Thank you very much for your help.

    Regards, Bram

    1. Hi Bram,

      Try the following formula:
      =IF(B1-A1>TIME(6,0,0), B1-TIME(0,30,0), B1-A1)

      1. Hi Svetlana, Thank you for your prompt reply.
        I am very happy! It put me on the right track.

        I solved it by first placing the difference in cell C1 and then placing the formula =IF(C1>TIME(6;0;0); C1-TIME(0;30;0); C1) in cell D1

        Very best regards,

        Bram

  36. Need to insert today date on B1 if A1 contains a specific value or characters

    need formula, pls help

    1. Hi Lokesh,

      You can use a formula similar to this:

      =IF(A1="text", TODAY(), "")

      Remember to apply the Date format to B1.

  37. I'm trying to create a cell I want it to read 25% of earned income. However, if expenses exceed the 25% of earned income, I want the cell to read 0. Can I do this?

    1. Never mind. I did it! I think.
      =IF(F4<(B4*0.25),B4*0.25,0)

  38. Hi Svetlana,

    What if I wanted the output cell to be equal to 1 only if the copied cell is greater than 500, and 0 only if the copied cell is less than 500?

    i.e. X=1 IF X > 500, 0 IF X < 500

    Thank you for your assistance :)

    1. Hi Clyde,

      You can use a formula similar to this:

      =IF(A1>500, 1, 0)

      1. Ah perfect, thank you very much...way more simpler than I expected it to be :)

  39. if any value like 19 or less then it then it should be count 19 or same but if it is above 20 then it should count 20.i want formula for this query?

    1. Hi Chirag,

      You can use a formula similar to this:
      =IF(A1<=19, 19, 20)

      1. I have a spreadsheet that I like to skip the column that have a grand Total of "0"

        So if Column G5 grand total is zero, skip G5 and look for G6
        if G6 is zero look for G7.

        If G7 value is 1 then insert the value A1

        Basically I like to keep only the values 1 and above in the spreadsheet

        Thank you

        1. Hi Jaison,

          And what do we do if G7 is 0 or >1?

          1. Thanks for the reply

            Once the value is >1 on the pivot, we will use that value.

            1. Jaison,

              If my understanding of the task is correct you can use the following formula:

              =IF(G5>0, G5, IF(G6>0, G6, IF(G7>0, G7, A1)))

  40. Hi there:

    I am trying to write a formula that says

    "If (reference to a cell on another sheet) is >=15, then enter 15000 in the cell if not, then enter 0."
    This is the formula I have entered that does not seem to be working..
    =IF('Visits Schools'!C38:C40>="15",15000,0)
    What's wrong with the formula?

    1. Kacey,

      Double quotes are not needed for numbers and each cell should be referenced individually, for example:
      =IF('Visits Schools'!C38>=15, 15000, 0)

      or

      =IF(AND('Visits Schools'!C38>=15,'Visits Schools'!C39>=15, 'Visits Schools'!C39>=15), 15000, 0)

  41. I have a spreadsheet that I like to skip the column that have a grand Total of "0"

    So if Column G5 grand total is zero, skip G5 and look for G6
    if G6 is zero look for G7.

    If G7 value is 1 then insert the value A1

    Basically I like to keep only the values 1 and above in the spreadsheet

    Thank you

    1. Any help would be much appreciated

  42. How do I write a formula for, If G2>H2, return 0, if h2<G2, subtract h2-g2, for answer.

    Help!!

    1. Hi Wanda,

      Here you go: =IF(G2>H2, 0, H2-G2)

  43. Hello!

    I have a spreadsheet which takes a ton of input from various user created lists. One of my cells is a simplified display, but properly doing the code is turning out to be difficult.

    Currently I have:
    =IF(ISNUMBER(FIND(Facility,Each1)),"↑BCR",IF(AND(ISNUMBER(FIND(Facility,Each2)),NOT(ISNUMBER(FIND(Facility,Each1)))),"↓BCR",""))&IF(ISNUMBER(FIND(Facility,Each3))," ↑BM",IF(ISNUMBER(FIND(Facility,Each4))," ↓BM",IF(ISNUMBER(FIND(Facility,Each5))," ↓BMx2",IF(ISNUMBER(FIND(Facility,Each6))," ↓BMx4",""))))

    Basically, I have two things I'm measuring: BCR and BM.
    I have a list of facilities.
    I have 6 lists, "Each#".

    Each1 = ↑BCR × integer
    Each2 = ↓BCR × integer, if not on Each1
    Each3 = ↑BM × integer
    Each4 = ↓BM × integer
    Each5 = ↓BM × 2 × integer
    Each6 = ↓BM × 4 × integer

    The formula recognizes that if a facility is not on Each3, Each4, Each5, or Each6 that the value " " is reported back. I would like for it to do similar with Each1 and Each2. Specifically, if a facility is on both Each1 and Each2, I'd like it to return " ".

    In general, the Each2 function I created seems to be broken.

    Help please?

    1. I'm still holding out that you'll help me! I really cannot figure this one out on me own.

  44. Hey, need help on creating formula for two numbers. Here is the current:

    If(AND(K14>=3000,M14>=10%,K14<=3000,M14<=10%),"Yes"," ")

    For some reason it is still returning blank when I put in a negative number less than -3000

    1. Hi Excelio,

      This happens because all numbers match your logical test :)

      K14>=3000 and K14<=3000

      M14>=10% and M14<=10%

      If you can specify under exactly what conditions you want to return "yes", I will try to help.

      1. After reading your comment I realized I needed to put an OR before the AND. Thank you!

        1. I also realized that I didn't put negatives in my question in front of the second 3000 and 10%. That might have solved a lot of confusion.

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

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

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

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

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)