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:

oztraining_728x90

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

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...
Extracting Data with Excel’s LEFT Formula What does it do?It returns the first character or characters in a text string, based on the number of characters you specify.Formula breakdown:=LEFT(text, )What it means:=LEFT(look in this cell, extract X characters)There are times when you will need t...
Get the Average of Values with Excel’s AVERA... What does it do?Gives you the average of a group of valuesFormula breakdown:=AVERAGE(number1, number2...)What it means:=AVERAGE(the numbers you want to average) There are times when you have to get the average of your values in your Excel work...
Add Leading Zeros in Excel Do you have a lot of numbers with an uneven number of digits in your Excel list?Do you want to make them uniform by adding leading zeros to them?Well, it's a pain to add zeros in front of them one by one!Thankfully, Excel allows you to add leading zeros with one singl...