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

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

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

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

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

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

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

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

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

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

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

  11. =IF(AND(A1>=80,A1=91,A1=101,A1=111,A1<=121),"SOUTH",))))

    already got it

  12. hi svetlana,

    i have a table of customer sales and want to grade them according to month spend, A, B, C and D. what formula would i need to define as per below;
    A = greater than 80,000
    B = greater than 41,000 and less than 80,000
    C = greater than 25,000 and less than 41,000
    D = less than 25,000 and greater than 0

    thanks in advance and look forward to you reply.

    daniel.

  13. hello i need help,
    for example,
    If A1 is equal to 80 up to 90 = EAST
    if 91-100 = NORTH
    if 101 -110 = SOUTH
    if 111-121 = WEST

    how can i make this in a if statement

  14. =COUNTIF('15 PP YR '!D5:NG5,"f")+IF(B3="FT">480,B3="PT">300)

    Here is what I have attempted. '15 PP YR'is a sheet, D5:NG5-adds up a row, if F. and then not sure which formula to use for if FT = full time the max is 480 turn red if above 480, then for PT = part time the max is 300 then turn red. Thanks for any help you can provide.

    1. I haven't gotten any assistance on my question, is there anything else you need. Thanks for any help, you can provide.

  15. hi please help me to create a formula-

    i want that the value should be count when the cell values is divide by 5 or grater than or equal by 5.

    For ex:- in 2 cell value is 2 0r 3 then sum should be 0 as per my condition (not divide not grater or equal to 5)

    if value 5 or 6 then sum should be 10. (both of divide by 5 and 2nd remains 1 but should not count in sum.)

    Ex2:- in three cell value is 5, 4, 7 then sum should be 10 (not count 4, value less from 5 & not divide 5)
    or
    if value is 15, 22, 34 then sum should be 65. (remains 0, 2, 4 which not counted.)

    or tell me formula for value of %.

  16. I need a assistance with a formula. In column a1 will be either FT OR PT, column b1 totals from sheet1 and needs to include if less than 300 if "PT" then RED OR if greater than 480 if "FT" then RED. How would I write that formula? Thanks for any help.

  17. Hi,
    I need help whit this:

    IF A2=1, how to autocomplete B2=0 and C2=0

    I need to type 1 in first column and the next two columns autocomplet whit 0(zero)

    Thank you!

    1. *in B2 =IF(A2=1,0,())
      *in B3 =IF(A2=1,0,())

  18. Start Week Close Week Week No
    24-Oct-15 30-Oct-15 84
    31-Oct-15 6-Nov-15 85
    7-Nov-15 13-Nov-15 86
    14-Nov-15 20-Nov-15 87
    21-Nov-15 27-Nov-15 88
    28-Nov-15 4-Dec-15 89
    5-Dec-15 11-Dec-15 90
    12-Dec-15 18-Dec-15 91
    19-Dec-15 25-Dec-15 92
    26-Dec-15 1-Jan-16 93
    2-Jan-16 8-Jan-16 94
    9-Jan-16 15-Jan-16 95
    16-Jan-16 22-Jan-16 96
    23-Jan-16 29-Jan-16 97
    30-Jan-16 5-Feb-16 98

    Could you please help me to provide IF(AND) formula for assigning week no
    For eg 20-Dec-2105 which is week 92

  19. 26-08-2015 7,900.00
    26-08-2015 17,917.00
    15-10-2015 10,307.00
    14-10-2015 29,590.00
    15-10-2015 3,864.00
    15-10-2015 24,210.00
    15-10-2015 14,061.00
    15-10-2015 14,631.00
    15-10-2015 48,663.00
    15-10-2015 3,846.00
    15-10-2015 37,751.00
    14-10-2015 4,800.00
    14-10-2015 6,393.00
    14-10-2015 33,025.00
    14-10-2015 91,100.00
    14-10-2015 4,693.00
    14-10-2015 10,657.00
    14-10-2015 33,663.00

    Above Sum is equal to this value 146071/- How ?

  20. Hi Svetlana
    I've have another one for you....
    I'm trying to us an IF formula for the following
    I have a calculation to determine if material was delivered on the date it needs to be delivered
    Formula: =U2-Q2 (Actual Deliver date-Need By date)
    If =0 "On Time" If =V2=0, -1, 1 "On Time" IF > -1 or <-1 "Late"

    How do I create a formula for this?

    1. IN addition, can I create rules based on customer without having to filter or add customer shipments to another tab. Or, is this a formula specific customers to independent tab.

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

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

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

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

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

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

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

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

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

  30. Added to favorites!

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

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

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

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

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

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

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

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

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

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

  41. hi,
    A1=75

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

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

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

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

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

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

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

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

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

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

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