A lambda that returns a value constrained within a lower and upper limit.

Development Rationale

The purpose in developing this lambda is to simplify the derivation of values that are constrained between a lower and upper boundary, which arise in labour costings, production runs, debt modelling etc.

About the Lambda Calculation

This lambda encapsulates a call to the associated lambda λValueBetween from this library that simplifies determining whether a value falls within, above or below a range of values.  Based on its results, the constrained value is returned.

1  =LAMBDA(value,lower,upper,
2  SWITCH(λIsBetween(value,lower,upper),-1,lower,1,upper,value))

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 a SWITCH function to test for the values returned by λIsBetween.  If it returns -1, which indicated value < lower, then lower is returned.  If it returns 1, which indicates value > upper, then upper is returned, otherwise, value is returned.

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 λValueBetween

=λConstrainedValue(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.

NB: This lambda function requires the λIsBetween function to be available in the workbook, since it depends upon it to determine whether value falls within the range lower to upper!

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.