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

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

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

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

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

  5. I am trying to get a formula to work to pull a price from a different page based on a number on my main page. ex. if the number is greater than 0 but less than 5 it is one price but if it greater than 5 and less than 10 it is another. Any suggestions?

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

  7. Hi.
    I am doing the lateness attendance record. We have 2 shift, morning and evening. For example, those who comes above 0706 and 1906 is late.
    If I need to put it into formula, how do I do it?
    I was thinking to do =if(A2>0705,"L") but how do I combine it with evening shift.

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice, sorry.In what format is your time recorded? In text format like number or time format? Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      1. How about this conditions

        If the value in A1 is greater than or equal to 250 but not equal to 400,return 250, and if A1 is greater that or equal to 400 but not equal to 800,return 400, and if the value in A1 is greater than or equal to 800 bit not equal to 2000,return 800

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

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

  10. I have a cell that needs to auto calculate an amount if a 25% is not achieved to make the cell equal to 25. Is there a way for excel to auto calculate that value?

    Any help on this would be greatly appreciated.

    1. Hello!
      Sorry, it's not quite clear what you are trying to achieve. From what date do you calculate 25%? Do you want to calculate the sum or set the value to 25? Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  11. Hi everyone, need help on this problem.

    A1 = Karen (Name of Tenant)
    B1 = Total Monthly Rental Payment
    C1 = Rental Rate per month - 10,000.00
    D1 = Add VAT 12%
    E1 = Less EWT 5%
    F1 = PEZA accredited

    i want that result on B1 should be non-VAT 12% exclusive if the tenant is PEZA accredited

    pls. help me on my current formula

    current formula: =(I5+G5)-H5

    Thank you in advance

  12. Hi there,

    I have some excel question and below is some examples of the question.

    I have a pallet and I would like to arrange my box on the this pallet. My Box stacking is not exceed more than 5 feet.

    Pallet size is 1100x110x130 in mm.
    Box size is 406x345x258 in mm

    Base on size dimension above, manual calculation is as below

    X : 1100 / 406 = 2.709 (this means only 2 box I only able to arrange in X direction)
    Y : 1100 /345 = 3.188 (this means only 3 box I only able to arrange in Y direction)
    Z :[ (5ft x 12 x 25.4) - 130] / 258 = 5.403 (this means only able to arrange the Box in 5 layer, Z direction)

    Total Box in one pallet is = 2 x 3 x 5
    = 30 boxes / pallet

    The question is how can I change the answer 2.709 become 2 using excel equation?

  13. Formula to return cell (eg A21 value) if value lies between >X<Y where X&Y are cell values. Please! Thanks

  14. I have huge data with different Values and have to derive at some classification based on three data sets.

    Col A & B has Numbers as Values and Col C has Names as Values

    Col A has Values ranging from 0 to 1000
    Col B has Values ranging from 0 to 1000000
    Col C has Values WHITE, RED, ORANGE, BLUE

    Could anyone help me to get this please. Below is an example of what I am looking for.

    if A is greater than 1 and equals to 4 and is WHITE or RED term it as Medium
    if B is greater than 8999 and equals to 16999 term it as Medium

  15. I have a formula that I am stuck building It is using the below information. Can you please advise how to build the "if then" formula cell C5 for the below? I am stuck on trying to get the in between numbers to work. so far I have gotten = IF(C5<4,5,(C5

    5 = 0 to 4
    4 = 4.01 to 12
    3 = 12.01 to 24
    2 = 24.01 to 28
    1 = 28<

  16. I HAVE THREE SHEET, WITH SAME HEADER BUT VARIOUS VALUE IN IT, HOW TO KNOW WHICH SHEET DATA LESS THEN OUT OF THREE. CAN ANY OPTION OR COMMAND TO PRINT LESS SHEET NAME

    SHEET NAME
    1. 3338 DATA A 1620
    2. 5498 DATA A 1550
    3. 5913 DATA A 1820

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

  18. Hi,

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

    Thanks!

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

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

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

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

          1. USING logical operation sir. 1-10 cu.m equivalent of 80pesos,in excess of 11-15 is equivalent of 8 per cu.m. in excess in 16 up cu.m 8.50 per cu.m.
            it will be use this one of our water billing

            1. Hello!
              You don't need to use a logical operation here. Your explanations are not very clear, but I tried to guess.
              Try the following formula:

              =MIN(1,C1)*80+MAX(0,C1-16)*8.5+MAX(0,MAX(0,C1-MAX(0,C1-16))-10)*8

              Hope this is what you need.

              1. Thank you so much sir, ...
                god bless you always team

              2. THANK YOU SIR, BUT I HAVE ONE PROBLEM I CANNOT COMPUTE THE CUBIC METER AFTER 16 to up?
                16-up is 8.50 per cubic.

                sample
                16 cu,meter =128.50
                17 cu.meter = 137
                18 cu.meter =145.50

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

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

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

  26. I have an excel sheet with data on the amount of donations a person gave for 3 specific years.
    Is there a formula/function that can look at the 3 rows, and output if there was an increase/decrease/remained the same between the years?
    For example: if i have:
    2017 = 100 or 50
    2018 = 150 or 100
    2019 = 50 or 150
    -> a formula that can be used to say "this person increased donation amount over the years" or "there was an increase, but then a decrease in donations"

    1. Hello!
      Explain what does your baseline mean? What does "100 or 50" mean? It is not a number, but a text. It cannot be compared.Give an example of the source data and the expected result.

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

  28. Sir,
    How to get the result for below quary
    if a2[a2=(c1+d1)] column value is same as a1[a1=(c1+d1)] then a2 should be blank other wise a2
    Kindly help me...
    Thanks in advance.

  29. I am trying to do a conditional formatting in Excel where if a specific cell (i.e. cell D10) matches another cell (i.e. cell b3) than D10 would be green, if it does not match it turns red. The problem I am having is there are unseen decimal points involved. Is there a way to set up conditional formatting where it reads the whole number, not the decimals? I cannot remove the decimals as the formulas are pulling from other spreadsheets.

  30. How do I write the following formula:
    IF F23>0 and 25 and 50 and 75 and <=100, then "4"

  31. for example
    i have 25000, less than =150000/- = 25000 ans
    but greater than 150000/- = ?

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

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

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

  35. Hi!
    I need to find duplicate symbols in cell, if there is equal 3 or more consecutive symbols (numbers). Each cell can consist of different amount of symbols and equal consecutive symbols can be in beginning or middle or end. Could you please help? Thanks in advance!

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

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

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

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

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

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

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

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

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

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

    1. Hello Jennifer!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

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

  46. Hi, I have a question regarding combining 2 Boolean conditions
    A, B, C are my conditions e.g. A = "a", B = "b", C="c"
    My question: 'Or(Or(A,B), C)' equals ' A Or B Or C' ?
    'And(And(A,B),C)' equals 'A And B And C' ?
    Thank you for answering

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

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

  49. I want to Check the value of a Column less than an amount and want to Multiply the same with rate

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

      =IF(A1<100,A1*$B$1,A1)

      If that's not what you wanted, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  50. I have two columns I want to compare if value of colum a cell is equal to any cell of coloum b than it come in front of coloum a eg

    A b

    4 7

    7 4
    9 3

    Then 4 come in front of 4

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