Between Formula in Excel [Quick Tips]

Posted on June 24th, 2010 in Excel Howtos - 135 comments

In today’s quick tip, lets find how to check for between conditions in Excel using formulas, like this:

Between Formula - Testing for between condition in Excel

Between Formula in Excel for Numbers:

Lets say you have 3 values in A1, A2 and A3. And you want to find out if A1 falls between A2 and A3.

Now, the simplest formula for such a thing would be test whether the conditions A1>=A2, A1<=A3 are both true. Hence, it would look like,
=if(AND(A1>=A2,A1<=A3),"Yes", "No")

However, there are 2 problems with a formula like above:

1. It assumes that A2 is smaller than A3.
2. It is just too big.

Shouldn’t there be a shorter and simpler formula?!?

Well, there is. Last week when chatting with Daniel Ferry, he mentioned a darned clever use of MEDIAN formula to test this. It goes like,

=if(A1=MEDIAN(A1:A3),"Yes","No")

Now, not only does the above formula look elegant and simple, it also works whether A2 is smaller or larger than A3.

Between Formula in Excel for Dates:

Well, dates are just numbers in Excel. So you can safely use the technique above to test if a given date in A1 falls between the two dates in A2 and A3, like this:
=if(A1=MEDIAN(A1:A3),"Yes","No")

Between Formula for Text Values:

Lets say you want to find-out if the text in A1 is between text in A2 and A3 when arranged alphabetically, a la in dictionary. You can do so in Excel using,

wait for it…

that is right, <= and >= operators, like this:
=if(AND(A1>=A2,A1<=A3),"Yes", "No")

Between Formulas in Excel – Summary and Examples:

Here is a list of examples and the corresponding Excel Formulas to test the between condition.

Between Formula in Excel - Examples

Do you check for Between Conditions in Excel?

Checking if a value falls between 2 other values is fairly common when you are working with data. I would love to know how you test for such conditions in excel? What kind of formulas do you use?

Share using comments.

Recommended Excel Formula Tutorials:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

