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,
…
wait for it…
…
that is right, <= and >= operators, like this:
=if(AND(A1>=A2,A1<=A3),"Yes", "No")
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.
7 Responses to “Build models & dashboards faster with Watch Window”
yes, I use watches in excel vb
Dear Chandoo,
This is a quite useful for myself. I highly appreciate your efforts for acquainting us with this value adding and time saving tip.
I'll admit it, I never saw this feature before. I was familiar with using Watch Window in VB, but not in workbook. I'll definitely use this in future when building dashboards!
it was always great reading your blog, one thing that i want to know, which tool you are using to create such gif image?
it was also a nice post,,,,,, sir
Hello Sir,
Really very nice post. I didn't use this features in past. But now going to use it..
Thanks a lot for every time come up with something new..
Thanks
Very Nice post sir.
Every Time posting something new.. Thanks a lot