Between Formula in Excel [Quick Tips]
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,
wait for it…
that is right, <= and >= operators, like this:
Between Formulas in Excel – Summary and Examples:
Here is a list of examples and the corresponding Excel Formulas to test the between condition.
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?
Share using comments.
Recommended Excel Formula Tutorials:
- Check for Either Or conditions in Excel
- Find out if 2 ranges of dates overlap using formulas
- Get my Excel Formulas e-Book, learn 75 most used formulas overnight
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Use Excel For Rapid UI Prototypes [Awesome uses of Excel]||Office 2010 Contest Winners are here!!! »|