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