“Every wall is a door.” [Ralph Waldo Emerson]


Of course you can easily test whether a number A1 is between two others A2 and A3 with

=AND(A2>=A1,A1<=A3) [resulting in True resp. False]

or with

=(A2>=A1)*(A1<=A3) [resulting in 1 resp. 0]

It is also easy to exclude the border values by omitting the ‘=’ in the formulas.

Another tricky approach is using the MEDIAN function:




The nice thing about MEDIAN is that you do not need to care whether A2 is less or greater than A3. But only if you include the border values into your test! Beware if you need to exclude the border values: