A lambda test whether a value is between a set of constraints, lower than the lower boundary or above the upper boundary.

Development Rationale

The purpose in developing this lambda is to simplify the testing of values as to whether they fall between, below or above a specified range of values.  It relieves the issues that arise if an array of values is to be tested, and the complexities that then arise using AND and OR logic in the formulas testing the values as to whether they fall within the constraints.

In some cases, the need is not just to determine if the value is in a specified range, but if the value is outside the range, to determine a result for values above or below the range, hence we decided not just to return TRUE or FALSE, but in a similar way to the standard Excel SIGN function, to return -1 to indicate the value is less than the lower constraint, 0 to indicate it is between the constraining values and +1 if it is above the upper constraint.

About the Lambda Calculation

The process of calculating the the required results is fairly straight forward.  The value of this lambda is in simplifying a whole range of calculations where values need to be filtered as falling within a range, and can be useful not only in forecasting formulas, but in analytical formulas using functions such as FILTER, GROUPBY and PIVOTBY.

1  =LAMBDA(value,lower,upper,
2  IFS(ISOMITTED(lower),#VALUE!,ISOMITTED(upper),#VALUE!,ISOMITTED(value),#VALUE!,value<lower,-1,value>upper,1,TRUE,0))

In line 1 above, the lambda is defined, with three arguments capitalised as is usual for Excel functions: value, lower and upper.  The values of all three arguments may be any valid Excel value, including blank cells.  Blank cells will be treated as the number 0.

In line 2, the return value of the lambda function is derived, using an IFS function to test for several possible results.  Each of the arguments is tested for omission, using ISOMITTED.  If one or more arguments are omitted, the function returns #VALUE!.   Otherwise, the value argument is compared to see if it less than the lower argument, and if so then -1 is returned.  Then value is compared to see if it is greater than upper and if so then 1 is returned.  Finally, if none of the preceding conditions is met, the value returned is 0, indicating that value is between lower and upper inclusively.  In deriving values, it uses standard Excel value comparison rules, as follows: number values < text string values < logical values

Defined Name Settings

To create the lambda, simply use the define name¹ feature in Excel to define this named lambda in the workbook where it will be used.  The required settings are shown below and may simply be copied from here and pasted into the Define Name dialog box.

Syntax of λIsBetween

=λIsBetween(value,lower,upper)

where:
   value is a reference to a range, a nested expression that returns a value or array of values,or a constant value or array of constants that is (are) to be compared to see if it (or they) fall between a set of constraints.

   lower is a reference to a range, a nested expression that returns a value or array of values,or a constant value or array of constants that provide the lower constraint value(s) against which value is compared..

   upper is a reference to a range, a nested expression that returns a value or array of values,or a constant value or array of constants that provide the upper constraint value(s) against which value is compared.

return values

Sample File

An example file, showing the definition and use of this lambda is available here: Public Lambda Samples.xlsx.

Version History

¹ Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.

²  You may change the name of the lambda and drop the prefix.  The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source.  The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.