How to implement an ISBETWEEN-like function for arrays of values without having to calculate the array more than once?

Question

An ISBETWEEN function tests whether a value falls between a lower bound and a higher bound. With no native ISBETWEEN function in Excel, the value under test must be compared twice; first with '>' and then with '<' (or '>=' and '<=' for an ISBETWEEN test that is inclusive of the bounds.)

Comparing the value twice means having to calculate it twice, and this can be extremely expensive when that value is an array. With array functions being somewhat cryptic even at the best of times, doubling up on such a calculation also sends the readability of the function plummeting.

My question is whether anyone knows of a technique that delivers ISBETWEEN-like functionality for an array of values without the double calculation of that array? My preference is to do this with native Excel functionality but, if anyone has some great VBA, that would be good too.

Many thanks for your time!

Will

What do you mean "ISBETWEEN-like functionality for an array of values" are you checking each value in that array for being between a max and a min or are you giving another value and seeing if that value is less than Max(array) and greater than Min(array)? — John Bustos, Sep 16 '16 at 18:55
afaik There isn't a reasonable way to get an array from a CSE into a VBA function and have it return an array for CSE to continue processing, so VBA might be a non-starter here. Perhaps the `Lookup()` function may be of use here though since it will "search" a range and return a result.Not sure if the first parameter of `Lookup()` can be an array itself. That would be interesting... — JNevill, Sep 16 '16 at 18:56
How exactly would you expect ***any*** implementation to check whether a value is between 2 numbers with only one comparison? — Comintern, Sep 16 '16 at 19:00
The only way I would imagine doing it (which is what Excel would have to do in the background anyways) would be to sort the array and then you know the max and min. So, if you wrote your own VBA method, you'd only have to iterate the array (at most) once to know - I say (at most) because you may not have to iterate fully if you found your value is already contained within. — John Bustos, Sep 16 '16 at 19:01
Hi John Bustos, I want to test each value of the array against the bounds, returning TRUE or FALSE as the output. — William Bell, Sep 16 '16 at 19:04
That being the case, then it's pretty easy to just say val >= lowerbound and val <= upperbound. That is not costly at all and Excel is made to do that. — John Bustos, Sep 16 '16 at 19:06
Hi Comintern, someone on Chandoo.org implemented ISBETWEEN for a single value without any compares! They did this using the MEDIAN function. The downside though, was that the value needed to be calculated twice. But this does go to show that Excel functions can be put to sneaky uses. I think you're 'probably' right; 2 compares will likely be needed. A possible way forward is: calc the values once and feed that into the 1st compare, and then feed that into the 2nd compare...? — William Bell, Sep 16 '16 at 19:12
John Bustos, but if val is actually {v1, v2, v3, v4,..., vn} (with possibly n in the hundreds), the compute time becomes huge. — William Bell, Sep 16 '16 at 19:14
That being your issue, then just implement it in VBA where your first assignment is `Dim ToCompare as Double` `ToCompare = cell.Value` then use that for the comparison. It will compute it once, store it in the Double within VBA and use that Double for comparing against your max and min. — John Bustos, Sep 16 '16 at 19:17

score 3 · Answer 1 · answered Sep 16 '16 at 19:07

3

Building from my comment above: This doesn't provide a 100% answer to your question, but since it was pretty generic, I think this is the closest to an answer that I can get.

Imagine a spreadsheet set up like:

We can get a count of all the values that are between 3 and 5 using CTE/Array formula:

={SUM(IF(LOOKUP(A1:A6,{3,"B";6,"C"})="B",1,0))}

Results:

That's a pretty round-about way of doing this, but the array of A1:A6 only needs to be referenced once. Which is pretty cool.

Note that the squirrely brackets in the above formula aren't actually entered, but are placed by excel when you enter the array formula to indicate that it's an array formula... you probably already know that though if you've read this far.

answered Sep 16 '16 at 19:07

JNevill

46,980
4
38
63

Good answer, JNeville, but read his comment above, I don't think this is what he wanted to achieve. – John Bustos Sep 16 '16 at 19:11
1

