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 6. Total comments: 717

  1. Thank you so much Svetlana for your help thus far, your work on this forum have been amazing.

    1. Hi Tim,

      Thank yo so much for your kind words.

      It's difficult to suggest a proper formula without seeing your data, but I think VLOOKUP is the right function for your task.

  2. Hi, I need a formula for the following:

    If today() is greater than a date in a cell by 4 days, return a result

    thanks for your support.

    1. Hi Irshad,

      Here you go:

      =IF(TODAY()- A1>4, value_if_true, "")

      1. Dear Svetlana,
        Thanks a lot.It works great when I replace the value_if_true with a number, e.g =IF(TODAY()-A954>4,1,2).

        But how do I replace the 1,2 by text?
        I did this, =IF(TODAY()-A954>4,TEST,TEST FAILED), but it doesnt return the text. I get #NAME? as result.

        Sorry to be a pain and thanks again for your support.

        1. Hi Irshad,

          Just enclose the text in double quotes, like this:
          =IF(TODAY()-A954>4,"TEST","TEST FAILED")

          1. Hi Svetlana,

            Thanks loads for your help and support!:)

            Best Regards

  3. im trying to figure out a formula? if cell a1 is less than 13 the value in b1 is 26 if its greater than 13 the value is 24 any help would be great!!

    1. Hi Steve,

      And what if A1=13?

      Anyway, you can use the following formula:
      =IF(A1<13, 26, 24)

      And you can replace A1<13 with A1<=13, if needed.

  4. Svetlana, you've been a huge help on this forum. Thank you so much for all you've done.

    I'm trying to draft a formula to find the number of items that were created on or after a certain date (Held in Sheet1, column N) but not resolved before the date held in Sheet1 column Q. A list of every day of the year is listed in column B, with B3 starting at 1/1/2015, B4 at 1/2/2015, etc.

    e.g. We're needing to track the number of bugs that are in an active state each day, as well as track the number of bugs in a resolved state every day. Column N holds the created date of every bug and Column Q holds the resolved date of every bug. I know how to do a countif to determine if a bug's creation date was the same as the date in column B, but I can't figure out how to make it check if it's still open.

    1. Hi Aaron,

      Thank you for your kind words.

      Does an "open case" mean no date in column Q or the date in column Q is less than today's date?

      If the former, you can check column Q for empty cells, e.g. =COUNTIF(Q3:Q100,"<>"&"")

      If the latter, you can compare a date in column Q with today's date:
      =COUNTIF(Q3:Q100,"<"&TODAY()) You can find more formula examples in COUNTIF in Excel - count if not blank, greater than, duplicate or unique.

      1. Svetlana, thank you for the help. No, it means that the date each case is opened is in column N (N3:N100000)and the resolved dates are in column (Q3:Q100000). I'm needing to find for every day since January 1st how many cases are open on each day and how many are resolved on each day, so we can see how quickly we're reducing the case load. So I need to find, for each date (listed Sheet3!B3:B1000) if a bug was created on before that date, but not resolved (because if it's created January 2nd but not resolved until January 31st it's still active from Jan 2-Jan30), or if it was created on or after that date and also resolved on or after that date.

  5. =IF(G26<=A14,A14-G26,"MET") I'm trying to get my formula to say MET if its = to. But it only reads Mets if I'm a penny or more over. what am I doing wrong?

    1. Hi Walter,

      Since you are using the "less than or equal to" operator in the logical test, the formula reruns the difference A14-G26 if G26=A14. If you want it to return "met" in this case, replace "<=" with "<" like this: =IF(G26<14, A14-G26,"MET")

  6. E2 = 23% and M2=-23% I need a formula to highlight a tolerance. If M2 is +5% over E2 it's orange, if M2 is +10% over E2 it's green. If M2 is equal to E2 it's just clear. If M2 is -1% or less it's RED.

    Can anyone help out, it's driving me nuts

    1. Upon a second thought, that actually makes no difference :)

      You can create 3 conditional formatting rules, say for M2:M1000, with the following percent change formulas:

      Orange:=AND(($M2-$E2)/$E2>=5%, ($M2-$E2)/$E2<10%)

      Green:=($M2-$E2)/$E2>=10%

      Red: =($M2-$E2)/$E2<=-1%

      1. THANKS! worked perfectly

    2. Hi Edd,

      Do you have amounts of percentages in E2 and M2?

  7. how do i take a blank cell, and have that cell equal to the large dollar amount of 4 other cells? so if i have 4 cells with different dollar amounts, i want the blank cell to know to choose the larger amount of the 4 cells to be the value? how do i do this?

    1. Hi Tim,

      You can use the MAX function similar to this:
      =MAX(A1:A4)

      1. Ahh, did not think about that MAX formula. can I call myself an excel wiz... Thank you so much Svetlana

  8. I am looking for a little help with excel please.

    I have two columns (ex: E and F) in which I need to find all rows in which column E is greater than F.

    Is there a formula for finding this?

    ANY help would be greatly appreciated!

    Thank you

    1. Hi Maria,

      To count such cells, you can use an array formula similar to this:

      =SUM(--(E1:E100>F1:F100))

      Remember to press Ctrl + Shift + Enter to enter the array formula correctly.

      To highlight such rows, you can create a conditional formatting rule with the formula =$E2>$F2 where row 2 is the top-most row with data.

  9. I found a SUMPRODUCT for a cell (E8), the Total Cost, using two separate arrays. One that I identified by cell range numbers (B8:D8) the other I identified using the cell range by name (Cost, which was cells B3:D3). I got my numbers for column E just fine, but now I am looking to write a function for the column F that calculates the shipping. I want to find the shipping for each Total Cost at 2%, unless 2% of that total cost is $10 or greater, then I want it to insert $10. I wrote the following function: =IF(E8*0.02>9.99,E8*0.02,10), but for a total cost of $457.00 it's returning $10. But $457*.02=$9.14, which is what it should be putting in the box instead of $10. Where am I going wrong?

    1. NVM, I had a greater than sign instead of a less than sign in the equation. So stupid!

  10. Hi Svetlana,

    I'm looking for a formula where if the value of a certain cell is less than or equal to $50 then the next cell over produces the number 1. If the value of the same cell is less than or equal to $100 the next cell over produces the number 2 and so on and so forth.
    Example:
    If the value of K3 is less than or equal to $50 than M3= 1.
    If the value of K3 is less than or equal to $100 than M3=2.
    If the value of K3 is less than or equal to $150 than M3=3.
    If the value of K3 is less than or equal to $200 than M3=4.
    I would need the formula to continue until about $600.

    1. Hi Dan ,

      You can use nested IF functions like this:

      =IF(K3<=50, 1, IF(K3<=100, 2, IF(K3<=150, 3, IF(K3<=200, 4, ""))))

      1. Thank you very much!

  11. Hi Svetlana, I've been looking for a formula to compare two figures and to divide accordingly. The formula is to create a spread sheet in order to calculate incomes.

    If A1 is more than B1, then average B1 by 12. if A1 is less than B1, then add A1 and B1 and divide by 24.

    1. Hi Carlos,

      I am not sure I understand what "average B1 by 12" is :) Anyway, you can add a proper calculation instead of ... in the below formula:

      =IF(A1>B1, ..., IF(A1<B1, (A1+B1)/24, ""))

      Also, please note that the formula will return an empty string if A1=B1.

  12. I am trying to create a formula for 2 columns that have different sets of numbers

    Column A Column B
    99205 99204
    99212 99214

    1st I need a formula to tell me if column A and B match and if not I need to know if the number in column A increases or decreases and by how much. I need to to say, if it doesn't match, something like "Increased by 2 levels" or Decreased by 1 level". Is that possible?

    1. Hi Alyce,

      Supposing that increasing/ decreasing levels is just the difference between columns A and B, you can use a formula similar to this:

      =IF(A1=B1,"match",IF(A1>B1,"increased by "&A1-B1&" levels",IF(A1<B1,"decreased by "&B1-A1&" levels","")))

      If A1=B1, it displays "match"

      If A1>B1, it displays "increased by X levels"

      If B1>A1, it displays "decreased by X levels"

      1. Thank you very much!!

        2nd question could I combine that formula you gave me above and these 2 formulas:

        =IF(VALUE(MID(E2,4, 1)) = 0, "NEW", "ESTABLISHED")

        =IF(D2=E2,"MATCH", D2& " to " &E2)

        and make one big formula?

        1. Hi Alyce,

          I don't think it's possible because these two formulas have both value_if_true and value_if_false arguments. I.e. if D2=E2, the second formula inserts "MATCH", otherwise concatenates values in D2 and E2, there is simply no room for other logical tests.

          You can, of course, add a few more nested IF functions in my formula if you remove value_if_false arguments from the existing functions. But you have to decide on the order of logical tests, because once the condition is met, the formula won't check subsequent ones.

  13. How do you write a formula for this situation. If value in column is greater than zero but less 15 assign 1, value in the same column is more than 16 but less than 25 than assign 2

    1. Hi Amir,

      Here you go:
      =IF(AND(A1>0, A1<15), 1, IF(AND(A1>16, A1<25), 2, ""))

      Please note, the formula will return an empty string for 15 and 16 because they do not meet either criterion, if you want to include them, use <= and >= operators instead of < and >.

      1. Hi Svetlana,
        Thank you much, it worked perfectly well.

  14. still not working :(

    1. Hi Paula,

      If you can explain your criteria in more detail, I will try to help. I.e. how do you calculate regular hours and overtime?

  15. Hello Shahbazkhan,

    You can use a formula similar to this:

    =IF(AND(B2>70, C2>70), "qualify", "")

    1. Hello Svetlana Cheusheva,

      Thank You very much for reply :) :) !!!!!!!

  16. I have data of employees it shows date and time in same column I want to highlight the late comer and early leaver how can I set the formula say coming late after 8:30 am and leaving before 2:00 pm

    1. Hi Akhtar,

      Supposing the arrival time is in column A and departure time in column B, you can create conditional formatting rules with the following formulas:

      =TIME(HOUR($A2),MINUTE($A2),SECOND($A2))>TIMEVALUE("8:30 AM")

      =TIME(HOUR($B2),MINUTE($B2),SECOND($B2))<TIMEVALUE("2:00 PM")

  17. Suppose i want to put a comment like 80% and above is Distinction, 70-79% is Credit, 50-69% is Pass.what formula do i use so that excel will do it without typing in Dinstinction, or pass or credit or fail myself?

  18. If column A1,B1,C1,D1 are values and I want E1 is greater among them, then what will be the formula in exel

  19. Hi I have a simple problem. I'm not an excel expert at all.

    I have to calculate the hours worked by staff. 9 Hours per day is required, so 45 hours per week means no overtime or deduction for fewer hours worked. When the weekly hours worked is less and equal to 45 i need the answer for overtime worked to be "zero", not true or false. what formula do i use? how do i do that ? im clueless...

    1. Hi Francois,

      You can use an IF formula similar to this:

      =IF(A2<=45, 0, "overtime")

      Where A2 is the Hour column. And you can enter any other text, number or calculation instead of "overtime".

  20. Hi Svetlana

    Great article, very helpful thank you. I was wondering if you could help me with two queries that I can not quite solve myself, despite your helpful article?

    1) I am looking for a calculation for the following if A1 is greater than 5823 I need it to calculate what 1% of any value is between 5824 and 42000 (in other words if the figure in A1 was 50,000 I would need the calculation to be (42,000-5824)*0.01) (if it was 25000 I would need it to calculate (25000-5824)*0.01) (If it was 5000 I would need it to recognise it was less than 5824 and insert a 0)

    2) I would like the spreadsheet to count anyone between age 18 and 67, the spreadsheet would have a list of dates of birth. Also I'm not sure if it can do this but every time I go into the spreadsheet can it recognise the same information based on that days date?

    An answer to at least one of the above would really help me?

    Many thanks

    Michael

    1. Hi Michael,

      Sorry, I am not sure if I fully understand your first task. The below formula works with the following logic:
      - if the value in A1 is greater than 5823, it calculates (A1-5824)*0.01
      - if the value in A1 is equal to or less than 5823, it returns 0

      =IF(A1>5823, (A1-5824)*0.01, 0)

      Please correct me if my understanding is wrong.

      As for the second task, you can use one of the age calculation formulas described in How to calculate age from date of birth in Excel and then use the following COUNTIFS formula:

      =COUNTIFS(B1:B15, ">18", B1:B15, "<67")

      Where column B is the age column.

      1. Hi Svetlana

        Thank you for your help. Step two is perfect I will apply this.

        as for question 1 there is one part missing. The calculation should only be based on 1% of anything greater than 5823 and anything less than 42001. So any amount above 42000 need to be ignored in the calculation?

        Thanks

        Michael

        1. I think I got it. Then we can add the following OR statement in the logical test:
          =IF(OR(A1>5823, A1<42001), (A1-5824)*0.01, 0)

          1. Not quite I'm afraid if I A1 = 50000 the calculation should be (42000-5824)*0.01 = 361.76
            The formula you provided still counts the additional 8000 above the 42000 so it calculates (50000-5824)*0.01=441.76

            I am trying to get the calculation to only take into account anything between and including 5824 and 42000

            Do you understand and can you help?

            Sorry it is quite difficult to explain thank you for your patience

            1. Hi Michael,

              Okay, let me check if I understand the conditions correctly:

              If A1>=5824 and A1<=42000, then (A1-5824)*0.01

              If A1>42000, then (42000-5824)*0.01

              If A1<5824, then 0

              The following formula works with the above logic:

              =IF(AND(A1>=5824, A1<=42000), (A1-5824)*0.01, IF(A1>42000, (42000-5824)*0.01, 0))

              BTW, instead of (42000-5824)*0.01 you can put the resulting number 361.76 directly in the formula:

              =IF(AND(A1>=5824, A1<=42000), (A1-5824)*0.01, IF(A1>42000, 361.76, 0))

              If I misinterpreted any of the conditions again, please let me know, or you can adjust the calculations directly in the formula.

              1. Svetlana

                That is perfect, thank you very much your help is greatly appreciated.

                Kind regards

                Michael

  21. I need a formula to calculate a value based on 4 value ranges. So if A1 is greater than 100, but less than 299, then B1 multiplied by set value, but if A1 if greater than 300 but less than 499, then B1 is multiplied by a different set value, if A1 is greater than 500 but less than 1999, the B1 is multiplied by a different set value and if A1 is greater than 2000 then B1 is multiplied by yet another value. Can you help??? many thanks

    1. Hi Jean,

      You can use a nested IF formula like this:

      =IF(A1>2000, B1*1, IF(A1>=500, B1*2, IF(A1>=300, B1*3, IF(A1>=100, A1*4,""))))

      1. HUGE THANKS!!!!!!!!!!!!!! You've just made my day! thanks

  22. If I wanted to show if A1 is not ABC, the Cell B1 will shows either some text or change the cell (B1) color to RED

    1. Hi Adrian,

      To show some text, enter the following formula in B1:

      =IF(A1<>"ABC", "some text", "")

      To change the color, select B1 and create a conditional formatting rule for it with this formula: =$A1<>"ABC"

  23. PLEASE HELP!
    I have two columns with raw data and they are columns D = Days, E=total pages.

    To report on the data columns I have the following:
    Column L = Total Pages
    Column M = Total line count
    Column N = Less than 3 days

    this is what I need:
    Column L
    Total Pages
    1
    2
    3
    4
    5-10
    11-15
    16-20

    Column M
    Total Invcs
    needs to equal columnColumn E if its 1 page
    needs to equal columnColumn E if its 2 pages
    needs to equal columnColumn E if its 3 pages
    needs to equal columnColumn E if its 4 pages
    needs to equal columnColumn E if its 5, 6, 7, 8, 9,10 pages
    needs to equal columnColumn E if its 11, 12, 13, 14, 15 pages
    needs to equal columnColumn E if its 16, 17, 18, 19, 20 pages

    Column N
    Less than 3 Days
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less
    same as column M BUT only if column D is 3 or less

    1. Columns M and N are where I need the formula that I can't figure out :((

  24. *order weight column ex. 6.2 (A5)*

    The formulas i entered was

    =IF(A5>=5<=12,C5)

    1. Hi Dan,

      Excel cannot understand 2 logical operators like you use. You need an AND statement in this case.

      For example, the following formula will return a value from C5 if A5 is equal to or greater than 5 and less than or equal to 12, an empty string otherwise:

      =IF(AND(A5>=5, A5<=12), C5, "")

      If you are looking for something different, please clarify.

      1. You are an absolute star Svetlana, really appreciate you taking the time to solve my issue, our dogs will never go hungry! :)

  25. Hi

    Need some help with this one: i have two columsn and want to use A1=B1 and should give answer TRUE as it is the same field but getting FALSE?

    1. Hi Adri,

      If the values in A1 and B1 are absolutely identical, your formula will return TRUE.

      If it returns FALSE, then the values are different, though they may look the same. For example, these may be decimal numbers with 1 or 2 decimal places displayed, and these places are identical, while the 4th or 5th place is different.

      There can be a lot of other differences not noticeable at the first sight. Regrettably, it's impossible to spot the exact reason without seeing your data.

  26. I am trying to build a formula that takes data from one sheet and transfers it to another sheet in the spreadsheet if the value in the column indicated on the first sheet equals "Yes". Please help.

    1. I'm also trying to figure this out. I would imagine it would be an If equals sort of thing, but I'm not sure how to do it.

      1. You can use the VLOOKUP function to pull matching data from another worksheet and the IF function to check if the condition is met.

        For example, the following formula checks if the value in B2 is "yes", and if it is, it pulls the data for "Product1" from column C on Sheet1; otherwise it returns an empty string:

        =IF(B2="yes", VLOOKUP("Product1", Sheet1!A2:C10, 3, FALSE), "")

        Instead of "Product1", you can refer to the cell containing your key value, say A2:

        =IF(B2="yes", VLOOKUP(A2, Sheet1!A2:C10, 3, FALSE), "")

  27. Hello, Need some one help to apply logical if condition. There is data in column i want to apply if condition....

    A2 B2 Result should be like
    5 (1550)
    6 (1900)
    4 (1200)
    8 (2600)
    If A2 is less than equal to "4" multiple by 300. OR if its greater than equal to "4" multiple by 350.

    1. Hi Mohd,

      You can use the following IF function:

      =IF(A2<=4, A2*300, A2*350)

      1. Thanks!

  28. I have a simple formula B8*D8, there are times when I want to include various text messages in the cells B8 or D8 and I want the formula to ignore the text and not create an error message. I have tried IF(B8=XXXXX,0,B8)*IF(D8=XXXXX,0,D8) where I have put the x's I have tried everything I can think of to describe any text with no success.

    1. Hi Jeff,

      The following formula returns an empty string if an error occurs, otherwise the product of B8 and D8:
      =IFERROR(B8*D8, "")

      Is this what you are looking for?

  29. Hi! I tried to use this formula but the result is wrong, or it marks the formula as inconsistent... How can I change this to work?
    =COUNTIF(C3:C42,">6<12")

    1. Hi Fany,

      Since you have 2 criteria, you should use the COUNTIFS function and define each condition separately:

      =COUNTIFS(C3:C42, ">6", C3:C42, "<12")

  30. Hi I'm trying to monitor the days of the delivery date if it is on schedule or behind schedule. Can anyone help me please, below are the arguments. Thanks!

    1. If C3>=(10),then 10 days and above behind schedule
    2. If C3=B3, then on schudule

    1. Hi Mushy,

      Here you go:

      =IF(C3=B3, "on schedule", IF(C3>=10, "behind schedule", ""))

  31. I'm trying to get a formula to do the following.
    If B3 matches any cell exactly in a list say K3 through K30 then multiply G3 by .0223. I would like to have a few criteria ranges but if I can get help with the first one, I should be able to replicate it for the other criteria.

    1. Hi Michael,

      Try the following array formula (remember to press Ctrl+Shift+Enter):

      =IF(SUM(--(B3=K3:K30))>=1, G3*0.0223, "")

  32. Hi there,
    I am trying to make a formula based on the cell number falling within a certain range. The problem is: if the number is between 1 and 120 I need it to multiply that number by 11.2. If the number is between 120 and 200 I need to it multiply the number by 10. If the number is between 200 and 280 then I need that number multiplied by 8.5. If the number is between 280 and 450 I need the number multiplied by 7.2, and if the number is over 450 then it needs to be multiplied by 6.6. The number is created by the data I add in other cells. I have tried nested IF's, but the formula only seems to stay true if the number is over 450, The others don't add up to what they should. Can someone please help?
    thanks,
    Cheryl-Ann

    1. Hi Cheryl-Ann,

      The following formula seems to work just fine:

      =IF(A1>450, A1*6.6, IF(A1>=280, A1*7.2, IF(A2>=200, A1*8.5, IF(A1>=120, A1*10, IF(AND(A1>1, A1<120), A1*11.2, "")))))

  33. I am trying to create a formula using if statement that takes 2 values and devides them. If the result is greater than 100 I want it to put 100 in the field, if the result is less than 100 I want it to put the result of the division. this is what I have and it is not working

    =IF(D3/C3>100, 100, D3/C3)

    Can somebody help me with the correct formula.

    thanks

    1. Hi Kevin,

      The formula is correct. However, if C3 is blank or contains a zero, it returns #DIV/0 error because you cannot divide by 0. Is this the case?

      As a workaround, you can wrap your formula in the IFERROR function, like this:

      =IFERROR(IF(D3/C3>100, 100, D3/C3), "")

      If C3 is 0 or blank, it returns an empty string. Or you can type any message within "".

  34. Trying to do conditional formatting on a column for if column K2 is greater than J2 highlight K2. The problem is that the values in the K column are all less than values (example <0.0045) so it highlights all of them regardless of the value. Is there any way to do a conditional formatting formula that will understand the values in the K column?

    1. Hi Tad,

      Try creating a rule for column K with the following formula:

      =$K2>$J2 Where 2 is your first row with data.

      Make sure the rule does not apply to the header row, otherwise the formatting will get shifted 1 cell up.

      1. I have a worksheet where I am trying to do if I4<J4 (an each subsequent row) then highlight, but I've tried:
        1) highlight the cells in column I, not including any header rows
        2) conditionally format rule "=$I4<$J4" (and then choose my formatting)
        3) it says applies to "$I$1:$I2413"
        However, it is not highlighting correctly (some cells where II are highlighted)

        1. Hi Alison,

          You should write the formula for the top-most row of the range to which you want to apply the rule.

          For example, if you want the rule to be applied to $I$1:$I2413, then the right formula is =$I1<$J1 If you want to highlight values beginning with row 4, then make sure the rule based on your formula (=$I4<$J4) applies to $I$4:$I2413.

  35. Hi need help with a formula.

    I'm trying to figure out how to make this happen and hope I can explain correctly...I want the formula to look at several cells and say if this cell has 0.00 then look at the cell before it, and if that cell also has 0.00 then look at the cell before it, and so on for several cells. However if any of them are greater than 0.00 then input that first cell amount and don't look at any further cells.

    1. Hi Janet,

      What you need is nested IF functions similar to this:

      =IF(A1<>0,A1,IF(A2<>0,A2,IF(A3<>0,A3,IF(A4<>0,A4,""))))

  36. Hello,

    I'm working on an inventory and order guide for a restaurant. It's set up so it totals the amount of cases ordered for the week and I'd like to set a condition that if we order 16 cases or more we pay $10.00 per case and if we order less than 16 we pay $13.00 per case. It sure would be convenient if I could have this automatically calculated.. :)

    Any ideas? Thank you!

    1. Hi Tito,

      You can do this using an IF formula. For example:

      =IF(A1>=16, A1*10, A1*13)

      Where A1 is the amount of cases.

  37. Hi,
    I have a query regarding a formula.
    I want to have the following but I don't know how to put that into an excel formula:

    If b20 = greater or equal to g31 I want it to say accept.

    I hope someone can help me with this!

    Thank you!!

    1. Hi William,

      Try the following IF formula:

      =IF(B20>=G31, "accept", "")

      1. Hi Svetlana,

        Thank you for your reply, but unfortunatly it is not working..
        Do you maybe have another idea?

        Thank you!

        1. William,

          The formula is so simple that it simply cannot not work :)

          Please have a look at this example (the formula is in A1). If you want something different, please clarify.

          1. Great, I got it!

            Thank you very much!

  38. If cell A is less than or equal to cell B and less than "2" than its true. Example:

    Time to Close (Days) SLA Business Days Met SLA
    1.3 1 FALSE

    If time to close is less than 2 days I want to "Met SLA" to be True.

    I know this much of the formula to get my true or false statement (=I2>=H2) but can't find how to add the less than 2 without creating another column.

    1. Hi Ash,

      What you need is the AND statement like this:

      =AND(I2>=H2, H2<2)

      1. OMG thank you so much!!! I was about to pull all my hair out!! LOL You're awesome!!

  39. Hi Svetlana,

    Would you be able to help out here? The beginning of the formula works, the rest does not.

    =IF(D4>=2.51,3,IF(2.51>D4>=2.01,2.5,IF(2.01>D4>=1.51,2,IF(1.51>D4>=1.01,1.5,IF(1.01>D4>=0.51,1,0)))))

    The logic behind it is as follows:

    If value in cell D4 is bigger than 2.51 round to 3
    If the value is between 2.01 - 2.51 round to 2.5 and so on

    Many thanks in advance for your help!

    1. please can tell me the Sum formula of category wise Die. 1 to 40 is Window and 41 to 43 is Door then Die .44 is Window how can i apply logical formula to identify Door and window by entering Die Number??

    2. Hi Kasia,

      You don't want expressions such as 2.51>D4 and the like :)

      =IF(D4>=2.51,3,IF(D4>=2.01,2.5,IF(D4>=1.51,2,IF(D4>=1.01,1.5,IF(D4>=0.51,1,0)))))

  40. I need a formula to do the following in cell J6

    If Cell J46*0.03 is greater than J26 then J6 is = to J26 if not it is equal to J46*0.03

    1. Hi Stephanie,

      Here's a formula for cell J6:
      =IF(J46*0.03>J26, J26, J46*0.03)

  41. I need a formula to do the following:
    if cell A1 is >0 then return value in cell A6 but if cell A1 and cell B1 are >0 then return value in B6 but if cell A1, B1 and C1 are >0 then return value in C6...
    Thanks!

    1. Hi Claude,

      You need a nested IF formula like this:
      =IF(AND(A1>0, B1>0, C1>0), C6, IF(AND(A1>0, B1>0), B6, IF(A1>0, A6, "")))

  42. Can you please help me write the a formula to do the following?

    If A is =30 but =60 but =90: Return "SR"

    1. Disregard, my text didn't display correctly, but I figured out the formula anyway.

      Thanks

  43. I want to be select if less the 7500 then select 175 and above 7500 then select 200 how to use the formula use

    1. Hi Pankaj,

      It may be as simple as =IF(A1>7500, 175, 200)

  44. Hi,
    Is it possible to use two function in same sell like =MIN(IF(A1:A5=0, "", A1:A5)) and =Round(A1,0) ?

    1. Nope. Only one formula per cell is allowed.

  45. Iam trying to come up with a formula to fibure materials for a project.
    The project is round columns Our material is 62" wide so and diamater of the material over 29" mean I have to figure the full width of the sheets

    I am trying to have a column for dia of column example
    24" column x 3.146 /2 = 37.752 since this is greater than 29 the formula should read 62" I have used this and made work for greater but it did not work when I had a 18" column that would have been less than 29

    =IF(A2=29,"OK",A1)

    Does this make sense what am I doing wrong?

    1. Hi Gordon,

      I am not sure if the cell references are correct because I don't know your data structure. The following formula returns "OK" if the value in A2 is equal to or less than 29, the value from A1 otherwise. Is this what you are looking for?

      =IF(A2>=29,"OK",A1)

  46. Hi, I have 3 separate dates in 3 different cells. I need the formula to bring back "first" if the date in cell 1 is the most recent date, "Second" if the date in cell 2 is the most recent date or "third" if the date in cell 3 is the most recent date. The spreadsheet has multiple dates which are not defined. Hope you can help. Thanks

    1. Hi Jason,

      You need a nested IF formula like this:

      =IF(AND(A2>B2,A2>C2), "first", IF(AND(B2>A2, B2>C2), "second", IF(AND(C2>A2, C2>B2), "third", "")))

      1. Thanks Svetlana - can you also help with another formula please. I need the formula to show cell ap3 if "first" is in cell cf3 or show cell l3 if "second" is in cell cf3 or show cell bk3 if third is in cell cf3. Can you help please?

        1. I think i've managed to figure the formula thanks. =IF(CF3="FIRST",L3,IF(CF3="SECOND",AP3,IF(CF3="THIRD",BK3)))

  47. Hello.. I'm not very advanced with excel so I hope my query makes sense. I'm trying to create a formula to provide a result in cell E1. The E1 result should be either:

    If A1 equals 'USD' then multiply cells B1 & C1 to provide the result in E1, but if A1 does not equal 'USD' then the result in E1 should be equal to cell D1.

    Hope you can help! Thank you!

    1. Hi Gail,

      Here's the formula for E1:

      =IF(A1="USD", B1*C1, D1)

  48. Hi mam, can you please help me with the formula to compare 3 rows details for eg.,
    A B C D
    54 54 54 Equal
    54 32 54 Not equal

    1. Hi Lavanya,

      Here you go:
      =IF(AND(A1=B1, A1=C1), "equal", "not equal")

  49. Hi,
    Please help me workout the following:
    Determine if value in cell A1 is equal to the value in cell B1, return the difference in values (whether it is positive or negative value).

    1. Hi Melik,

      If my understanding is correct, you need a simple calculation like =B1-A1

      Please correct me if you are looking for something different.

  50. Hi
    I am trying to set up a training data sheet, I have put the date ie todays date in A1, in another cell =IF(I7<TODAY()-335,TRUE,FALSE) I have entered this data. In theory when i enter a trained date within this time it goes green, but if I want it to turn amber with 30 days to go how would I input this data before it goes to red.
    I have input the data in to the conditional formatting as new rules but can not get it to change to amber when it has 30 days before it expires, can you help

    Thank you

    Craig

    1. Hi Craig,

      I am sorry I do not quite understand the condition. But we have an article that explains different aspects of Excel conditional formatting for dates. It includes a lot of examples on how to highlight dates within a certain date range. Hopefully you will find a solution there:
      How to conditionally format dates and time in Excel

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