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:
MEDIAN(A12,C12,B12)
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
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.
David N says
Your BETWEEN MEDIAN exclusive solution (at least the idea that it’s plus/minus 1 by default) only works with whole numbers. Say you have Value = 4.5, Upper = 5, and Lower = 2. Then using plus/minus 1 as the default ends up with MEDIAN(4.5,2+1,5-1) = MEDIAN(4.5,3,4) = 4 and returns FALSE instead of TRUE. Of course it could work with some other plus/minus shift as long as that shift was appreciably small compared to the size of any possible decimal fractions.
In the end, the MAX MIN approach seems more manageable, particularly if you don’t know which number should serve as the upper/lower limits. Otherwise you would just use direct comparisons as Bryan described. But I don’t really see the point of including a negative sign in the MAX portion, and have to disagree that making it inclusive should be accomplished by increasing/decreasing the limits by 1 as opposed to simply tacking an “or equal to” onto one or both of the inequality signs.
=AND(A3<=MAX(B3,C3),A3>=MIN(B3,C3))
Morton Wakeland says
Mynda, it’s “ole Mort” in Dallas, Texas….Hey Happy Mother’s Day to you – let them spoil you today – smile.
I have a Table of data on one worksheet – very simply as an example
3 columns
0 10 -20%
11 20 -10%
21 30 0
31 40 +15%
41 50 +20%
I have a “value” on another worksheet in a Table row, say it is 36, I want to use MEDIAN to find that I should use a factor of +15%.
Am also confused with referencing data in Tables on other worksheets and [@headername]
Most often when I begin typing the reference header name it will appear with others in a list, now it’s not, the names I’m using are LowerValue and UpperValue.
Thanks for any suggestions Mynda…
Mort Wakeland, Dallas, TX
Mynda Treacy says
Hi Mort,
I’d use a VLOOKUP on a sorted list to return the factor or +15%. Here is an example:
https://www.myonlinetraininghub.com/excel-2007-%e2%80%93-vlookup-sorted-list-explained
Not sure what’s going on with your table references but have you tried using your mouse to select them instead of typing them in?
Mynda
P.S. had a lovely Mother’s day, thanks!
Catalin Bombea says
Hi Morton,
Hope you and Mynda don’t mind if i give you another option to solve your problem, in fact, i think you are already familiar to this solution:
If the data you provided is in range A2:C6 (only data, no headers), then you can use this formula:
=INDEX(C2:C6,MATCH(A10,A2:A6,1)) , where A10 holds the value to be searched (that 36 you mentioned).
The ranges can be hard typed into the formula, like this:
=INDEX({-0.2;-0.1;0;0.15;0.2},MATCH(A10,{0;11;21;31;41},1))
The result is exactly the same, of course.
But if you are looking for an interpolation, to give the precise percentage, even if your data table has a higher granularity, you can try the interpolation formula and a nice interpolation UDF i made as a student.
The sample file can be downloaded from MOTH OneDrive Folder.
Cheers,
Catalin
Bryan says
Your first formula doens’t work if your upper limit and lower limit are both negative numbers, and the value you compare against is positive. For example, if Value = 3, Upper Limit = -2, and Lower Limit = -4, then the formula evaluates to: AND(3MIN(-4,-2) –> AND(3-4) –> AND(TRUE,TRUE) –> TRUE.
Couldn’t you just use =AND(A3C3)?
Mynda Treacy says
Hi Bryan,
Thanks for your comment. Another good reason to use the MEDIAN method as opposed to the MAX and MIN option.
I’m not sure what you mean by ‘just use =AND(A3,C3)’, where are you using them?
Kind regards,
Mynda.
Bryan says
Sorry about that, it looks like it treated my greater than and lesser than signs as an HTML tag and removed them (some of my formula explanation is missing as well). The formula was supposed to be =AND(A3 {lesser than} B3,A3 {greater than} C3).