I disagree. Instead of returning true or false, here I return 1 or 0 and then sum that. 6 of one, half a dozen of the other. One could remove the `if()` statement from the CSE and have the `lookup()="B"` spit out the boolean directly to whatever OP is wrapping this with. – JNevill Sep 16 '16 at 19:13
hmmmmm JNevill, using LOOKUP is a very interesting idea!!! I'm going to have a play with it! :) – William Bell Sep 16 '16 at 19:27
I tailored this to spit out an array of Booleans and it is really clever! – William Bell Sep 16 '16 at 20:20
But, I wasn't clear above about an important point... I need to perform the ISBETWEEN with the bounds in arrays too.... So, imagine the value to test is in column A, and the lower and upper bounds are in B and C. Then we're testing for B1 <= A1 <= C1, B2 <= A2 <= C2, ..., Bn <= An <= Cn. None of the columns can be sorted. I'm guessing we're in the realms of VBA now? – William Bell Sep 16 '16 at 20:30
Yes... sounds like VBA might be the right fit unless you can figure out how to shoehorn an array of arrays into the second parameter of the `lookup()` function. That'd be cool, but I wouldn't want to inherit that spreadsheet after you get hit by a bus. – JNevill Sep 16 '16 at 20:46
Yes but if I change one of the values to (say) 1 I get #N/A – Tom Sharpe Sep 16 '16 at 21:22
Maybe this would fix it =SUM(IF(LOOKUP(A1:A6,{-9E+307,"A";3,"B";6,"C"})="B",1,0)) – Tom Sharpe Sep 16 '16 at 22:00
I'm working on some VBA but posting it in a comment loses the formatting... – William Bell Sep 16 '16 at 22:34
Tom Sharpe, yes setting a very low lower bound will get that formula to work – William Bell Sep 16 '16 at 22:35

score 0 · Accepted Answer · edited May 23 '17 at 12:32

So I've been able to develop a piece of VBA, based on the idea here.

Dim vValueArg As Variant, vLowerArg As Variant, vUpperArg As Variant, vTestLower As Variant, vTestUpper As Variant

Function ISBETWEEN(vValue As Variant, vLower As Variant, vUpper As Variant, Optional bInc As Boolean = True) As Variant

vValueArg = vValue
vLowerArg = vLower
vUpperArg = vUpper

If bInc Then
   vTestLower = [GetValue() >= GetLower()]
   vTestUpper = [GetValue() <= GetUpper()]
Else
   vTestLower = [GetValue() > GetLower()]
   vTestUpper = [GetValue() < GetUpper()]
End If

ISBETWEEN = [IF((GetTestLower() * GetTestUpper()) = 1, TRUE, FALSE)]

End Function

Function GetValue() As Variant
   GetValue = vValueArg
End Function

Function GetLower() As Variant
   GetLower = vLowerArg
End Function

Function GetUpper() As Variant
   GetUpper = vUpperArg
End Function

Function GetTestLower() As Variant
   GetTestLower = vTestLower
End Function

Function GetTestUpper() As Variant
   GetTestUpper = vTestUpper
End Function

The first argument can be a single value, range or array. If a single value, then the next two arguments must also be single values (but this kinda defeats the purpose of the code!)

The second and third arguments can also be a single value, range or array. If a range consisting of multiple cells or array of multiple values, then the dimensions of these arguments must match those of the first argument. (NB - I have NOT tested the code with 2 dimensional ranges or arrays!)

The final, optional, argument determines whether the ISBETWEEN test is performed including or excluding the bounds. TRUE = include bounds; i.e. arg2 <= arg1 <= arg3 (the default, and can therefore be omitted). FALSE = exclude bounds; i.e. arg2 < arg1 < arg3.

While this might not be the prettiest code in the world, it is compact, fast (no loops) and copes with ranges and arrays of any size.

Hope some of you find this useful! :)

How to implement an ISBETWEEN-like function for arrays of values without having to calculate the array more than once?

2 Answers2