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:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: For numbers, we have a creative use of the Median formula:

between-01

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

between-02

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

between-03

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

between

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Cleaning Data with Excel’s CLEAN Formula   What does it do? Removes all nonprintable characters from text Formula breakdown: =CLEAN(text) What it means: =CLEAN(this dirty text cell) There are times when imported text from other applications contain characters that are unprintable. The CLEAN...
10 + 1 Ways to Round Numbers in Excel This is a guest post by Alan Murray from Computergaga. There are many ways to round numbers in Excel. These techniques will typically involve formatting the values, or using formulas. This post will look at 10 rounding functions of Excel, and then look at some formatting te...
Count Formula in Excel What does it do? Counts the number of cells that contain numbers Formula breakdown: =COUNT(value1, ...) What it means: =COUNT(range of cells to check, ...) Ever had a column of data and wanted to check if all of the values contain valid numbers? It would ...
Round Off Numbers with Excel’s ROUND Formula What does it do? Rounds a number to the nearest decimal based on your specified number of digits Formula breakdown: =ROUND(number, num_digits) What it means: =ROUND(the number, number of decimal places to round off) Ever had the need to round off numbers? ...