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

  1. Hi Elexed.Can you put this formula
    =$G8+MIN(IF($F8<5,5000,IF($F8<10,7500,IF($F8<15,10000,IF($F8<20,12500,IF($F8<25,15000,IF($F8<30,17500,20000)))))),ROUND(IF($F8<5,0.2,IF($F8<10,0.3,IF($F8<15,0.4,IF($F8<20,0.5,IF($F8<25,0.6,IF($F8<30,0.7,0.8))))))*G8,0))

  2. I need these condition:
    1. If b=0 = a/2
    2. if b<=0, a<=0 = 0
    3. if b<=0 = 0

    I use these 2 formula :
    =IF(E1=0,"",IF(D1<=0,"")))
    =IF(E1=0,D1/2,0),elseif(E1<=0&D1=0,0)

    but when b<=0, a<=0 this is not working. that I want to mean- if both b&a cell negative or <=0 it is not working. Is it possible for those 3 condition apply in excel.

    Please Help me.
    Dider-E-Elahi

  3. Hi,

    I am trying to use IF/AND to look at two cells that each could contain either "TRUE", "FALSE" or 'blank'. and return either "G" if both are TRUE ... "A" if one cell is "TRUE" and the other either "FALSE" or 'blank' ... "R" if neither contain "TRUE" or both are "FALSE" or 'bank'etc..

    I have tried this formula
    =IF(AND(Sheet2!B11="TRUE", Sheet2!B14="TRUE"),"G", IF(AND(Sheet2!B11="TRUE", Sheet2!B14="FALSE"),"A", IF(AND(Sheet2!B11="", Sheet2!B14="TRUE"),"A", IF(AND(Sheet2!B11="", Sheet2!B14=""),"R", IF(AND(Sheet2!B11="TRUE", Sheet2!B14=""),"A", IF(AND(Sheet2!B11="", Sheet2!B14="FALSE"),"R", IF(AND(Sheet2!B11="FALSE", Sheet2!B14="FALSE"),"R", IF(AND(Sheet2!B11="FALSE", Sheet2!B14=""),"R", IF(AND(Sheet2!B11="FALSE", Sheet2!B14="TRUE"),"R")))))))))

    But the only permutation that returns a result is if they are both blank.. the rest return "FALSE"

    Please help

  4. Hi!Kindly affix one formula in proposed salary column which may taken into account tenure in Hospital, present salary & auto increase & %age of present salary as per given policy schedule & pick the figure which is lower side & then apply the same amount as Marginal Increase in salary. In short i want to implement the policy schedule for proposed increase in salary.
    1 A-0412 2/1/2014 29D.10M,1Y 34,225 (34,225)
    2 A-0416 1/27/2015 3D.11M,0Y 33,472 (33,472)
    3 A-0431 1/1/2007 29D.11M,8Y 38,824 (38,824)

    Range of Years Worked Maximum Limit of Increase in Salary
    Below 5 Lower of 5,000 or 20 % of present salary
    5-10 Lower of 7,500 or 30 % of present salary
    10-15 Lower of 10,000 or 40 % of present salary
    15-20 Lower of 12,500 or 50 % of present salary
    20-25 Lower of 15,000 or 60 % of present salary
    25-30 Lower of 17,500 or 70 % of present salary
    30-35 Lower of 20,000 or 80 % of present salary

  5. Hello,

    I have a bunch of numbers that are updating in column B (around 100 rows of percentages and their symbol in column A) every fifteen minutes. I want to display only the ones that are less than -5% or greater than 5% with their symbol. Is there a way to do this and display it in a new list so it only shows these values in a condensed list?

  6. Hi Svetlana

    Now it my issue is resolved, You are "GENIUS", Thanks a lot, Have a Great Happy Life and Happy New Year, God Bless You.

    I really appreciate and thank you again.

    Asad

  7. Hi Svetlana

    I mean to say, for example if I receive 10 items in "QTY RECEIVED" in Col-A and issue 11 items in "QTY ISSUED" in Col-B then your formula is showing -1 in "QTY BALANCE" in Col-F, I want that is should also show "error" in stead of -1.

    Thanks

    Asad

    1. Hi Asad,

      Here you go:
      =IF(AND($B2<=$A2, SUM($C2:$E2)=$B2), $A2-$B2, "error")

  8. Hi

    How to check greater than 30 but less than 60 in a cell. Imagine value is in Cell A1.

    1. Hi Ihsan,

      You need to use an AND statement like this: AND(A1>30, A1<60)

      For example, =IF(AND(A1>30, A1<60), "OK", "Not OK")

  9. Hi Svetlana

    can you please help me about find out formula against my Question No. 246, I will really appreciate.

    Thanks

    asad

    1. Hi Asad,

      If my understanding of your task is correct, the following formula should do a trick:

      =IF(SUM($C2:$E2)=$B2, $A2-$B2, "error")

      1. Hi Svetlana

        Thanks a lot for your help, it is almost done, formula is working except a little more help, if value of column A and B does not match then it should also show error.

        Thank you again.

        Asad

        1. Hi Asad,

          My understanding was that A and B can be different values, because we calculate their difference. Please clarify what you mean by "A and B does not match".

  10. Hi

    I need a formula when, whereby the value ranges from -0.03 to 0.03, it should appear as True. And if there are other values, it should appear as False. Please help with this. Thank You.

  11. Hello,
    I'm working on a spreadsheet for grades in my class. I have a column that includes the total points earned by each student. I would like to set up a rule that would give an output if the total points are within a particular range. For example, if the total points are between 360 and 400, the output would be an "A," If the points are between 320-359, the output would be a "B" etc. I was able to make it work with an IF(AND but once I added a third condition, it wouldn't work. I'm also not sure how to format the rule when I want the cell I'm referencing to be in between a set of numbers. For example 319<Cell<360. What do you recommend?

    This is my grading scale in case it helps.
    A 400-360
    B 359-320
    C 319-280
    D 279-240
    F 239-0

    Thanks for your help!!

  12. I am trying to find a formula that will take a (24 hour) time in one cell, compare it to a (24 hour) time in a different cell and if the second cell is greater than "13:00:00", enter "13" in a separate cell.

    From my information:
    if 24:00:00 (in cell e9) is greater than 13:00:00 (in cell f9), then cell h9 will equal "13".

  13. Hi Svetlana

    I am making inventory sheet and facing problem about calculation the Balance QTY. let me explain here; I have made total 6 columns;

    Col A - 10 QTY. Received
    Col B - 7 QTY. Issued
    Col C - 4 QTY. Installed
    Col D - 1 QTY. Returned
    Col E - 1 QTY. DEAD
    Col F - QTY. Balance

    I have purchased 10 items and issued 7, that means 3 items should be shown in balance, but my inventory record justifying total 6 items of column C,D and E. So it should show error in column F until I find 1 missing item? Because I have issued total 7 and if total items of C,D and E matches with column B then 3 items should show in column F Balance.

    I will really appreciate for your help.

    Thanks

    Asad

  14. i want compare Column F2 & E2 for 10% difference and display it fg in windings3 in column C hoow can this be achieved?

    This is what i have at the moment.

    =IF(F2=E2*0,9,F2<=E2*1,1),"fg","i"))

    1. =IF(F2=E2*0,9,F2<=E2*1,1),"fg","i"))

      1. Hi!

        You probably meant this one:
        =IF(AND(F2>=E2*0.9,F2<=E2*1.1),"fg","i")

        Alternatively, you can embed the percentage change formula in the logical test:

        =IF((F2-E2)/E2<=0.1,"fg","i")

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

  16. Hi Svetlana

    I am making inventory sheet and facing problem about calculation the Balance QTY. let me explain here; I have made total 6 columns;

    Col A - 10 QTY. Received
    Col B - 7 QTY. Issued
    Col C - 4 QTY. Installed
    Col D - 1 QTY. Returned
    Col E - 1 QTY. DEAD
    Col F - QTY. Balance

    I have purchased 10 items and issued 7, that means 3 items should be shown in balance, but my inventory record justifying total 6 items of column C,D and E. So it should show error in column F until I find 1 missing item? Because I have issued total 7 and if total items of C,D and E matches with column B then 3 items should show in column F Balance.

    I will really appreciate for your help.

    Thanks

    Asad

  17. Ok, I have multiple formulas going on.

    Cells A:A I input different numbers from 0 to 38.

    Cell ak1 =0, al1 = 1, am1 = 3 etc.

    Under each cell example (ak2, I have =countif (a1:a120,ak1) giving me the results of how many times ak1 (0) appears in column a1:a120.

    Each of these results in different amounts. I would like to sort the greatest amounts to the lesser amounts displaying the number in ak1 not ak2 thru cells b2:b39

    For example if 9 shows up 7 times and is the most I want 9 to show in cell b2 and then then next lesser number to appear in b3, etc.

  18. =IF(G10>=0=1000=1000=6000=9000,"EXCELLENT")

  19. Hi I need to compare Data in 2 sheets on a daily basis. I have a list of products names (in Alphabetical order) in Row A that have best before dates (CODE LIFE) in row I. I need to know if the best before date changes at all by + or - 5days. However the products in row A may not always be in the same order or even there as we may have sold out of the product under that BBD or Sent more product! today I may have 3 lines of Product A but tomorrow I may only have 2?? could you help please??

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

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

  22. i need help on this its about paye(pay as you earn)
    want to work this out
    if B11 is greater than 25000 it must subtract 25000 from it and the remainder multiply by 30% and add 750, if less than 25000 but greater than 20000 it must subtruct 20000 and multiply the remainder with 15% and if its 20000 below it must do nothing

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

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

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

  26. Hi Svetlana Cheushev,

    I have to calculate c21*d21 to f21, if f21 value is = 20 then f21 should show the actual value in F21

    can you help please.

  27. my previous post doesn't show everything I typed:

    I would like to have a well report a p value asterisk according to the following criteria:
    >0.05 "ns"
    less than or equal to 0.05 but greater than 0.01 "*"
    less than or equal to 0.01 but greater than 0.001 "**"
    anything <=0.0001 "***"
    Can anyone help?

  28. I would like to have a well report a p value asterisk according to the following criteria:
    >0.05 "ns"
    0.01 "*"
    0.001 "**"
    anything <=0.0001 "***"

    Can anyone help me write this function? Thanks!

  29. Hi i would like help with my formula
    If value1 is lower than 30 and value2 is lower than 30 equals 24,
    if not value1 is greater than 30 and value2 is greater than 30 equals 22,
    if not value1 is greater than 60 and value2 is greater than 60 equals 20.

    What i want is to check the size of something and give a range according to 2 measures, i mean if size is 24x28 the value is 24; if size is 24 x 40 the value is 22; if value is 60x24 the value is 20. It all depends on both sizes.

  30. Want to put age groups against age collected in a column. Was using if(A1>=20&A1<=25,"20-25","OKAY","NOT IN AGE LIMIT"). but not getting answer. Wanted to use nested if for more options.

  31. what is the wrong in the below formal

    =IF(G65,G610,2)

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

  33. I have three categories
    First on = 1
    Second one = 1.5
    Thread one = 2
    How I can do if function to get the below result
    = if (cell Nuber on 5 and <10 ,1.5,If cell Nuber on <10,2)

  34. I have three categories
    First on = 1
    Second one = 1.5
    Thread one = 2
    How I can do IF function to get the below result
    = if (C15 and <10 ),(1.5),If (C1 <10),(2)

  35. Apols Svetlana, seem to have deleted some of the text by accident before sending last comment. It should read:
    The formula i am trying returns the Medium scores as FALSE, which is: =IF(BU10>15,"High",IF(BU10>7<16,"Medium",IF(BU1015,"High",IF(BU107,"Medium",IF(BU10<8,"Low"))) it won't recognise the any of the Low scores.

    Kind regards,

    Terry

  36. Hi Svetlana,

    On a spreadsheet I am trying to give a word label to ranges of values in the previous cell. The formula i am trying is:=IF(BU10>15,"High",IF(BU10>7<16,"Medium",IF(BU1015,"High",IF(BU107,"Medium",IF(BU10<8,"Low"))) it won't recognise the any of the Low scores.

    Can you advise?

    Kind regards,

    Terry

  37. How do I solve with a formula when I input cell A1 any value ranged from 1000 to 7150 then cell A2 is equal to formula (A1/1000)*3.5, otherwise A2 should displays as 0?
    Thank you!

  38. How can you use the "unique" conditional formatting function ONLY when the cell is greater than zero or ""? Thanks

  39. Hi. My problem is this
    I need the sum of cell:
    A1: where when I type 1 it would have a value of 20, 2 would have a value of 25, and 3 would have a value of 60

    B1: where whatever value i would put would be multiplied by 60

    thanks very much

  40. hi,
    i just wanted to know that how can i compare from 3 row with each other with the percentage eg:A col contain 2.2,B col contain 2.25 and c col contain 2.23
    now which is greater by 5% can anybody help on this

  41. I am attempting to find imbalances amongst sum formulas. Some of the cells contain errors (#N/A) that I want to ignore. How do I do that?

    =IF((AND(J20,M20,P20)),"","X") is my current formaula but returns #N/A because of the errors. What should I change?

    Thanks

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

  43. Hi,

    I have 3 cells, A1 Total ordered (numbers) and B1 received orders (numbers), C1 should tell me the date when A1 and B1 matches.
    Is that possible?

    Or even better if possible:
    Total ordered A1 is counting dates typed in the column A2-A100. Same with received orders B1, counting dates from B2-B100.
    C1 could find the latest date from B2-B100 and then in formula make sure A1 and B1 matches to show the latest date from B2-B100, if no match then empty.
    That would make sure that today date is not late due to typing of the dates in the A and B column.

  44. how can i program a value that when i encode A instead of 1... so that when it will be summed up, it will still count as 1 and not 0?

  45. Hello,

    I need some help for an IF function to display the logical test for a number which is "greater than or equal to 1 and less than or equal to 30".
    Could you please suggest what I can put for this in the logical test?

    Twinkle

  46. Hi Svetlana, thank you for your help. I have two more questions:

    1)I need to have the following If statement return a blank value (a cell that is truly blank and doesn’t say “blank”) if it is false

    =IF(C10>I10, "YES")

    2)Using conditional formatting, is there a way to have two rules in one cell. For example, say I have a formula in C1 that can return a value of either “YES” or “NO”. If in cell D1 I want the cell to be highlighted green if the value in C1 is “NO” and red if the value was “YES”. How can I accomplish this?

    Thanks

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

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

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

  50. I need to creaate a formula that calculates an amount based on the following:
    If amount =1,000,000 multiply by 70%, but if it is between 1,000,001 and 1,499,000 multiply by 80%, but if it is greater than 1,500,000 multiply by 90%, if it less than 1,000,000 make it = 0. Can you help?

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