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

  1. I have a problem in using excel in this scenario, what if in column A i have values like 1.0, 1.1, 1.2,1.3, 1.4, 1.5 up to 3.0 and in column B i'd like to see the result that if values in column A are from 1.0-1.2 it should be equal to 18.00 in column B, if values in column A are from 1.3-1.5 it should be equal to 17.40 in column B, and so on... what do i have to do if i have infinitely many possible input value within a given range but should only have 1 specific outcome value? tnx.

  2. Hi there. I need to find out how to do the formula for the following.
    Column a is type of vehicle
    column b is computer time
    column c is my time.
    COLUMN E IS DIFFERENCE (EITHER POS OR NEG)
    now some times the computer is faster so what i want to say is that if column b is greater than column c then it must put in the difference. IE MY TIME FASTER

    BUT IF COLUMN B IS LESS (QUICKER) THAN COLUMN C THEN THE FORMULA MUST SUBTRACT COLUMN B FROM COLUMN C AND ENTER DIFFERENCE AS A NEGATIVE IN COLUMN E

    I HOPE I EXPLAINED THAT RIGHT. PLEASE HELP.
    MANY THANKS

  3. very good
    i am working in bescom i like your diffrent formulas

  4. Hi,

    Thanks so much dear Svetlana, it worked, I used the page your referred me to.
    This is used for a translation quality assessment rubric.
    Here it is:
    =IF(D32>=90, "Outstanding", IF(D32>=80, "Acceptable", IF(D32>=70, "Revisable", "Weak ")))

    Thanks so much! :)

  6. Hi again,

    Sorry, the reply is scrambled. Could you please fix this:
    =IF(I29 < D32 < I28, D33="Outstanding"), IF(I31 < D32 <I30, D33="Acceptable"), IF(I33 < D32 < I32, D33="Revisable"), IF(D32 < I34, D33="Weak")

  7. Hi Svetlana,

    Thanks for your wonderful support.
    If possible help me with these:
    If D32>89.99 and D3279.99 and D3269.99 and D3270 then D33 = Weak

    Thank you so much in advance. :)
    Bijîk

  8. Hi Svetlana,

    I am currently trying to write an equation for calculating how many people to do a task.

    If I need to count units in a warehouse, I need a certain amount of people on hand if there are certain quantities in a warehouse.

    Say I had these units to be counted. Following figures in units:

    A1 = 5000
    B2 = 29999
    B3 = 99999
    B4 = 1000000
    B5 = 2000000

    Between A2:B2 I need 1 person, between B2:B3 I need 2 people, between B3:B4, I need 4, people, between B4:B5 I need 10 people.

    How would I write this as an equation? There's a lot of different mixed views in the comments of the article page and I am certainly confused hence me asking to be your student for a day.

  9. I need a formulae for H Column equals the same value in E5 and K30 and E7 and K40...The values are 10221 and 12227 respectively

  10. Does anyone know what the "but no less than" formula is?

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

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

  13. Hi

    Hope you can help. I need a couple formulas:
    1. Need formula to look a LOS column lists 0 years, 0 months to 41 years, 8 months that states if the LOS is greater than 6 months, but less than 1 years, Y for yes.
    2. Need to look at text in column on one worksheet, and if matches on other worksheet which list job codes with job banding, list the job band created for the other worksheet.
    Any help would be appreciated!

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

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

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

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

  18. *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! :)

  19. Hi great explanations on your page! I wonder if you could help me?
    I'm trying to calculate shipping costs for our dog food order. I have a delivery weight column ex. 5kg-12kg and a price column ex. £6.50 (C5) along with an order with column ex. 6.2 (A5) Using an IF formula i want for the whole range upto 200kg
    I've entered =IF(A5>=512<=24,C6) etc etc
    However it tells me that I can't compare a boolean to a number because they are different types, I tried entering the double unary you suggested "--" but without success. Can you tell me what I'm doing wrong?
    Many Thanks, appreciate your time :)

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

  21. Hi,

    Need a little help with a logical if condition if you have time.

    I have three groups of ranges each with 3 questions, and they can have a value of high, medium or low i.e.
    range 1 - H / H / M
    range 2 - H / M / M
    range 3 - H / L / L

    If all of the values in range 1 = high, and the values in range 2 and 3 = high or medium, then return high
    If any of the values in range 1 = high or medium, and 2 or more in range 2 and 3 = high or medium, then return medium
    If any of the values in range 1 = low, and 2 or more in range 2 and 3 = low, then return low

    Thanks for any help possible.

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

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

  24. I have part numbers in a column, some contain the letter V at the end and some do not. I want a formula that indicates the part numbers that contain the V to simply display the value in another cell, and the ones without a V to calculate a simple multiplication.

    Thanks
    Vic

  25. A CPA associate answered my question: =IF((ISTEXT(B8)=TRUE),0,B8)*IF((ISTEXT(D8)=TRUE),0,D8)
    easy peasy thank you for your concern.

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

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

  28. Sorry the above arguments is not complete. Here's the complete one.

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

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

  30. Hi There,

    I am working on a report where I have to compare three columns to match a Tier 1-5 based on dollar value in a second column and a risk rating in a third.

    i.e Col c is Tier, Col f is Risk (High, medium, low, blank), col H is total cost $value. I need to be able to filter out errors where if it says tier 2, it could have any risk rating but must fall in to threshold of $10MM to $49,999,999.99 OR if it is rated "high" risk and falls between threshold of $1MM to $9,999,999.99.

    I need to show times when there is improper matching of tier and dollar value and highlight those records.

    Any formula suggestions would be helpful!
    Thanks!

  31. Hi,
    I am trying to prepare aging report in one column there are amounts related to different years. For example
    1- 12-Jan-2013 8,000
    2- 12-jan-2014 9,000
    3- 12-jan-2015 10,000

    so I want amount related to 2013 in One column, for 2014 in next means each year in different column.

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

  33. Hi,

    How can fix this formula. Pls revert. thanks in advance

    Value is 31
    Week 1- (Normal Hrs*Rate+OT@Rate)Value is 1>=7, Ruselt will be colomn 1
    Week 2- (Normal Hrs*Rate+OT@Rate)value is 1>=14,Ruselt will be colomn 2
    Week 3- (Normal Hrs*Rate+OT@Rate)Value is 1>=21,Ruselt will be colomn 3
    Week 4- (Normal Hrs*Rate+OT@Rate)Value is 1>=28,Ruselt will be colomn 4

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

  35. thank you

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

  37. It will be appreciated if you could assist with a formula I already have.

    I am already using formula:

    =IF(A17>25000,(270+((((ROUNDUP(A17,-3))-25000)/1000)*6)),IF(1000.0125000,(30+((((ROUNDUP(A17,-3))-1000)/1000)*10)),30))

    My questions is: What I am missing when the value is zero the fee should be zero, but I am getting $20

    Based on contract price:

    A. Up to and including the first $1,000.00 of contract price or fraction thereof, the fee shall be $30.00.

    Thereafter:
    B. Add $10.00 per each additional $1,000.00 up to $25,000.00 of contract price or fraction thereof. $30 +($24(k) x $10) = $270.00
    Thereafter:
    C. Add $6.00 per each additional $1,000.00 after and over the initial $25,000.00 of contract price or fraction thereof.

    $30 + (24(k) x 10) = $270.00 + $6.00 for each additional $1,000 after $25,001 and over.

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

  39. Dear Please tell me the formula for the bellow condition

    I am trying to set up a result based on the score achieved, example if some one got <80 should study Starter book and if some one got < 105 should study book 1 and if some one got <120 should study book 2 and so one tell book 5, please help me in this regard. ASAP.

    1. Hi Mohammad,

      You can use nested IF functions like this:

      =IF(A1<80, "starter", IF(A1<105, "book 1", IF(A1<210, "book 2", "")))

      Where A1 is the score.

  40. Hi,

    Please help me to find the solution

    A2 = Day or Week or DayWeek
    B2 = Arive date
    C2 = Depart date
    E2 = The number of days

    Thsi is a hotel and guest stays the charges are days and weeks, lets say a guest stays for 7 days the then the charges should be calculated for 01 week and balance days, so how to calculate this if A2=Day the answer total days, if A2=DayWeek the answer the total days but should be reduced by 07 days (01 week)

    Eg
    A2 => Day or Week or DayWeek
    B2 => 01/04/2015
    C2 => 10/04/2015
    E2 => if A2 is day the 10 days
    E2 => if A2 is Week then 01 week
    E2 => if A2 is DayWeek then 03 days (total days - 01 week)

    I want to know the formula to calculate if A2 is DayWeek then the answer should be remain days which reduced the week from total days (10-7=03days)

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

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

  43. I am trying to have date cell turn colors when over 30 days unless next cell has date in it?

  44. Eg, I got a starting time at 9am and an ending time at 11am and would like to condition that if the number of hours is less than 4 hrs, it will give me 4hr and if more than 4 hours, it should give me 8 hrs. Can anyone help? greatly appreciate any help as I tried all means and unable to solve this. A thousand thanks

  45. I am trying to create the following condition:

    If there are any Adjust Hours Listed, then the

    Adjust Pay = SM pay - the calculated EE Med LOA pay - MAT LOA pay. If there are no Adjust Hrs listed, than the Adjust Pay cell will be blank.

    SM Pay Adj Hrs EE Med LOA Pay MAT LOA Pay Adjust Pay
    $4,1791.17 46.67 0.00 1,928.85 2,250.32

    Is there a formula for this?

    Thank you.

  46. Hi, here is the thing, i have a list of items and i build them, but i need to know how many of those items are part of the requisition and how many are not, lets say i have to build 250 cars at $10 each i aready have built 240, then i build 10.... there im ok but if instead i build 25 i have to know in one cell how many of those are within my requsition (10) and how many are out of budget (15)

    thxs.

  47. Currently we have a formula of =IF(P584-P586<100;"RIGHT";"WRONG"). The idea of this formula is to find the difference between the field that has a sum in and the other field which is the posting in our accounting system. But the difference is okay as long as it is no more than 100.

    This formula covers one angel of the working but I would also need to do IF(P586-P584<100;"RIGHT";"WRONG") as well to ensure the differences are located from both angels.

    How would I be able to combine those two formulas for it to work?

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

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

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

      1. Sorry, I do not fully understand the task.
        As it's currently written, it's hard to tell exactly what you're asking.

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

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