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

  1. Hi everybody,

    Does there exist a Formula whereby everything I write in a Cell=1, for example:
    C3 has the text O00000001
    C4 has the text O00000002
    C5 has the text O00000003, and so on.

    The Formula that I have in place is: =(C3:C300)*27.50. However, in this case the value on C3:C300 has to be numerical, otherwise it doesn't add it up. In my case these are order IDs (starting with O-Oscar)

    So, let's say I have 97 cells in C column written with that sequence (O0000001-O0000300)

    I have 6 other columns and it would be very helpful if I can simply write:
    C3:O00001 D5: O000045 F9: O0004523

    Whereby CDF and other have of course different values.

    My question, simply put...

    Is there a way to make excel count my Order ID on any of the cells as a simple "1".
    I tried with IF, didn't work, or I didnt know the exact formula.

    Thanks

  2. Is this correct?
    1+21=22 i need formula of round off to one digit 22 = round off to 20
    2+34=36 this is round off to= 40
    plz reply the formula

  3. hELLO
    I have written

    =COUNTIFS(D7:D9,">="&AC10,D7:D9,"="&AC10 getting "VALUE!
    NEXT
    D7:D9 getting a number
    "<="&AC10 getting "VALUE!

    Can you fix it?
    Thank you

    1. mayby this is the right one.

      =COUNTIFS(D7:D9,">="&AC10,D7:D9,"<="&AD10)

  4. I have a value in cell "A1" (80). A value in cell "B1" (70). Cell "C1" has a formula (=B1-A1) which yields "-10" in "C1".

    Cell "D1" has a formula: if(C1<=-10,"yes","")

    It works and I get a "yes" if the value in "C1" is less than -10 but I get a blank ("") if the value EQUALS "C1".
    If I type in "-10" into cell "D1" it recognizes the "=" part of the function and it works.

    I tried substituting "≤" for "<=" to no avail.

  5. Hi..
    The formula in column C is IF(A1=B1,1,0)

    B B 1
    C C 1
    B B 1
    A A 0
    C C 1
    A D 0
    C B 0
    A A 0
    D D 0
    D D 0
    B B 1
    A D 0

    Why it is returning zero for equal values... like D D should return 1

  6. What formulae do i use to get the performance status as per below matrix

    % Achieved Market Performance Status
    100% Top Performer
    Above 90% Satisfactory Performer
    Below 90% Unsatisfactory Performer

  7. Hello I have a formula in mind, but I can't write it down in excel maybe you can help me with it.
    We have a range between 500 and 600, if a result is lower than 500 then its equal 0, but if its higher or equal than 500 we multiply the result by 2. But maximum result be multiplied by 2 can be up to 600. So if we have a result 700, only 600 will be multiplied by 2.

    Do you know what I mean?

  8. what formula do I use for table type fee schedule like values greater than 0 but not less than or equal to 500 and get 30 for its answer? please help me

  9. Hi! What formula can be used for:

    - Let's say the salary is in column G55.
    - So, G55 > $1,600 but < $1,500...contribution should detected as $6.20.

      1. Thank you! The article was a great help!!

  10. Hello all,

    I really need urgent help with regards to if operator. I have some dates in column A and other in column B. I want to keep the maximum of both the dates. So I use the following code: IF (A1>B1, A1,B1). While this works beautifully when we dont have blanks or hyphens. But in some case where a cell is having a hyphen it gives me a hyphen again in my new generated column, which I dont want, I want whatever available year is there in either column A or column B it should come out in my new generated column.

    A B C [if A>=B, A,B)
    1990 1995 1995
    2000 - - [I want this to be as 2000]

    What is problematic when I go for a <= code in the same shown example then I do get whatever the available year is. By this I mean the following:

    A B C [if A<=B, A,B)
    1990 1995 1990
    2000 - 2000[I want this to be as 2000]

    Why this discrepancy is happening, where am I going wrong here? Is something there that I am missing out?

    P.S. Since my analysis has some other connected codes in the same column, I cannot delete hyphens and keep them as blanks. And neither I can use a max code because max gives you 0 in case both the columns A and B have blanks which again is not desirable for me.

    Please help!!!

    1. Hello!
      You are comparing a number with a character and therefore get the wrong result. Use the ISNUMBER function to test a value for a number.

      =IF(IF(ISNUMBER(A1),A1,0)>IF(ISNUMBER(B1),B1,0),A1,B1)

  11. Hi Alexander,

    In the first tab I have a list of words "orange" Cell A1 , "banana" CellA2, "pear" cell A3 etc. In the second tab I have a number of other words "ba" CellA1, "ae" CellA2, "xg" Cell A3 etc. I need to write a formula to compare each word in the first tab against all the words in the second tab and if there is a match to any of the words, to return True/False,

    For example,
    A1 Orange = False (not matches to anything in the second tab)
    A2 Banana = TRUE (matches to 'ba' in cell A1 - second tab)
    A3 Pear = TRUE (matches to 'ea' in the Cell A2 - second tab)

    What formula do you think I can use to do that?

    Thank you!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(SUM(IFERROR(SEARCH(Sheet1!A1,Sheet2!$A$1:$A$10)*(Sheet2!$A$1:$A$10<>""),0)) + SUM(IFERROR(SEARCH(Sheet2!$A$1:$A$10,Sheet1!A1)*(Sheet2!$A$1:$A$10<>""),0)) > 0,TRUE,FALSE)

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Thank you! This is amazing, it worked!

  12. Assume that there are 3 independent columns in excel with both plus and negative values. The first two columns need to be added and the sign (positive or negative) of the result is noted. Also, note the sign of the 3rd column value on the same row. Then both signs should be compared. If both signs are the same then print “c” on the 4th column. If the signs are different print “w” on the 4th column. Take the 3rd column zero value as a negative value in case you get a zero on the 3rd column. Thanks for your help.

    1. Hi!
      Please use the following formula:

      =IF((A1+B1)*IF(C1=0,-1,C1) > 0,"C","W")

      I hope I answered your question.

  13. IF MY PARAMETERS ARE: IF A SQUAR IS(<than 1M IN WIDTH and < than 25M IN LENTH and <than100M2 in squares( IT WILL BE "patching" otherwise it will be "milling"

    What would be the formula

  14. I have 4 values in different cells MP-50, UP-20,HP-40 AND AP-6 . how to compare a cell value is less than or equal to this value . If a cell contain MP-7 than pls give me formula to solve this

  15. my D column has two options: Apple, Orange
    my E column is the dispense
    My F column is the due date; based on type and dispense date

    Question:
    If D says apple then my F is 21 days from E
    If D says orange then my F is 28 days from E

    How do I format my due date?

      1. I'm making a computation for a crypto game, what is the proper formula for this? I want to leave my battle result column blank but it's still showing "Lose". Can anyone help me with this? Thanks in advance!

        =IF(E8>3,"Win",IF(E8<3,"Lose",IF(OR(E8=0,ISBLANK(E8))," ")))

        This is the formula that I used.

        1. Hello!
          If I got you right, the formula below will help you with your task:

          =IF(E8>3,"Win",IF(OR(E8=0,ISBLANK(E8))," ","Lose"))

          1. Hi Alexander! I tried the formula above and it worked! I truly appreciate it!

  16. Hello, I have a specific need which I have not been able to work out through all my trials and searching. I need the fill color of A5 to be yellow if C5 is 240 greater than B5 but less than 351 more than B5. And also need A5 fill color to be red if C5 is greater than 350 more than B5. Is there a formula for this? Example: A5 is yellow fill when B5=2210 and C5=2505, and A5 is red fill when B5=1965 and C5=2475. Is there a formula out there for this? I appreciate any help to come my way.

  17. Why oh why does this not work:
    IF(AND($D$3="Smith Inc.",$A$2=2),C4+D4,C4)

    1. Hello!
      I don’t know your data source, so I can’t help. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

    2. I'm not sure what's happening when i post my comment, but it's removing the "not equal symbols " The formula should read IF(AND(D3 "does not equal Smith Inc.",A2=2),C4+D4,C$)

  18. Please help with formula. I am working on a project management dashboard.

    I am wanting one cell in the sheet to count the number of times each status is chosen; status was created as "text from drop down list; created list with data validation rule.", the listed choices are: "In Progress", "On Track", "Stalled", "Overdue", "Not Started", and "Complete".

    On my reporting sheet, I have created a cell labeled as "Project Status". In this cell, I need a formula that can calculate how many times each status from the list is chosen and the average of those instances. Finally, I want it to calculate the averaged information as an over all status for the project.

      1. Much appreciated. I will give that a shot.

  19. I have a table and I want to do advanced filtering to find persons with name only beginning with the letter "B" how would I do this?

  20. Please help with the formula to be used in counting the number of Students with the range of 75 - 79.

    That is the data. Thanks, please

    83
    83
    83
    83
    83
    83
    83
    77
    81
    82
    75
    75
    89
    89
    79
    75
    79
    79
    81
    91
    85
    77
    76
    80
    90
    88
    79
    90
    80

    1. Hello!
      You can use array formula

      =FREQUENCY(A1:A29,{75,79})

      Press Ctrl + Shift + Enter so that array function to work.

      It will return 3 values:
      The first is the count of numbers is less than or equal to 75
      Second, the count of numbers is greater than 75 and less than or equal to 79.
      Third - more than 79

  21. Can you explain how Excel might be able to perform the following function?

    A 'true' cell contains the 24 characters A,B,C,D,E,F,G,H,I,J,K,L,1,2,3,4,5,6,7,8,9,10,11,12. A 'false' cell contains duplicated and omitted characters - such as cell A,A,C,D,E,F,G,H,I,J,K,L,1,1,3,4,5,6,7,8,9,10,11,12.

    However, if this false cell A,A,C,D,E,F,G,H,I,J,K,L,1,1,3,4,5,6,7,8,9,10,11,12 is cancelled out by another false cell B,B,C,D,E,F,G,H,I,J,K,L,2,2,3,4,5,6,7,8,9,10,11,12, then these two cells are collectively true because every character occurs the same number of times (twice in this example).

    The project I'm working on involves large numbers of these false cells produced by a fairly random process. Can Excel identify sets of complementary cells that are collectively true? (these sets could be pairs - as in the above example - triplets, quadruplets, or more. Note that the whole list needn't be collectively true...just that collectively true sets of cells can be located by searches.)

    1. Hello!
      If I understood the problem correctly, your task requires a very large number of comparisons. Even if there are 5 cells, the number of possible combinations is 120. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  22. Hi,

    I have the following censored datasets reported during microbiological assay -
    < 1
    < 4
    < 10
    < 6
    < 5
    < 4

    Is it possible to determine the mean and SD of such numbers? If yes, how? And how can such numbers be further statistically analyzed?

  23. Use a formula to define the Students Marks Grade as given below in Column G.
    >=80 is "A", >=60 to ==35 to =<59 is "C", =<34 is "Not Eligible".

  24. plz check where is incorect

    =IF(AND(L5>80,L585,L590,L595,L5100),I5*100%,0)))))

    2. =IF(AND(L5>80,L585,L590,L595,L5100),I5*100%,0)))))

  25. 1+21=22 i need formula of round off one digit 22 = round on 20
    2+34=36 this is round on = 40
    plz reply the formula

    1. you can use Mround function.

  26. Hi,

    What formula should I use for:
    If the value is not equal to "follow" in C4 then get the value in C4.

    Thanks!

  27. Hoping someone can help me with an equation for a cell to equal the lesser of two values. the values are 10% of a full value and the second is 5% of full value.eg
    eg i want to hold 10% of claim value upto 50% of contract value, or 5% of contract value whichever is the lesser

  28. My requirement is that, IF A1 is equal to or less than X % of C1, then the result should be 2 times of B1, OR, IF A1 is greater than or equal to Y% of C1 but equal to or lesser than Z% of C1 then the result should be 3 times of B1. Please explain the command I should give in an EXCEL Worksheet.

  29. Please help me to make formula
    Condition:
    • Less than or equal to +0.002 = Passed
    • Greater than or equal to -0.002 = Passed
    • Greater than or equal to +0.003 = Failed
    • Less than or equal to -0.003 = Failed

    1. Hello!
      The formula below to solve your task:

      =IF(OR(A2>=0.003,A2<=-0.003),"Failed",IF(OR(A2=-0.002),"Passed",""))

  30. i search this:

    =if(L4=FALSE;G4;0)IF(L4=FALSE(AND=B4="testing";F4-G4;0))

    So is "false" then "0" but is L4 False AND B4 TESTING then he (F4-G4)

    but not work this, anyone help me?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(L4=FALSE,B4="testing"),F4-G4,IF(L4=FALSE,G4,0))

      I hope this will help

      1. somebody help me using excel formula pls
        1-10cum=80
        11-15=8/cu.m
        16cu.m.up =8.50

  31. Hi,
    the particular cell 'A' contains number less than 0 to 100, if cell A <=0, B cell should reads "GOOD", if Cell A reads 1 to 7 days Cell B should reads "AVERAGE", if Cell A reads 8 to 15 days than Cell B should reads "BAD".

    Please help.

  32. These are relational operators, not logical operators. Logical operators are OR, AND, NOT, XOR etc.

  33. Hello. I have trouble inserting the function as excel stated that I have too many arguements.
    I4- Type of cab used, C3 - Taxi, I5 - Distance Travelled in KM, C5= $0.22, C6 = $0.18
    IF((AND(I4=C3,I510,I5<=20)),((C5/0.4)*10)+((C6/0.35)*(I5-10)),,)

    I am trying to create a simple calculator for taxi fare but I am not sure if the IF function is even the right function to use. Hoping you could help me out. Thanks!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(I4=C3,I5<10,I5<=20),C5/0.4*10+C6/0.35*(I5-10),0)

      I hope it’ll be helpful.

  34. I have looked through all of the comments and questions and have not seen mine. I am wanted the take the higher value of 2 numbers and add it to the rest of the sum of a list of numbers I wrote it a as =IF(a1b1, sum(c1:f1)""), I have left something out some where.

    1. Hello Miranda!
      If I got you right, the formula below will help you with your task:

      =SUM(C1:F1)+MAX(A1:B1)

      Hope this is what you need.

  35. if A1 more than 6000 than 80, if A1 more than 9000 than 150, if A1 more than 12000 than 200

  36. Please tell me why this is not working.
    =IF(K3<=250000,"250000,K3<500000),"500000,">500K","no")))
    If a value in column k is less than 250K, I want to put <250K
    If a value in column k is greater than 250k or less than 500K, I want to put 500K

    1. Hello Vivian!
      If I understand your task correctly, the following formula should work for you:

      =IF(K3<=250000,"<250K",IF(K3<500000,"500K","no"))

      Hope this is what you need.

  37. Hello, Trying to figure out how to keep a high score. If A<B, then replace A with B.

    1. Hello Benjamin!
      If any values are already written in cells A1 and B1, then using the Excel formula they cannot be changed. Need to use a VBA macro

  38. (Give a function to be USED L2 to give performance (label head “GRADE”) which could be A, B or C on the basis of the MEAN in cell K2. Performance is B for MEAN>= 2000 but less than 3000, A for MEAN >= 3000 and C for MEAN <2000

    1. Hello!
      If I understand your task correctly, the following formula IF should work for you:

      =IF(K2>=3000,"A",IF(K2<2000,"C","B"))

      Hope this is what you need.

  39. Hello
    sir , plz help me for the formula
    =if(a1<=180,370+a1-b1)
    it can'nt execute on excel sheet

    1. Hello!
      There is no "FALSE" argument in your IF formula. I can assume that you need a formula like this:
      =IF(A1<=180,370+A1-B1,A1)

  40. Hello,
    Please i NEED A FORMULAR. eg, if x is less or equal to highlight with eg Green and if greater than x highlight with RED

    Thank You

  41. If Cell A1 is a number, Cell B1 is a > symbol and Cell C1 is another number, What formula do I need to say =A1>C1 (looking to return TRUE/FALSE) by referencing the > symbol in B1? I don't want to type in > into the formula, as the symbol can change based on a separate table. I want to reference a cell to determine the symbol.
    Thanks!

    1. Hello Frank!
      If I understand your task correctly, the following formula should work for you:

      =IF(B1=">",IF(A1>C1,TRUE,FALSE), IF(B1=">=", IF(A1>=C1,TRUE,FALSE), IF(B1="<",IF(A1 < C1,TRUE,FALSE), IF(B1="<=",IF(A1<=C1,TRUE,FALSE), IF(B1="=",IF(A1=C1,TRUE,FALSE), 0)))))

      1. Yes, that should do it! Didn't even think about running multiple IFs, was just looking to pick up the symbol through a reference. Thank you!

  42. Please help. What is the formula that i can use for this.
    If the sum in A1 is greater than zero the value that will appear in A2 is “overstated” if lesser than zero, the value that will appear in A2 is “understated”, if equal to zero the value that will appear in A2 is “perfect”. Please assist. Thank you.

    1. Hello Ken,
      Please try the following formula:

      =IF(A2=0,"perfect",IF(A2 > 0,"overstated","understated"))

      I hope it’ll be helpful.

    2. =IF(A1>0,"Overstated",IF(A1<0,"Understated","Perfect"))

  43. i hv 2 fields of Date (like 01.05.2020, 02.05.2020, 03.05.2020 etc.) & numbers (like 5,9,2,4,8,6) in sheet1 and now in other sheet2, if i find a particular date from sheet1 like 02.05.2020, this should show automatically the value mentioned in just next to that particular date. how to do that with if formula or with any other?

  44. Hi There, i need some help, i have a column with $values in from 100k to 1m+ and i need a formula that would look at the values in the money column and if it's between 100k to 249k = put text "100 to 250"
    250k to 499k = put text "250 to 500"
    500k to 999k = put text "500 to 1m"
    over 1m = put text "1m +"
    Any help appreciated

    1. Hello Steve!
      The formula below will do the trick for you:

      =VLOOKUP(A1,{100000,"100 to 249";250000,"250 to 499";500000,"500 to 1m";1000000,"1m +"},2,TRUE)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  45. I am trying to apply formula =IF(LEFT(T1,1)=0,"Incorrect","Correct").
    If T1 cell contain either 0.00 / 1 or 1.00 / 1 Answer is Correct.
    The expected answer when T1 cell contain 0.00 / 1 is Incorrect.

    Please help me.

    1. Hello Krishnaprasad!
      You are trying to compare text with a number. Convert text to number.

      =IF(--LEFT(T1,1)=0,"Incorrect","Correct")

      Or compare text with text.

      =IF(LEFT(T1,1)="0","Incorrect","Correct")

      I hope it’ll be helpful.

    2. I found the solution. (Sorry for the trouble)
      After converting string to number it worked...
      =IF(VALUE(LEFT(T1,1))=0,"Incorrect","Correct")

      Thank you...

  46. Hi, I know how to use great than and equal to and less than and equal to formula in separate cells however I want to combine them. This is what I know for eg greater than and equal to and less than and equal to. A2 cell would have a number for eg.

    =If(-10>=A2,”yes”,”no”)
    =If(10=X>=10 then output yes otherwise no.

    Thanks, a

    2. So formula for -10>=X>=10

  47. Hello,
    How can I incorporate blank offer dates to return blank rather than FALSE?

    1. =IF([@[Offer Date]]"", IF([@[Offer Date]]<=DATEVALUE("3/12/2020"),"Before", "After"))

  48. Hi,
    I want to incorporate a rule for a cell which should return "Y" or "N" for a value between 2 dates:
    Cell1>Date1 and Cell1<Date2.
    Is it possible to use both the arguments in the same if statement. I tried, but it did not work out.

    Thanks in anticipation.

    1. Hello Prabhu!
      Please try the following formula:

      =IF(AND(Cell1>Date1,Cell1<Date2),"Y","N")

      Hope you’ll find this information helpful.

      1. Thanks!
        What argument should be entered with respect to Date2. Please advise.

  49. In the my data base a column having two types of values "AA6213" & "6213". I am finding the values by "6213". So my query is how can i get the TRUE value even the cell containing value "AA6213"?

    1. Hello Jatin!
      Please try the following formula:

      =IF(IFERROR(FIND("6213",TEXT(A1,"###")),0) > 0,"Yes","No")

      Hope you’ll find this information helpful.

  50. Hi,
    I am trying to calculate:
    If A1 >A2 (by 10%) make A3 = 1
    If A1 >A2 (by 20%) make A3 = 2
    If A1 >A2 (by 30%) make A3 = 3
    and so on?

    1. Hello Burrod,

      If I understand your task correctly, the following formula will do the trick:
      =ROUNDUP((A1-A2) / A2*100, -1)/10

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