135 Responses to “Between Formula in Excel [Quick Tips]”

  1. JP says:

    Clever use of MEDIAN, but it returns “Yes” if you use the upper or lower number. Whether you want to consider 20 as being “between” 10 and 20 is up to you.

    Also, the examples made it harder to understand. In the first formula you use A1:A3 for the range, but the first picture looks like the formula is filled across rows, not columns.

  2. Daniel Ferry says:

    @JP –
    MEDIAN can be used regardless of your definition of “between.” To include the boundary points, I would write it like this:
    .
    =A1=MEDIAN(A1:A3)
    .
    To exclude them:
    .
    =A1=MEDIAN(A1,A2+1,A3-1)
    .
    Regards,
    Daniel Ferry
    excelhero.com

  3. Rob says:

    Daniel –

    your formula to exclude the boundary points would only work if you’re dealing strictly with integers. For example, if you test if 19.5 is between 10 and 20 using A1=MEDIAN(A1,A2+1,A3+1), it would fail.

    Rob

  4. Rob says:

    I should clarify…I think it’s a very creative use of MEDIAN and if you’re testing numbers and want to include the end points, it’s a simpler method, but you need to use the other style using just instead of = to properly not include end points.
    Rob

  5. Rob says:

    darn…should have known my greater than and less than characters would be removed.

    Meant to say you need to use the and() style test using just less than and greater than characters without the equal signs.
    Rob

  6. cALi says:

    I tried it using spanish MEDIANA(…) function, but it didn’t work. This is what I did, not such stylish, but it works fine: =IF(AND(A1=MIN(B1:C1)),”YES”,”NO”).

    cALi

  7. Daniel Ferry says:

    @Rob –

    You bring up a good point that I should have clarified. When using the method I shared above to exclude the boundary points, the user is responsible for the precision. I have used this technique for years with operations scheduling and task management, often with a precision of days. However, I have used it with finer precision, hours, minutes, seconds. Again this is totally up to the user; he can use whatever value he wants instead of the integers of one:
    .
    =A1=MEDIAN(A1,A2+1/24,A3-1/24)
    .
    =A1=MEDIAN(A1,A2+1/24/60,A3-1/24/60)
    .
    =A1=MEDIAN(A1,A2+1/24/60/60,A3-1/24/60/60)
    .
    …of course those constants could/should be replaced by defined names.
    .
    Taking this to the extreme, one could easily define a constant that equals the smallest positive value that Excel can represent:
    .
    spv: =2.229E-308
    .
    We can then write the formula as:
    .
    =A1=MEDIAN(A1,A2+spv,A3-spv)
    .
    …which will work for any possible decimal value between the boundary points. It’s a robust and elegant solution, imo.

    Regards,
    Daniel Ferry
    excelhero.com

  8. cALi says:

    Sorry, when copying and pasting, I should made some mistake, this is real one:
    =IF(AND(A1=MIN(B1:C1)),”YES”,”NO”)

    A1: tested in Between Value
    B1 and C1: limits

  9. cALi says:

    I suppose HTML is in conflict with the code. Same code, different order of the arguments in the AND function:
    =IF(AND(A1>=MIN(B1:C1),A1<=MAX(B1:C1)),"YES","NO")

  10. David says:

    The formula =IF(A1=MEDIAN(B1:C1),”Yes”,”No”) does not work when I tested it. It returns “No” for any value in A1, regardless if it falls between B1 and C1 or not.

  11. Daniel Ferry says:

    @cALi -
    I have no experience with the Spanish version of Excel, but I would be very surprised to learn that the worksheet functions differed in their outputs! Can you provide the exact formula (in Spanish) that did not work for you?
    .
    On a different note, here is an equivalent formula to yours, that does not use the AND() function, nor the IF() function:
    .
    =A1=MIN(MAX(A1:B1),MAX(B1:C1))
    .
    BTW, your formula (and hence my variation of it) has the characteristic where “between” includes the lower boundary point, but not the higher one. This can be altered in a similar fashion as my above example.
    .
    Regards,
    Daniel Ferry
    excelhero.com

  12. JP says:

    @Daniel –
    We can also do both.

    For example, I created a data validation in cell H2 consisting of “True,False” values. (That is, True and False without quotes).

    This formula would then allow you to toggle the output as exclusive or inclusive of the start and end numbers by changing the value in H2 (True means exclude, False means include):

    =B3=MEDIAN(B3,C3+N(H2),D3-N(H2))

  13. Daniel Ferry says:

    @JP -
    That’s it!
    Imagine the nested IF monster that you just avoided! Good job.
    That is why I am always going on about better solutions to haplessly using IF(), when one understands the problem.
    .
    Regards,
    Daniel Ferry
    excelhero.com

  14. cALi says:

    @Daniele

    Thanks for your time, I made some mistake since testing both alternatives I received the same results:
    =An=MIN(MAX(An:Bn),MAX(Bn:Cn))
    =An=MEDIAN(An:Cn)

    Indeed, your solution is not just elegant but also practical, I could name it “minimalist”.

    Best regards,
    cALi

  15. Chandoo says:

    @David.. you have to include A1 as well to get it right. Like this,

    =IF(A1=MEDIAN(A1,B1,C1),”Yes”,”No”)

  16. sam says:

    @ JP Instead of True/False use 1,0, we can then drop the N()

    =B3=MEDIAN(B3,C3+H2,D3-H2)

  17. Tim Buckingham says:

    Turned into UDF for kicks

    Function ISBETWEEN(Rng, num1, num2) As Boolean
    ‘ Checks if value between num1 and num2
    Dim Low As Double, Hi As Double

    ISBETWEEN = False

    Low = Application.Min(num1, num2)
    Hi = Application.Max(num1, num2)

    If Rng Is Nothing Then Exit Function
    If Rng = Application.WorksheetFunction.Median(Rng, Low, Hi) Then ISBETWEEN = True

    End Function

    I like how easy it is to read when wanting to count the values that fall between using

    =COUNTIF(Rng,ISBETWEEN(Rng,Num1,Num2))

  18. Gerald Higgins says:

    I think the use of the MEDIAN function is very clever.

    Nitpicking now.
    If I understand correctly, Daniel’s suggestion for an amendment to exclude the =boundaries case as in
    =A1=MEDIAN(A1,A2+1/24,A3-1/24)
    assumes that all the numbers involved are positive.
    If one or both of the boundary numbers are negative, I think this formula will produce wrong results for values of A1 just outside the true boundary range.

    Also, this formula
    =A1=MIN(MAX(A1:B1),MAX(B1:C1))
    works as long as the C1 value is higher than the B1 value, but not the other way round, which was described asa fault in the OP.
    This formula solves that particular problem (it’s essentially the same as cALi’s)
    =AND(A1MIN(B1:C1))
    Replace < with <= as required.

  19. Gerald Higgins says:

    Sorry, lost symbols in my last post.
    I’ll try again.
    The last formula should be
    =AND(A1 (less than symbol) MAX(B1:C1),A1 (greater than symbol) MIN(B1:C1))

  20. elad says:

    CooL :)))) very elegant solution !

  21. Guest says:

    Great use of the function – I will be using this.
    As always though, formulaic results are only as good as the data on which they’re based (it’s spelled “coyote” instead of “cayote,” so your last text example should actually read yes. :-)
    Not to nitpick….but to nitpick… :-)

  22. JP says:

    @sam –

    “Instead of True/False use 1,0, we can then drop the N()”

    That’s true, but who’s going to understand that? If your users can, they’re much smarter than mine.

  23. [...] Between Formula in Excel, Chandoo presents some formulas for determining if a given value is in between two known [...]

  24. Bernd P says:

    Daniel’s spv approach does not work because the spv addon never makes it into the mantissa of the floating point numbers.

    Regards,
    Bernd

  25. Daniel Ferry says:

    @Bernd -

    With all due respect, you should double check that.

  26. Bernd P says:

    Daniel,
    Excel 2010 (version 14.0.4760.1000 32 bit), spv set to 2.229E-308, A1 = 1, A2 = 1, A3 = 2, result A1=MEDIAN(A1,A2+spv,A3-spv) = True (should be False).
    Again, if I am not mistaken, the very small value does not make it into the mantissa of the MEDIAN parameters which will then lead to MEDIAN(1,1,2) = True.
    Regards,
    Bernd

  27. Bernd P says:

    Daniel,
    I do like the spv idea. My suggestion to fix the mantissa issue would be something like this:
    =A1=MEDIAN(A1,A2+POWER(10,INT(LOG10(A2))-14),A3-POWER(10,INT(LOG10(A3))-14))
    But this is sort of a monster formula again. Maybe two functions InfInc and InfDec (for infinitesimal increase / decrease) should be introduced which return the smallest float greater than the input (resp. the greatest float which is smaller).
    Regards,
    Bernd

  28. Daniel Ferry says:

    @Bernd –
    .
    Touche.
    .
    While the formula logic is correct, Excel does not handle the very, very small number correctly in this instance. Good bug catch.
    .
    As I mentioned above, I have used the MEDIAN method countless times, but usually with dates, but also to the precision of hours, minutes, and seconds. I’ve never actually tried to use it with such fine precision before. I should have tested it before commenting, as Murphy’s Law always prevails.
    .
    After testing I discovered that 1E-14 is the finest precision where my idea does work. To be sure, this will work in virtually every situation, as this is a very small number:
    .
    0.00000000000001
    .
    In fact, this is exactly what your POWER/LOG formula results in. So there is no need to use the monster formula.
    .
    Instead of defining spv as the smallest possible value (in Excel) we can simply enter it’s definition as:
    .
    =1e-14
    .
    and now spv can stand for the smallest possible value (handled correctly).

  29. Bernd P says:

    Daniel,
    I am sorry, but – no, you cannot take an absolute 1e-14. Please note that my POWER/LOG formula flexibly adjust itself to the number in question:
    For 1 it’s 1e-14, for 10 it’s 1e-13, for 100 it’s 1e-12, …
    It will exactly impact on the lowest digit of the mantissa. Please note that it can be different for the two MEDIAN border parameters. Please see my example at
    http://www.sulprobil.com/html/test_if_between_2_values.html
    Regards,
    Bernd

  30. Rick Rothstein (MVP - Excel) says:

    There is always this purely mathematical method for determining if a value (A1) is between two limits (A2 and A3) excluding the end points…

    =If(ABS(A1-((A2+A3)/2)).LT.ABS(A3-A2)/2,”Yes”,”No”)

    To make it include the end points, change the less than to less than or equal….

    =If(ABS(A1-((A2+A3)/2)).LE.ABS(A3-A2)/2,”Yes”,”No”)

    Note that I used (with the surrounding dots) .LT. for the “less than” symbol and .LE. for the “less than or equal” symbol. Now, the only thing I am unsure of is how to adjust this for the spv that was brought up in the latter comments… anyone want to take a stab at it?

  31. Bernd P says:

    Rick,
    Your ABS approach makes perfect sense for small numbers (ASCII code) or floats that are in the same ball park.
    But test the values 0, 1, 2, 3, …, 9 on the border values 1e16 and 5 with .LT. and with .LE.
    The ABS approach gets it horribly wrong here because the lower border value 5 gets off the mantissa when added or subtracted to or from 1e16.
    Regards,
    Bernd

  32. Rick Rothstein (MVP - Excel) says:

    @Bernd,

    While it is possible, of course, I would not normally expect a test for inclusion within a range to have such wildly divergent end points for the range.

  33. Bernd P says:

    Rick,
    Why risk anything if you can only lose? If I deal with floating point numbers of unknown size and if I need to know whether a number is between two others I would use neither the MEDIAN approach nor the ABS approach.
    I think it’s far more important to know the basics about floating point numbers than to know this MEDIAN “trick” or the ABS comparison:
    http://docs.sun.com/source/806-3568/ncg_goldberg.html
    Regards,
    Bernd

  34. Debbie says:

    Thanks for posting…! Worked perfectly for what I needed!!!

  35. sanjeev khendi says:

    i want to if function/ if total sales 200000 ,then com rate =5% give me information how to solve it with example

  36. Hui... says:

    @Sanjev
    Assuming you are entering this in the cell representing Com Rate
    =if(sum(sales range)>=200000,5%,10%)
    10% is the value for Com Rate if sales

  37. cALi says:

    @Sanjev
    Using Daniel Ferry approach about IF function, which I have embraced as mine:

    C D E
    Lower Limit Upper Limit Commission Rate
    ?

  38. cALi says:

    @Sanjev
    Using Daniel Ferry approach about IF function, which I have embraced as mine:

    C D E
    Lower Limit Upper Limit Commission Rate
    4 Range1 – 100,000.00 0%
    5 Range2 100,000.00 200,000.00 2%
    6 Range3 200,000.00 1,000,000.00 5%
    7 Range4 1,000,000.00 1E+100 6%
    8
    9 Actual Sales 180,000.00
    Comm. Rate 2% =SUMPRODUCT((C4:C7

  39. cALi says:

    @ Chandoo, really sorry for the mess, the text editor is definitely not my friend… this will be my last chance, I hope it works…

    @ Sanjeev,
    Using Daniel Ferry IF function approach, and using some dummy data:

    A B C D
    Lower Limit Upper Limit Commission Rate
    3 Range1 – 100,000.00 0%
    4 Range2 100,000.00 200,000.00 2%
    5 Range3 200,000.00 1,000,000.00 5%
    6 Range4 1,000,000.00 1E+100 6%
    7 Actual Sales 180,000.00
    8 Comm. Rate 2% =SUMPRODUCT((B3:B6?$B$7)*($B$7?C3:C6)*D3:D6)

    Please replace ‘?’ with ‘less than or equal to’ and ‘?’ with ‘less than’ proper operators.
    By the way, C6 is a dummy value, is ‘upper infinite’ to make this approach work.

    Regards,
    cALi

  40. Amit Zaveri says:

    @ cALi – thanks

    I modified and used your example to check date within given range. Below is the link to download the example.

    https://docs.google.com/uc?id=0B-QZQs3hmWJJOWZiNzk4ODAtYjA2My00MjYyLTkzMWEtOGMwMGJhNDA1NGU4&export=download&hl=en

  41. KM says:

    Sales Achievement
    15,001 – 20,000 EL
    20,001 – 50,000 E
    50,001 – 100,000 D
    100,001 – 160,000 C
    160,001 – 240,000 B
    240,001 & above A

    If the sales achievement fall in between 50,001-100,000 is under Class D, can you help if i have many column of acheivement data which fall under different class. How can i set the formula in one time?

  42. Hui... says:

    @KM
    Are your Ranges in 1 Column or 3
    ie: is 15,001 – 20,000 in 1 cell or 3 cells

  43. mani says:

    I want drop my serial no continuous automatically from the input value
    e.g I have list of TV I given code for that
    TV001
    TV002
    TV003
    THEN I START ADDING BIKE
    BIKE001
    BIKE002
    AGAIN I WANT ADD TV FROM PREVIOUS NUMBER CONTINUATION
    LIKE
    TV004
    TV005
    again i want add bike003 cotinuation from last number
    IS THERE ANY FORMULAS FOR THAT?
    PLEASE SEND THIS TO MY EMAIL ADDRESS sent to mani.n@govasool.com

  44. Antony says:

    i have a problem: i have 2 rows, A1 and A2 are containing ID which are same. B1 has to be compared with B2 and B3, and if B1 falls between them then it should tell “YES” else “NO”. How will I do this????

  45. Hui... says:

    @Antony
    Assuming B2 < = B3 then use:
    =IF(AND(B1>=B2,B1<=B3),”Yes”,”No”)

  46. Murugaraj says:

    @ Hui,
    I have precisely the same situation as @KM has (in comment 40). I have the values in three colums (“range begin”, “range end”, “category”). I need to find out in which range a given value lies and fetch the corresponding category. Help pls.

  47. Sid says:

    How do I use to it see if a time value is between 2 values
    Example if 09:18:24 is between 09:18:00 and 09:19:00

  48. Daniel Ferry says:

    Sid,
    .
    Exactly the same way!
    .
    Assume your times are in these cells:
    .
    A1 = 09:18:24
    A2 = 09:18:00
    A3 = 09:19:00
    .
    The formula from any other cell will determine if 09:18:24 is between the other two values:
    .
    =A1=MEDIAN(A1:A3)

    • Michael says:

      How can I make it tell me if the current time and date is between two other times and dates.

      I am working with the following:

      Lets say that the time right now is Thursday at 4 PM. How would this work out?
      Thursday: 11:00 AM – 2:00 AM (Friday)

      Then imagine that the current time is  Friday at 1 AM.

      Thanks!!!! 

      • Hui... says:

        @Michael
        =MEDIAN(DATE(2012,12,10)+TIME(11,0,0), DATE(2012,12,11)+TIME(16,0,0), DATE(2012,12,12)+TIME(18,0,0)) = DATE(2012,12,11)+TIME(16,0,0)
        Adjust Dates/Times to suit

        or

        =MEDIAN(A1, A2, A3)=A1
        where A1 is the Date/Time now
        A2 & A3 are the other dates/times

        • Michael says:

          Thanks for your help.

          I’m a little unsure how to interpret your answer….

          Also, where would I insert the following?

          “DATE(2012,12,day(today()))+TIME(Hour(now()),minute(now()),second(now()))”

          I think I would substitute this in at the end of your answer’s equation for the “= DATE(2012,12,11)+TIME(16,0,0)” part, so that my equation will always work… right? thanks again!!!

          • Hui says:

            The Format is:
             
            =Median(Start Date, End Date, Now)=Now
            it doesn’t matter what order the components go
            so:
            =Median(Now, End Date, Start Date)=Now
            is Just as valid
            If you use the Now() function that already includes the date & time
            So you can use
            =Median(Start Date+Time, End Date+Time, Now())=Now()
            =MEDIAN(DATE(2012,12,13)+TIME(11,0,0),DATE(2012,12,14)+TIME(2,0,0), Now())=Now()
            or if you want to use Today
            eg: 11am today until 2am tomorrow
            =MEDIAN(Int(Now())+Time(11,0,0),Int(Now()+1)+TIME(2,0,0), Now())=Now()

             

  49. msog says:

    Daniel,

    I’m quite confused by the results I’m receiving when trying this formula. I’m using it to try to validate if a date is between two other dates using the “short date” format. I receive “NO” all statements except for the exact middle date (which is what the median actually is, mathematically speaking). Is there something wrong with my formula that prevents me from getting any date between the two values?

    Formula: =IF(A1=MEDIAN($E$1:$F$1),”YES”, “NO”)

    Thanks in advance

  50. amal says:

    Hi,

    I need help:

    A2 contains name of staff
    C2 contains his weight
    I need to fill D2 with Lean, Fit, Fat or Obese base on which range his weight fits in based on below grid:
    50-60: Lean
    60-70: Fit
    70-80: fat
    80-100: obese

  51. Jesus Rodriguez says:

    Is there a list of the different symbols and what they represent, or what function they have when used in a formula? Example: = (equal to), < (greater than), etc.

    Actually, what I’d like to know, it’s if there is a symbol that represents “between”. Let’s say I want a formula like this: =IF(A1betweenA2andA3,”Yes”,”No”).

    Thank you in advance,
    Jesus R

  52. Hui... says:

    Jesus R

    There are only a few symbols useable in this context

    X > Y, X Greater than Y
    X < Y, X Less than Y
    X = Y, X equal to Y

    They can be combined
    X >= Y, X Greater than or equal to Y
    X < = Y, X Less than or equal to Y

    X <> Y, X not equal to Y

    you can often use other Excel functions to make other logic

    or(X=Y , Z=A), X=Y or Z=A will force this to be true
    and(X=Y , Z=A), X=Y and Z=A both have to be True for this to be True

    The above can be used in numerous ways to create quite complex logic

  53. Shishir says:

    There are a problem
    I requered to the formula for example below
    A1 B1 C1
    A+++ A +++
    A++ A ++
    A+ A +
    A A

    Kindly suggest me the formula for that in write segment.

    Thanks

  54. Hui... says:

    @Shishir
    .
    Not sure but try the following
    B1: =Left(A1,1)
    C1: =Right(A1,Len(a1)-1)
    Select B1 + C1
    Copy down

  55. aa aa says:

    Hello, nice topic.. It’s clear to use between value when there s just one.. how d you determine where the values in a range fall between in another range.. lets say I have a list goes like
    1-5 100
    6-13 200
    14-32 300
    what I want s to expand the list like
    1- 100
    2- 100

    6- 200

    Guess first I need to find where the tax number fall between , then I ll reference to the cell just aside of that range.

    Need help, thenks in edvance

  56. neha says:

    Dear All,
    Plz help in formating the “if formula” in excel of the below condition
    Less than 95% = 0
    95.01% to 97.5% = 0.06
    97.51% to 100% = 0.12
    100.01% to 102.5% = 0.18
    102.51% to 105% = 0.25

  57. Hui... says:

    @Neha
    Try this:
    =IF(A1<=95%, 0, IF(A1<=97.5%, 0.06, IF(A1<=100%, 0.12, IF(A1<=102.5%, 0.18, 0.25))))
    .
    or this odd one
    =CHOOSE( MIN( INT((( A1-95.001%)/2.5%)) + 2,5), 0, 0.06, 0.12, 0.18, 0.25)

  58. jmichuck says:

    How about adding an else statement to this
    =if(AND(A1>=A2,A1<=A3),"Yes", "No")

    So if cell A1 is empty I the result will be a blank cell or an entry of my choosing.

  59. jmichuck says:

    Here is a better way of explaining what I’m looking for
    Can you add an ELSE statement to this: =if(AND(A1>=A2,A1<=A3),"Yes", "No")

    What I need is to be able to return a null value if cell A1 doesn't have any data in it yet

  60. Hui... says:

    @Jmichuck
    =IF(A1<>“”,IF(AND(A1>=A2,A1< =A3),"Yes", "No"),"Null")
    Retype all ” characters

  61. neha says:

    Dear hui,
    I have tried your suggested logic but it didn’t work.Err.502 come while putting it.Plz.help me out

  62. Hui... says:

    @Neha
    Did you try:
    =IF(A1<=0.95, 0, IF(A1<=0.975, 0.06, IF(A1<=1, 0.12, IF(A1<=1.025, 0.18, 0.25))))

  63. neha says:

    hai Hui Thanxxxxxxxxxx.a lot dear……….it works.With this i finally complited my report which need to submit by tomorrow.Thanks once again.

  64. jmichuck says:

    Thank you, but there seems to be an error in the formula.

    By the way, thank you so much for your services. This will impress the boss for sure.

  65. jmichuck says:

    What do you mean by “Retype all ” characters”?

  66. jmichuck says:

    oK you literally mean they have to be re-typed. Strange but it worked. Thank you very much

    • Hui... says:

      Sometimes WordPress converts the ” characters to something that looks like a ” but isn’t
      When you copy/paste to excel, excel doesn’t understand what those ” look-like characters are
      And returns an error

  67. baum schausberger says:

    problem. how to use ABS and IF here: (9-5)/2+9=11 but 11>9 so I need 11-9 = good. how to do this.

  68. Hui... says:

    =if((9-5)/2+9<11,11-((9-5)/2+9),(9-5)/2+9) )

  69. Pradhish says:

    nice use of median. just what was i looking for, but i would appreciate if you could extend the number of rows it checks for inclusion. for eg in the sample data you posted,
    http://chandoo.org/img/f/between-formula-in-excel.png

    i wish to find out if “22″ falls under range B2:C9. (Assuming “Value” is in cell A1). kindly help me with this since the only solution i can think of is using nested functions which makes it a monster formula..

  70. matew says:

    thnks hui. now I got your formula =sqrt(A1^2+A2^)-1-IF(sqrt(A1^2+A2^2)-1>53,53,0) work good, now if it is possible, beside this I really need also in the same formula to use ABS value and ROUND, because I got negative numbers, and so many decimals, so to eliminate I need to use those functions, thank you.

  71. neha says:

    @ Dear Hui
    there is a condition -
    15001 – 20000 = Grade “C”
    20001 – 50000 = Grade “B”
    50001 – 100000 = Grade “A”
    100001 & Abiove = Grade +A
    I have used your earlier formula with some modification i.e.
    =IF(A2<=20000,"c",IF(A2<=50000,"b",IF(A2<=100000,"a","a+")))
    it works but I also want with the change of grade colour of the cell is also changed for eg Grade A comes with green backgroung & grade C comes with Red background & like wise.
    I tried conditional formatting but yet no appropriate result comes.
    Plz help.

  72. Hui... says:

    @Neha
    You will need to add 3 CF Rules and have them in the right order
    Select your Range I am assuming B2:B10
    Enter 3 CF Rules using formulas
    CF1: =$A2<=20000 CF Color X, Stop If true Yes
    CF2: =$A2<=50000 CF Color Y, Stop If true Yes
    CF3: =$A2<=100000 CF Color Z, Stop If true Yes
    Now apply this
    Apply a Default Color which will be applicable if the score is Greater than 100000
    That should be it
    Make sure that the 3 CF's are in the order above, you can shift them up/down once entered

  73. Shishir says:

    Dear all
    I want to
    A B C
    BP03/44/00/12FC BP03/44/00/12 FC
    BP03/44/00/21SF BP03/44/00/21 SF

    Kindly suggest me how i will do by using the formulas.
    Thanks & Regards
    Shishir

  74. Sonal says:

    Dear all
    on dated 10/01/1012i make a excel sheet. If after the day like tomorrow or day after tommorow somebody modify any cell indicate in a seperate colour which cell somebody modity. which formula i use for that. Kindly suggest me.
    Thanks

    Sonal

  75. walt says:

    Vlookup is an excellent formula to find “between” values:

    Table:
    Value Multiplier
    Column A Column B
    0.00001 0.5
    4.826369861 1
    9.652739721 1.5
    14.47910958 2
    19.30547944 2.5
    24.1318493 3
    28.95821916 3.5
    33.78458902 4
    38.61095888 4.5
    43.43732875 5
    48.26369861 5.5
    53.09006847 6

    Lookup value (cell A1) -> 3
    vlookup(A1,$A$1:$B$12,2,TRUE) – will result in 0.5.

    Hope this helps.

  76. Gaurav says:

    Hi, can someone help me how to write this function in Excel.

    There are 100 rows of 3 diferent numbers (so, 100 rows, 3 columns, C1, C2, C3).
    I have to do the following:
    If C1, C2 and C3 are equal to 2, 4, and 5 respectively, then answer should be 1
    If C1 and C2, or C2 and C3, or C3 and C1 are able to match 2&4, or 4&5 or 5&2 respectively, [i.e, if two of the 3 entries match correctly] then answer should be 0.5
    If none of C1, C2, C3 match 2,4,5 respectively, then answer should be 0.

    Thanks

  77. Jessie says:

    I’m a complete noob at Excel Formulas. I’ve been trying to increase my knowledge of excel but I can’t seem to find how to create this formula.

    I have an employee that works from 6:00am to 2:30pm. She takes a 30 minute lunch and has two paid 15 minute breaks. At the end of the day she has 7.5 hours of productivity. The problem is some days she works in as many as 8 different queues. I have to record those times in each queue but at the end of the day her hours should not be more than 7.5. In a perfect world she’d work in one queue for 6-2:30pm and a simple formula would work to get 7.5 hours but that’s not the case. She may work 2 hours in one queue, 1 in another, 3 in another and 2 in another. How do I factor her breaks and her lunch in my formula. She goes to break at 8:30am, lunch at 12:00-12:30 and last break at 1:45.
    Also some other factors, employees working 4-6 hours get on break. 7 hours a lunch and break, 8-12 hours is lunch and two breaks. employees can’t work more than 12 hours in a day. I hope someone can help. I’m lost. Here’s one formula I was using. but sometimes my hours go above 7.5. =IF(SUM(D23-C23),(24*MOD(D23-C23,1.25)-LOOKUP(24*MOD(D23-C23,1.25),{0,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9

    • Jessie says:

      sorry, I left that formula incomplete.

      =IF(SUM(D23-C23),(24*MOD(D23-C23,1.25)-LOOKUP(24*MOD(D23-C23,1.25),{0,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10},{0,0.5,0.5,0.5,0.5,0.5,1,1,1,1,1,1,1,1})),”")

  78. jmichuck says:

    I am using this formula have a formula in cell E1 =IF(A1″”,IF(AND(A1>=C1,A1<=D1),"PASS","FAIL")," ")
    When I ener a value in cell A1, I get a pass/fail or null returned in cell E1.
    I would like to also place a value into B1 but have that take priority over A1.
    So If I was to only have an value in A1 the formula would work as stated above. If I was to place a value in Cell B1 it would then disregard cell A1 and return pass/fail based on input in B1.

    Hope that makes sense. Thank you

  79. Hui... says:

    @Jmichuck
    like:
    =IF(B1<>"","B1 not empty" ,IF(A1 ="", IF(AND (A1>=C1, A1< =D1), "PASS", "FAIL"), ""))

    you will have to retype all the " marks

    • jmichuck says:

      Hui,

      Thank you but not quite correct. I would like the formula to take the value of A1 & B1 and evaluate if they fall between the values of C1 & D1. If so then I would get either a PASS/FAIL result. If there is a Value in B1 then it would disregard the value in A1. If both A1 and B1 are Empty then the cell with the formula would remain empty.

      Thank you so much for looking into this. I really appreciate it.

      -Chuck

  80. Daniel Ferry says:

    jmichuck,

    Here’s one way:


    =CHOOSE(1+(INDEX(A1:B1,(LEN(B1)>0)+1)>=C1)*(INDEX(A1:B1,(LEN(B1)>0)+1)<=D1)+(LEN(A1&B1)=0)*2,"Fail","Pass","")

    …or in the spirit of Chandoo’s article:


    =CHOOSE(1+(INDEX(A1:B1,(LEN(B1)>0)+1)=MEDIAN(INDEX(A1:B1,(LEN(B1)>0)+1),C1,D1))+(LEN(A1&B1)=0)*2,"Fail","Pass","")

    Regards,
    Daniel Ferry
    Excel MVP

    • jmichuck says:

      Daniel,

      Thank you very much. That worked out perfectly

    • jmichuck says:

      OK one thing. If cells A1 thru D1 have no values, I would like to see the cell with the formula to be null/empty. Currently with the formulas above I will get #VALUE or #REF!

      Thanks again

      • jmichuck says:

        Sorry the second formula returns #NUM! not #REF!

      • Daniel Ferry says:

        jmichuck,

        To satisfy this further requirement is easy for the first formula (we just add another null at the end:


        =CHOOSE(1+(INDEX(A1:B1,(LEN(B1)>0)+1)>=C1)*(INDEX(A1:B1,(LEN(B1)>0)+1)<=D1)+(LEN(A1&B1)=0)*2,"Fail","Pass","","")

        You would need to trap the condition with an IF() or IFERROR() wrapper on the second formula, so for your requirements I’d go with this formula directly above, even though I made the MEDIAN() suggestion to Chandoo in the first place!

  81. Murray says:

    Since Excel stores date/times as numbers, using median will only work if the times you are actually in the range.

    This stumped me for a bit.

    If I want to check that “01/12/1972 9:15AM”, is between “9:00AM” and “10:00AM”, the median formula won’t work. You need to check if its between “01/12/1972 9:00AM” and “01/12/1972 10:00AM”

    Does anyone have an idea on how to quickly check if the date:time is between two times (with no date).

  82. Earlene says:

    In excel 2010 I need a formula If a cell is blank > 21 days send an email.

    Many thanks in advance!

  83. Tara says:

    I find this string very interesting and helpful. I am not sure I followed all of it, so if this has already been answered, I apologize.

    Here is what I have:
    Cell A1: =Today()
    Column B: List of Dates by Week ending – Begins with 2/19/12
    Column E: Percent completed

    I need a formula that will look at cell A1, determine which cell would apply in column B, and then populate the percentage from column E.

    Any help would be greatly appreciated.

    • Tara says:

      I realized that I can do this with a simple VLOOKUP. So, based on the information in my previous post:

      =VLOOKUP(A1,B2:E52,4)

      In the past, I had only used VLOOKUP to find exact matches, so I did not realize that for the range_lookup I could either use TRUE, or omit the criteria, and find the closest match.

      Sometimes the simplest things elude us, so I just thought I would share this for anyone else who might be searching.

  84. shishir says:

    HI,
    A B C D
    1 ABC Y

    2 ACB Y Y ACB

    3 CAB Y

    4 CBA Y Y CBA

    5 BCA Y Y BCA

    I get THE VALUE OF COLUMN D BY USING IF, AND & INDEX FRMULA.THE PROBLEM IS I WANTED TO CONTINUE THE VALUE OF D LIKE WITHOUT ANY BLANK IN ROW (D1=ACB; D2=CBA; D3=BCA). KINDLY SUGGEST WHICH FUNCTION I USE.

  85. Krystian says:

    Hi

    I wonder if someone could help me with this. How can I check if time beetween a and b fall in between the time c and d?

    Thank you in advance

    Krystian

  86. Krystian says:

    Following previous question: to be more specific, I need to check whether the time between 09:17:00 and 09:58:00 falls between the time 09:35:00 and 09:45:00?  
    Any help would be really appreciated
     

  87. where here, and how, is possible to upload a vba code, I need to add some condition more to the code but I don’t know how to do it.

  88. Syl says:

    I have two excel documents I’m trying to use a look up formula to see compare the peoples names on both of the documents but I can’t seem to have any luck. I have used vlookups before and never had to dealed with text. any help will be appreciate it! and just I’m new on excel.

  89. Ardis says:

    I’d like to use conditional formatting to highlight the rows where today’s date falls between various dates in a column but all those different dates need to be the range of plus 6 weeks.
    Am I on the right tack with this?
    CF1:=(today’s date=MEDIAN(DATEfirst cell/+DAY(42)CF color yello, stop IF true yes
     

  90. sixseven says:

    THis trick just saved me a ton of time.  I used absolute references for two of the numbers instead of a range.

  91. Rick January says:

    I want to use a conditional formula as follows.  If the result of a calculation produces a number less than -0.3 return a value.  If the number is between -0.3 and +0.3 return a second value.  If the number is greater than +0.3 return a third value.  I tried this formula
    =IF(E25<-0.3,”corrosive”,IF(E25>0.3,”scaling”,”balanced”)) and variations

    However, it returns “corrosive” even if the calculated number is -0.3 and “scaling” when the number is +0.3. 

  92. Boppa says:

    Hi All,

    I am trying to find formula for the below scenario.

    I have a object id and start date in spread sheet 1 and object id, 3 start date and 3 end dates for the same object in another spread sheet. I want to findout for which record in spread sheet 2 the start date of the spread sheet fall inbetween.

    Sheet 1

    53205649
    8/3/2012

    Sheet 2

    53205649
    7/1/2012
    12/31/9999

    53205649
    7/1/2011
    6/30/2012

    53205649
    7/31/2010
    6/30/2011

    Any help is much appreciated.

    Boppa

  93. Nitesh says:

    Guys, I work in middle east & they have two calendars here.. one islamic calendar (lunar one) & second is Gregorian. I need to know if any specific date falls between two dates, then it automatically converts in islamic month/ date. I have already made a calendar which have first & last dates (gregorian dates) of any islamic month. Can anybody help me??

    Thanks a lot in advance.

  94. Alam says:

    hi. it worked just like charm..
    but applying it seems it works only with three columns….for example
    column 1 (data)
    column 2 data
    column 3 (data)
    column5  (value)

    If(column5=median(column1,2,3),”yes”, ‘no’)

    than this formula doesnt work…
    do u think u can find anyothe way

  95. Juls says:

    Hi there,

    I have tried your formula for a project plan. Basically, if the date at the top of the row is equal to or inbetween a start or end date (located in the first two columns), I want it to write “YES” into the calendar – so I know a task is running on that date. Kind of like a Gantt chart.

    However, this formula does not recognise that the 2nd of October is between the 1st and the 5th, and no formula I tried so far recognised the 1st is between the 1st and the 5th.

    Any ideas?

  96. [...] Click here for more Excel home works, quizzes & challenges. Clue: Click here for a clue. Got [...]

  97. Tan says:

    The conditional formatting does not work along with the AND function in Office 2007. Is this only for 2010?

  98. GatesIsAntichrist says:

    Bravo! I simply could not make it to the end of all the posts above so forgive me if already covered:

    Below I will show that
    - the contiguous sequence A1,A2,A3 is not required, neither row-wise or column-wise.
    - Furthermore, A1 can be a formula, not just a cell.
    - So can A2 and A3!

    So you can hardcode 95% and 105% for A2 and A3; or Make A2 98% of something, etc.

    Assuming notation X for one general cell address (or formula!) and Y and Z for the pair to go between where Y <= X <= Z,
    =if(X=median(X,Y,Z),"yes","no") or minimally
    X=median(X,Y,Z)

    Example: =if(C3-C2=median(C3-C2,$E$1,$F$1),"yes","no")
    where $E$1 is 95 and $F$1 is 105

    This capitalizes on the characteristic of ranges that you can build ranges noncontiguously with commas. You aren't legally bound to that colon, you know, ha ha.

    1. Correct me if I was imprecise about endpoint inclusion.
    2. I saw some concern above about negative numbers. I have not tried to test that, much less an exhaustive bulletproofing.
    3. floating point "epsilon" issues may still apply.
    4. Tested only with XL2003 (because you're crazy to use any later disastrous version, unless forced to do so)

  99. Mark R says:

    Could somebody help me with the below formula?
    I basically want it to sum column D but based on the criteria of column C……..i’m trying to ask the formula to look between codes 50000 and 60000……but this formula doesn’t work for me – what can I use instead of median to look between these codes?

    =SUMIFS(D8:D15,C8:C15,MEDIAN(50000:60000))

    Many thanks in advance!

  100. Clinton says:

    Just wanted to say thanks so much for the tip on using Median. Saved me a lot of extra typing. Was working on a list of unit counts referencing a tiered pricing schedule. I utilized the following and it worked like a charm even though the counts and unit price schedule were in two different worksheets. Cheers!
    =IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$3:$C$3),Sheet3!$D$3,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$4:$C$4),Sheet3!$D$4,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$5:$C$5),Sheet3!$D$5,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$6:$C$6),Sheet3!$D$6,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$7:$C$7),Sheet3!$D$7,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$8:$C$8),Sheet3!$D$8,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$9:$C$9),Sheet3!$D$9,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$10:$C$10),Sheet3!$D$10,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$11:$C$11),Sheet3!$D$11,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$12:$C$12),Sheet3!$D$12,IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$13:$C$13),Sheet3!$D$13,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$14:$C$14),Sheet3!$D$14,(IF((B2+C2/5)=MEDIAN((B2+C2/5),Sheet3!$B$15:$C$15),Sheet3!$D$15,0))))))))))))))))))))))))

  101. Sumeet says:

    Just a note of thanks. Due to this thread was able to solve a issue very quickly

  102. JAYANT says:

    =IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,IF(A2=15, “OK”, “Not OK”,)))))))))))

  103. kirill says:

    =(A1-A2)*(A1-A3)<=0
    or
    =(A1-A2)*(A1-A3)<0

  104. Magda says:

    Hi,

    I need help.

    I have 2 tables;

    1) Dates Column A and prices column B
    2) Date ranges column M and prices column Q

    I need a formula which will do the following
    - Identify if date in column A falls within the rangefrom column M
    - If the answer is positive then I need a price from column Q to deduct a price from column B

    Any ideas?

    Thanks

  105. ericb says:

    the median stuff saved my bacon. you rock!

  106. lucyolsen says:

    =(A3=MEDIAN(A1:A5))*(COUNTIF(A1:A5,A3)=1)

    • lucyolsen says:

      Sorry, I mean
      =A1=MEDIAN(A1:A3)*(COUNTIF(A1:A3,A1)=1)

      Also, if you put () around the first statement, it returns a 0 or 1 rather than true/false.

      • lucyolsen says:

        …that doesn’t work for testing the number 0.

        but these should work for an entire range of numbers, not just 2:
        =MIN(A3:A16)=A1)
        =MIN(A3:A16)A1)

Leave a Reply