I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Test if between 2 values

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:
=A1=MEDIAN(A1:A3)
or
=A1=MEDIAN(A1,A2,A3)

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:

20101003_PB_01_TestBetween2Values_Screen
20101003_PB_01_TestBetween2Values_Code

If you are interested in downloading an Excel © sample file which created the output shown above, go to my Download page, please.

Sulprobil   Get it done   Contact   Disclaimer   Download