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

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

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

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

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

  5. Hello,
    I hope you can help me!
    I need to find a formula for to represent this in excel for students test scores:

    Beginning 0-10 which will be 1
    Developing 11-19 which will be 2
    Expanding 20-28 which will be 3
    Extension 29+ which will be 4

    IF A1==20,"3" BUT IF A1>29,"4".

    I really appreciate your help!!!

    thank you so much

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

  7. I have created the following IF formula but rather than giving me a value it is just returning TRUE, how do I get the value
    =IF(B9>=26,B9<=100,(B9/(1-30%)))
    100 TRUE

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

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

  10. Added to favorites!

  11. Thank, great help. not i make all sense. did try other formula yesterday. it did the trick.

    =IF(I12<75,"Not Competent",IF(I12<85,"Satisfactory",IF(I12<95,"Competent",IF(I12<100,"Very Competent",IF(I12=100, "Highly Competent")))))

  12. Hi i need to solve this. pls help
    If the employee worked more than 20days he/she will receive 10$ per day he/she worked for food, if worked day is less than 20, he/she will receive 8$ per day .

  13. Svetlana!
    There is any way that I can send you the spreadsheet to see if you can take a look?
    Thanks!!

  14. Omg!.. I need help!!
    Is a lot of info 785 rows
    But i need this... If a check was paid on or before due date then the word "earned", if was later than due date "lost" otherwise "N/A"

  15. Thanks so much Svetlana!!! That makes a lot of sense and I'm embarrassed I couldn't figure it out. Thanks again

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

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

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

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

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

  21. hi,
    A1=75

    input formula A1>=75 then A1*4 and calculate value not morethan 500

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

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

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

  25. Hy me to much tired to apply the formula but no successful apply the condition is that

    If the amount is less then 20000then impose GSt 9% if amount is grater than equal to 20000 than impose 7% GST

  26. I WANT TO FIND LESS WORKING HOURS i.e. those who are not working 8 hrs instead working less than 8 hrs. I want to know the formula . If i have given- IN TIME, OUT TIME. Then how will i find out the short time

  27. Hello,

    I need to know an formula for the below required input to get desired output,

    A1 iN n1 n2 Type Reduction 23 24
    A2 20 1500 75.00 K3 Triple 538
    A3 20 1000 50.00 K3 Triple 394
    A4 20 750 37.50 K3 Triple 20 306
    A5 22.4 1500 66.96 K3 Triple 50 500
    A6 22.4 1000 44.64 K3 Triple 10 363
    A7 22.4 750 33.48 K3 Triple 275

    In different excel workbook, i want to shown values of cell H1 "24" and value in cell H4 "306" as the condition is for 20 number selection of the component is 24 with rating of 306 which is greater than or equal to 302.

  28. Hello, thank you for this post and the useful information. I am trying to format a cell to do the following and would appreciate any help:

    If the numerical cell value of A1 is greater than the numerical cell value of B1 I would like whatever numerical value manually input by a user into cell C1 to automatically show up as a negative number in red.

    Is this possible and if so what would the formula be?

  29. What formula would I use for the following syntax:
    if A1=X1 and B1=Y1 then sum C1-Z1

  30. hi, I faced with a strange problem in excel which makes problem in my formula,
    I have an if formula,
    if a<=bb=>a ,x,y it workes?!?!
    I don't know when I should use each one so I can not give correct formula.
    please help me soon.

  31. I was wondering if I could produce an if statement where I could produce the word "down" if the 11th character = "10", how would the formula look?

    BW1-10-R1-10-P05

    Any help would be appreciated.

  32. Hi
    If I need something to create a range with IF formula say if "0" "Too New to rate", If between 1-1.9 "Below Expectations"
    How would I do it. I tried this but not working :(

    =IF(G38=0,"Too New to Rate", IF(G38>=1=2=3=3.5=4.5<=5,"Far Exceeds Expectations"))))))

    Thanks in advance!!

  33. Need Help !

    I wanna Data in according to Slab , If i Say

    Donna ( Name ) is A Grade Sale Person

    Maddona ( Name ) is B Grade Sale Person

    Slab Should be :

    If "A" Grade Sale Person achieve

    >=100% , >=120% ,>=140%
    "A"-25$ "A"-50$ "A"-100$
    "B"-15$ "B"-25$ "B"-50$

    IF "A" Grade Sale Person achieve Slab-1(>=110 ) will be get 25$
    IF "B" Grade Sale Person achieve Slab-1(>=110) will be get 15$

    Keep in mind if achieve Slab-2 or Slab-3 , automatic he/she achieved slab-1 , in this case Slab-1 should be ZERO or BLANK.

    Thanks In advanced

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

  35. Hi , i have to preaper report which check below conditions form given actual resolved time and give result if issue resolved in given time meets below condition (yes/No).

    Ie ... it checks the priority of ticket and then given amount of time and compare with its priority expected resolved time and output in YES/NO
    Can you help me with exact formula

    priority Parameter P1 P2 P3 P4
    Resolution Time - Hrs 4.00 8.00 16.00 72.00

  36. Parameter P1Priority 2 Priority 3 Priority 4
    Response Time - Hrs 0.15 0.30 2.00 24.00
    Resolution Time - Hrs 4.00 8.00 16.00 72.00

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

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

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

  40. Dear admin.

    Maybe this question is out from topic. Is it possible to make an input and it will come out with other output at different cell.

    For example, I choose input at A1, and other output will generate based on what we set.

    Eg : A1 = Input(6 x 7) ; auto generate output: B1 = 6 & C1 = 7

    Correct answer would be appreciated.

    Thanks

  41. I have 10 numbers(positive/Negative) in cells A1 to J1.
    I want to determine whether this numbers from A1 to J1 are having growth/fall.

    For e.g.
    1,2,3,4,5,6,7,8,9,10 are having growth.
    Similarly,
    10,9,8,7,6,5,4,3,2,1 are having fall.

    Both the cases are acceptable.("YES")

    But,

    4,5,6,7,5,4 are not having continuous growth.("NO")

    I need a formula for this.

    What i have managed to do so far.
    =IF(OR(AND(D5>E5,E5>F5,F5>G5,G5>H5,H5>I5,I5>J5,J5>K5,K5>L5,L5>M5,M5>N5),AND(D5<E5,E5<F5,F5<G5,G5<H5,H5<I5,I5<J5,J5<K5,K5<L5,L5<M5,M5<N5)),"YES","NO")

    But this doesn't work if any of the cells are blank.

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

  43. Hi,

    I have two cells like below.
    Category Wages
    O/L TAILOR -I 280
    F/L TAILOR -I 305
    HELPER 175
    CUTTING HELPER 220

    i need a automatic for wages cell , (e.g If i entered O/L TAILOR IN CATEGORY CELL AND WAGES SHOULD BE CHANGE AUTOMATIC VALUE OF 280)Some one help me out

  44. Hi
    I've list of numbers in a column, I want to find out the numbers which are equal to, 10 grades larger or 10 grades lesser than a number in the column.
    Thank you

  45. Hi,

    my quires

    (>=15% -25points)(>12%0%15%,"25",IF(K2=15%,"25",IF(K2>12%,"15",IF(K20%,"5",IF(K2<12%,"5"))))))

    first two logic will come but last logic not yet come.

    Regards

    SP

  46. Hi,

    my quires

    >=15% -25points
    >12%0%15%,"25",IF(K2=15%,"25",IF(K2>12%,"15",IF(K20%,"5",IF(K2<12%,"5"))))))

    first two logic will come but last logic not put 5 points

    Reagrds

    SP

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

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

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

  50. Hi Svetlana,

    If A1 amount has a range and the results in A2 with different types.

    0-50000 = Type A
    50000-200000 = Type B
    200000-700000 = Type C

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