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

  1. hi,
    A1=75

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  37. I have one query:

    I need to have a formula which can determine if it passed or failed according to its type, sample:

    A: Normal/Abnormal
    B:date and time request was received
    C:date and time request was approved
    D:C-B = total hours of the time when it was approved
    E:PASS / FAIL

    FAIL: if request is normal and its approval time exceeds 72 hours
    PASS: if request is normal and its approval time is within 72 hours
    FAIL: if request is abnormal and its approval time is more than 24 hours
    PASS: if request if abnormal and its approval time is within 24 hours

  38. Hi,
    i am facing difficulties in allocating Grades to the students, please help me to write a formula in the M.S.Excel work sheet. Here is an example:

    Grading standard:
    below or equal to 39 is fail,
    40 to 49= D
    50 to 59=C
    60 to 69=B
    70 to 79=A
    80 to 89=A+
    90 and above=A++
    Students Name %obtained Grade
    Ajma 85
    Saeed 63
    Shahzad 55
    Latif 90
    Rashid 74
    Akhtar 80
    Aslam 49
    Ahmad 68

  39. Hi, is there a way to add 2 colums but not have it go over 8? I want to add f8 and f9 but I need only 8 in I9 and the rest to go in j9. Can you please help me?

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

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

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

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

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

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

  46. disregard the last part

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

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

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

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

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