This section of the Clarkson ITT Wiki includes the λ Library 2.0. The library contains articles and videos providing pre-cut LAMBDAs that may be copied and pasted into your own projects to solve common problems in modelling and in analytical tools. These are examples with basic explanation and documentation. For more general explanation on how and when to use LAMBDA and when to create user defined functions, see the various courses on this site.
Access to some of these articles is restricted to alumni of our various courses and subscribers to The Learning HabITT subscription program.
λAddressOf
Type: Referencing - providing reference information
A lambda to simplify the process of deriving the address of a range for the purposes of navigation (use in hyperlinks) and documentation and checklists..
λArrayAnd
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda to simplify calculating AND conditions row-by-row or column-by-column in array expressions in place of using Product of Comparison..
λArrayN
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda to simplify calculating N values in array expressions in place of using cumbersome IF(ISNUMBER) formulas.
λArrayOr
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda to simplify calculating OR conditions row-by-row or column-by-column in array expressions in place of using Product of Comparison..
λArrayT
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda to simplify calculating T values in array expressions in place of using cumbersome IF(ISTEXT) formulas.
λBlockCalcWithTotals
Type: Modelling Lambda - streamline modelling tasks
A lambda to simplify the generation of totals on a block calculation. The calculation is passed to this lambda with flags that indicate whether totals should be calculated along the rows and down the columns, and for each, whether it precedes or is appended to the array of results. This allows the totals to move automatically when the size of the results changes dynamically, such as for a FILTER function, or a formula depending upon a variable dynamic array calculation.
λCheckUserSelection
Type: Modelling Lambda - streamline modelling tasks
A lambda that checks a user selection against a validation list source, and if not selected, or no longer valid, returns a default entry.
λConstrainedValue
Type: Modelling Lambda - streamline modelling tasks
A lambda that returns a value constrained within a lower and upper limit.
λCorkscrew
Type: Modelling Lambda - streamline modelling tasks
A lambda that constructs a dynamic corkscrew range for modelling showing the rolling opening balances, movements and closing balances, which may be arranged horizontally (the default) or vertically.
λCorkscrewH
Type: Modelling Lambda - streamline modelling tasks
A lambda that constructs a dynamic horizontally-aligned corkscrew range for modelling showing the rolling opening balances, movements and closing balances. It is a simplified version of the main λCorkscrew function
λEasterOrthodox
Type: Date and Financial Period - simplify calendrical calculations, especially for financial periods
A lambda to calculate the date of Easter for any year from 1900 (Excel's earliest year) to 2400 as celebrated by the Orthodox churches.
λEasterWestern
Type: Date and Financial Period - simplify calendrical calculations, especially for financial periods
A lambda to calculate the date of Easter for any year from 1900 (Excel's earliest year) to 2400 as celebrated by the Western churches, both Roman Catholic and Protestant.
λFinancialHalf
Type: Date and Financial Period - simplify calendrical calculations, especially for financial periods
A lambda to encapsulate the calculation of the Financial Half Year and present it in four ways.
λFinancialHalfEnd
Type: Date and Financial Period - simplify calendrical calculations, especially for financial periods
A lambda to encapsulate the calculation of the Financial Half-end Date for a given date.
λFinancialQuarter
Type: Date and Financial Period - simplify calendrical calculations, especially for financial periods
A lambda to encapsulate the calculation of the Financial Quarter and present it in four ways.
λFinancialQuarterEnd
Type: Date and Financial Period - simplify calendrical calculations, especially for financial periods
A lambda to encapsulate the calculation of the Financial Quarter-end Date for a given date.
λFinancialYear
Type: Date and Financial Period - simplify calendrical calculations, especially for financial periods
A lambda to encapsulate the calculation of the Financial Year in which a date falls.
λFinancialYearEnd
Type: Date and Financial Period - simplify calendrical calculations, especially for financial periods
A lambda to encapsulate the calculation of the Financial Year-end Date for a given date.
λFlexTotals
Type: Modelling Lambda - streamline modelling tasks
A lambda that returns totals by column, but for a source range which includes rows of independent array formulas and for which the dimensions may change, both by column and by row.
λIsBetween
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda test whether a value is between a set of constraints, lower than the lower boundary or above the upper boundary.
λIsDuplicate
Type: Modelling Lambda - streamline modelling tasks
A lambda that returns TRUE if the entry in a range is a duplicate of an earlier entry, otherwise, returns FALSE. May be used with multiple rows or columns, by concatenating the row or column values to test for duplicate keys over multiple rows or columns.
λIsSorted
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda to simplify the process of determining if a range or array is sorted in ascending order which may be necessary to check or monitor if, for example, XLOOKUP and XMATCH functions have been used based on binary searches in ascending order. It may be necessary to have a check formula that warns if the user changes the sort order in a table or data range.
λOversAndUnders
Type: Modelling Lambda - streamline modelling tasks
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.
λPivotbyHeadings
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda that simplifies replacing the function name headings in a PIVOTBY result set with more useful text that is suitable for reporting. It can handle more than one row field and more than one column field.
λSubtotalByColumn
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda to simplify the generation of subtotals by column from dynamic arrays.
λSubtotalByRow
Type: Array Simplification Lambda - simplify array calculations to make them easier to read
A lambda to simplify the generation of subtotals by row from dynamic arrays.
λValidationSource (Sundowner Lecture Series)
Type: Modelling Lambda - streamline modelling tasks
A lambda to enable the dynamic selection of validation list source ranges based on either a hard-wired list name, or a list dependent upon a precedent selection. It simplifies and dramatically improve the management of dependent drop-down lists in Excel, where the choices in one list need to change in response to a selection in another list. This is the lambda developed in the Sundowner Lecture Series lecture 2401 in February 2024.
λVarianceToBase
Type: Modelling Lambda - streamline modelling tasks
A lambda to simplify deriving variances, and handling base values of 0.
λWorksheetName
Type: Referencing - providing reference information
A lambda to encapsulate the calculation of the name of the worksheet in which a referenced cell is located.