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.
11 Responses to “Use Alt+Enter to get multiple lines in a cell [spreadcheats]”
@Chandoo:
One more useful trick.......
In a column you have no. of data in rows and need to copy in the next row from the previous row, no need to go for the previous rows but entering Alt + down arrow, you will get the list of data, (in asending order), entered in the previous rows...
This is another great tip. I use this all the time to make sense of some *very* long formulas. As soon as the formula is debugged I remove the break.
Great tip Chandoo!
I use this feature often and it has even gotten the, "how did you do that" response.
Thanks!
@Ketan: Alt+down arrow is an awesome tip. I never knew it and now I am using it everyday.
@Jorge, Tony: Agree... 🙂
[...] Day 1: Insert Line Breaks in a Cell [...]
how can we merge a two sheet.
excellent idea. Chandoo you are genious
Hi chandoo,
I have used ctrl+enter to break the cell. But I did not get the result.
Please tell me how can i break the cell in multiple lines.
Hi, Ranveer,
Its not Ctrl+enter to break the cell, use Alt+Enter to make it happen.
hi Chandoo....
how we can use Alt+Enter in multiple rows at the same time please reply hurry i have lot of work and have no time and i m stuck in this. 🙁
Alt+J worked once 🙁
So I found another more reliable way:
=SUBSTITUTE(A2,CHAR(13),"")
Where A2 is the cell that contains the line breaks which the code for it is CHAR(13). It will replace it with whatever inside the ""