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

CountIf Formula in Excel What does it do?Counts the number of cells that matches your specified conditionFormula breakdown:=COUNTIF(range, criteria)What it means:=COUNTIF(range of cells to check, condition to check against)Do you have a scenario where you want to count the num...
Change Phone Area Codes with Excel’s REPLACE Formu... What does it do?Replaces part of a text string, based on the number of characters you specify, with a different text stringFormula breakdown:=REPLACE(old_text, start_num, num_chars, new_text)What it means:=REPLACE(this cell, starting from this number, all the ...
VLOOKUP with Multiple Criteria in Excel ‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office.Advanced users have seen the use of adding more than one...
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...