Function Reference: LAMBDA
| Syntax | ||
| =LAMBDA(parameter_or_calculation1,[parameter_or_calculation2],...[parameter_or_calculation254]) | ||
| Purpose / Description | ||
| Allows the definition of a lambda function. A lambda function is a user defined function built using existing functions in the product, in this case, Excel. A LAMBDA can be used directly in formulas that require them, such as BYCOL, BYROW, SCAN etc., or it can be used to create a stored user-defined function. User-defined functions are created by defining a name that uses the LAMBDA function, which defines the parameters (arguments) to be passed to the function and the calculation to be carried out. Using a nested LET function, these lambdas can work through many multi-step calculations, and they are recursive, so a lambda can call itself. But we would propose the following, as an easier way of understanding the intent... =LAMBDA([argument1],...[argument253],calculation) That is, there can be up to 253 optional arguments defined and finally, a required calculation which derives the value returned by the LAMBDA. It is difficult to overstate the importance or dramatic impact this has had on the models and calculations we can undertake, and the massive simplifying of the process for deploying user defined functions, without the need to rely on programming languages, installing and registering add-ins etc.. |
||
| Arguments | ||
| Name | Type | Description |
| parameter_or_ calculation1 |
text string or Excel expression |
If this argument defines a parameter, it must be a valid argument name as a text string. Similar rules apply as for the LET function. It should be alphanumeric, exclude punctuation and special characters other than the underscore(_). If this is the final argument, representing the calculation or result of the lambda, it may be any valid constant value (within Excel's defined limits) or any valid Excel expression. If no parameters are required, then the only argument will be the calculation. Excel has functions such as PI, NOW, TODAY, RAND that require no arguments, their value is intrinsic to the system or globally or universally defined. |
| [parameter_or_ calculation2]... [parameter_or_ calculation254] |
text string or Excel expression |
If this argument defines a parameter, it must be a valid argument name as a text string. Similar rules apply as for the LET function. It should be alphanumeric, exclude punctuation and special characters other than the underscore(_). If this is the final argument, representing the calculation or result of the lambda, it may be any valid constant value (within Excel's defined limits) or any valid Excel expression. |
| Return Values | ||
| Type | Specific Value | Description |
| (array of) any | The result of the calculation embodied in the LAMBDA function. It may be any single value, or an array of values, depending upon the calculations performed and whether arrays of arguments are passed. | |
| error | #VALUE! | More than 253 parameters are defined, as the maximum number of arguments is 254, the last, regardless of how many arguments are used is always the resulting calculation. This therefofore sets the limit to the number of arguments to 253. |
| error | #VALUE! | A call to a LAMBDA omits any required parameters. |
| error | #CALC! | When defined in a cell, a LAMBDA must be called by following the definition with parentheses containing any parameter values or references. If omitted, this error is returned, see sample file. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Comment: | This is a humbdinger! Together with the LET function, this has opened up huge possibilities in building effective models with tight control on errors. I could, but would not want, to live without it now! That's just me editorialising 😐 | |
| Comment: | The LET function can be extremely useful in building multi-step LAMBDAs, as it makes the layout easier to manage and audit. The advent of the Advanced Formula Environment also makes this much easier, once you get familiar with it, but we have lots of examples on the site building them simply from scratch, testing them in cells, then copying and pasting to a name definition. | |
| Comment: | In designing a lambda function, on should consider whether the values passed as arguments may be arrays, and if so, prepare the calculations to handle them so that the newly created function operates well in the modern Excel environment of dynamic array calculations. | |
| Comment: | User defined functions built with LAMBDA can be shared within an enterprise in a couple of relatively easy ways, avoiding the need to install complex add-ins. They can simply be copied and pasted between workbooks, or can be downloaded and installed from GitHub, using the Advanced Formula Environment. | |
| Examples: | Open / download sample workbook that covers the basics for this function: LAMBDA Basics.xlsx. More detailed and advanced examples showing how and why to use this function in real-world examples can found in the exercises and min-courses which can be found via the LAMBDA function tag below | |