There is no explicit Between formula in Excel, however we can come up with creative ways to create this functionality. Our goal is to evaluate if a given value is between a range, for example, is 6 between 1 and 10?
We have three possible scenarios: numbers, dates, and text.
I explain how you can do this and please go to the bottom of the page to see the animated gif tutorial:
STEP 1: For numbers, we have a creative use of the Median formula:
=IF(C7=MEDIAN(A7:C7), “Yes”, “No”)
In our first example above, the range is 20-60, upon checking the value 50, it is in between this range.
The median formula will return the value in the middle of these 3 values when arranged in increasing order: 20, 50, 60. The median value is 50. Since it matches the value we are evaluating, then the answer we get is a Yes, this value (50) is in between the range.
STEP 2: For dates, we have the same application of the median formula. Because Excel treats dates as numbers too:
=IF(C10=MEDIAN(A10:C10), “Yes”, “No”)
In our first example above, the range is May 1 – July 1, upon checking the date June 1, it is in between this range.
The median formula will return the value in the middle of these 3 dates when arranged in increasing order: May 1, June 1, July 1. The median value is June 1. Since it matches the value we are evaluating, then the answer we get is a Yes, this value (June 1) is in between the range.
STEP 3: For text, we are checking if the value is alphabetically in the middle. We will be using the and formula:
=IF(AND(C12>=A12, C12<=B12, “Yes”, “No”)
Interestingly enough, you can compare texts using the >= and <= operators. Excel is able to compare them which goes alphabetically first or last.
In our first example above, the range is Cat – Dog, upon checking the text Cow, it is in between this range. As when arranged alphabetically, it would be: Cat, Cow, Dog.
The And formula checks if Cow >= Cat, and Cow <= Dog. You will see that both of these are true, as Cow is alphabetically later than Cat, while Cow is alphabetically ahead of Dog. Which is why we get a Yes result.