A little while ago Brendan asked me how he could check if values in a list fall between two limits.
Now, it’d be nice if there was a BETWEEN Function, but there isn’t. Hence why the title of this tutorial isn’t ‘BETWEEN Function’, it’s ‘BETWEEN Formula’.
I’ve got two options for you:
BETWEEN with MAX and MIN
The formula in D3 evaluates as follows:
Step 1 =AND(-5 < MAX(-C3,B3), -5 > MIN(C3,B3))
Step 2 =AND(-5 < MAX(- -4,4), -5 > MIN(-4,4))
Step 3 =AND(-5 < 4, -5 > -4)
Step 4 =AND(TRUE , FALSE)
Step 5 =FALSE
Note how this formula excludes the upper and lower limits.
If you wanted it to be inclusive of the limits, i.e. include -4 and 4 in your ‘between’ values, you can either change the limits to -5 and 5, or alter the formula to increase/decrease the limit by 1.
BETWEEN with MEDIAN (Inclusive)
The formula in D12 evaluates as follows:
Step 1 =A12=MEDIAN(A12,C12,B12)
Step 2 =-5=MEDIAN(-5,4,-4)
Step 3 =-5= -4
Step 4 =FALSE
Remember the Median is the number in the middle of a set of numbers.
This part of the formula:
returns the number in the middle of the upper limit, lower limit and the value we’re checking in A12.
So if A12 is the MEDIAN value or the value in the middle, then the whole formula evaluates to TRUE.
Note how this formula is inclusive of the upper and lower limits. Next is an exclusive option.
BETWEEN with MEDIAN (Exclusive)
This formula simply includes the limits by increasing/decreasing them by 1.
Thanks to Roberto for the MEDIAN solution.
If you like this tip please share it using the Facebook, LinkedIn, Twitter and +1 Google buttons below, or please leave a comment.