A lambda that returns a the amounts over an upper limit or under a lower limit.  Useful in calculations related to capacity.  Effectively the differences between the original values and the values returned by λConstrainedValue.

Development Rationale

The purpose in developing this lambda is to simplify the process of deriving the values above and below limits which arise in labour costings, production runs, debt modelling etc.

About the Lambda Calculation

1  =LAMBDA(value,lower,upper,[return_overs],[return_unders],
2  LET(overUnders,value-λConstrainedValue(value,lower,upper),
3  useOvers,IFS(ISOMITTED(return_overs),TRUE,ISNUMBER(1*return_overs),(1*return_overs)<>0,TRUE,FALSE),
4  useUnders,IFS(ISOMITTED(return_unders),TRUE,ISNUMBER(1*return_unders),(1*return_unders)<>0,TRUE,FALSE),
5  useOvers*overUnders*(overUnders>0)+useUnders*overUnders*(overUnders<0)))

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

In line 2, the LET function that handles the nested steps of calculation is commenced, and the local variable overUnders is set to the difference between the value(s) passed in the value argument and those values returned by the λConstrainedValue function.  If the value isn't constrained, the value derived will be 0, otherwise it will be the amount above upper or below lower.

In line 3, the local variable useOvers is set to indicate if overs should be returned.  If the optional return_overs argument is omitted, the value is set to TRUE.  Numbers are coerced and text strings are replaced with FALSE.

In line 4, the local variable useUnders is set to indicate if overs should be returned.  If the optional return_unders argument is omitted, the value is set to TRUE.  Numbers are coerced and text strings are replaced with FALSE.

In line 5, the return value of the LET function is derived, which is the overall result from the LAMBDA.  It is the result of two Products of Comparison applied to overUnders such that over limit values are filtered in if useOvers is TRUE and under limit values are filtered in if useUnders is TRUE.

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

=λOversAndUnders(value,lower,upper,[return_overs],[return_unders])

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 from which the differences to the limits are to be returned.

   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_overs] is an optional reference to a range, a nested expression that returns a value or array of values,or a constant value or array of constants that flags whether over limit values are to be returned.  TRUE or FALSE is expected.  TRUE is the default value if it is omitted.  Numbers are coerced to logical values, 0 = FALSE, all other numbers = TRUE.  Text strings are replaced with FALSE.

   [return_unders] is an optional reference to a range, a nested expression that returns a value or array of values,or a constant value or array of constants that flags whether under limit values are to be returned.  TRUE or FALSE is expected.  TRUE is the default value if it is omitted.  Numbers are coerced to logical values, 0 = FALSE, all other numbers = TRUE.  Text strings are replaced with FALSE. 

NB: This lambda function requires the λIsBetween and λConstrainedValue functions to be available in the workbook, since it depends upon them 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: ModellingLambdaSamples.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.