I need a clever BETWEEN worksheet formula. Assume I have values in A1:A3 and I need to determine if A2 is in between A1 and A3.
A1=1
A2=2
A3=3
Now further assume that there is no defined sort order for this range. I could also have
A1=3
A2=2
A3=1
and it should return TRUE. Currently I’m using
=OR(AND(A2>A1,A2<A3 ),AND(A2<A1,A2>A3))
It works fine, but it’s so darn long. Slightly smaller:
=AND(A2>MIN(A1,A3),A2<MAX(A1,A3))
I feel like I missing something easy here.
P.S. If you leave a comment with a formula use ampersand-gee-tee-semicolon for greater than and ampersand-ell-tee-semicolon for less than.
How about:
=COVAR(A1:A2,A2:A3)>0
What about:
=RANK(A2,A1:A3)=2
Simple:
=AND(A2>A1,A3>A2)
(I’m assuming you really want the values to be “between” not “between or equal to”.)
The formula above by jkpieterse
=RANK(A2,E8:E10)=2
returns TRUE for 1,1,2
but FALSE for 1,2,2
M
Hahaha
And my formula is crap!
I wish you could delete your own posts :o(
=A2=SMALL(A1:A3,2)
or
=A2=LARGE(A1:A3,2)
…mrt
=(A1>A2)=(A1
Note if this is intended to emulate the SQL’s BETWEEN, it doesn’t e.g. this in T-SQL (MS SQL Server):
DECLARE @min INTEGER,
@max INTEGER,
@test INTEGER
SET @min = 1
SET @max = 3
SET @test = 1
SELECT CASE
WHEN @test BETWEEN @min AND @max THEN -1
ELSE 0
END
returns -1
Let’s try again with the comparison operators escaped for HTML.
=(A1>A2)=(A1<A3)
Personally, I’d stick with the AND(MIN…MAX…) approach. It’s the most robust allowing you to reorg the worksheet layout at will. *And,* it’s the most transparent.
how about sign((a2-a1)*(a3-a2))??
It has the advantage of recognizing the presence of a1=a2 and/or a3=a2 while also functioning as boolean (1=true)
(although come to think of it it is almost the same as the first answer, but faste to calculate I suspect)
Or (a2-a1)*(a2-a3)<0
=(A2-A1)*(A2-A3)<0
Problem with solutions involving SIGN, COVAR etc is that they work for numeric inputs only. Why make the spreadsheet brittle in that way when it’s not necessary?
Mathematically, this should work…
=ABS(A2-Average(A1,A3))>=Average(A1,A3)
oops, meant to use the less than sign…
=ABS(A2-AVERAGE(A1,A3))<=AVERAGE(A1,A3)
What a great bunch of suggestions! Looking them over, though, it’s clear that the applicability of many of them to the stated problem rests on what the author intended to happen with matching values. In other words, are the following (horizontal) sets meant to be TRUE or FALSE:
1,1,3
3,3,1
1,3,3
3,1,1
1,1,1
Based on the author’s first formula, I assumed that matching values did not constitute “between”, and so the COVAR-based solution I offered returns FALSE for all of these sets.
Likewise, the elegant multiplication-of-neighbor-differences solution offered by Lori (and repeated with a correction for the “<” by Dave, and similar to Kim’s SIGN-based solution, but without the function call) consistently returns FALSE for all of these sets. But, as a bonus, it can easily be made to return TRUE by changing the <0 to <=0.
The RANK-based solution offered by jkpieterse returns TRUE for the first and fourth sets, but FALSE for the other three.
The wonderfully simple LARGE/SMALL-based solution from Michael consistently returns TRUE for all of these sets.
Seeing the AVERAGE-based solution from David Wasserman got me thinking about another Excel function, TRIMMEAN, resulting in the following solution, which also consistently returns TRUE for all of these sets:
=TRIMMEAN(A1:A3,2/3)=A2
In the end, though, I might agree with The Contrarian and David that the author’s first and most verbose formula may prove to be best in the long run, as it is infinitely clearer about its intention than many of the proposed solutions and it has the added (and not insignificant) benefit of working with strings as well as numbers. So, here’s to being verbose:
=OR(AND(A2>A1,A2<A3 ),AND(A2<A1,A2>A3))
Simply add =’s as necessary to get TRUE for the sets shown above.
Excellent summary Eric.
Sorry to bounce up and down saying “Mine’s the best! Me! Me!” like a small child, but I really do think that suggestion 8 is the most direct expression of the problem; it will be the fastest to execute; and it will work for all inputs, not just numbers.
The number A1 will be between A2 and A3 if it is bigger than A2 and smaller than A3. In these circumstances
=(A1>A2)=(A1<A3)
evaluates to
=TRUE=TRUE
which in turn evaluates to TRUE.
Alternatively, A2 and A3 and may be in descending order. If A1 is between them, then
=(A1>A2)=(A1<A3)
evaluates to
=FALSE=FALSE
which in turn evaluates to TRUE.
Under any other circumstances, the formula evaluates to =TRUE=FALSE or =FALSE=TRUE, which evaluates to FALSE.
This can trivially be changed to include the boundary conditions (>= instead of >) etc.
David, I didn’t mention that formula in my comment because I was having some trouble getting it to return TRUE for 1,2,3 and 3,2,1, and didn’t have time to look closer at it. I did get it to work like this:
=(A1<A2)=(A2<A3)
However, it is inconsistent against the matching value sets in my prior post, returning FALSE for the 1st and 3nd ones, but TRUE for all the others.
Of course, there’s a good chance I’ve done something wrong. I do like its simplicity, and its support of strings is a plus, so can you clarify what’s happening with simple ascending/descending sets?
Ok, one of us has misread the spec, and it turns out that it’s … me. I have been seeking to show A1 is between A2 and A3; should be A2 between A1 and A3.
I like the formula’s simplicity too, but on more careful inspection you are right; it behaves splendidly except where A2 coincides with A1 or A3. In those circumstances what it returns depends on whether the numbers are ascending or descending.
The issue is that A1<A2 is not the same as NOT(A2>A1); it’s equivalent to NOT(A2>=A1). The handling of the boundary condition is therefore sensitive to the ordering of the numbers.
There’s an interesting lesson here. I tested my code with hundreds of random A1, A2, A3, each between -0.5 and +0.5 But because these are real numbers, I never got any exact matches. You rightly tested the boundary conditions with integers and identified the issue you raised. Much better.