Between Formula in Excel [Quick Tips]

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

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

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,

Between Formulas in Excel – Summary and 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?

Recommended Excel Formula Tutorials:

Do you want to be awesome in Excel?

Source: m.chandoo.org

Category: Forex

Similar articles: