Excel Function: LAMBDA
The LAMBDA function allows you to create custom and reusable functions.
Furthermore, when used with some other functions such as MAP and REDUCE, it allows you to create powerful formulas.
Usage:
=LAMBDA(parameter, calculation)
or
=LAMBDA(parameter1, parameter2, ..., calculation)
Understanding this function
The goal here will be to create a simple function that will triple the quantity:
Enter in the LAMBDA function:
- parameter: the name of the variable (here, the number that will need to be passed as an argument and tripled by the function)
- calculation: the calculation to perform based on the argument parameter
The formula to triple the quantity is:
=LAMBDA(number,number*3)The LAMBDA function indeed returns the created function, but since no number has been passed as an argument, it cannot return a result:
When you use other Excel functions, you start by entering the function (for example AVERAGE), then in parentheses the argument(s).
With LAMBDA it's the same, enter the function (here LAMBDA(number,number*3)), then in parentheses the argument required by the function:
=LAMBDA(number,number*3)(A2)Of course we could also have done the same calculation by entering =A2*3 but the objective was to take a simple example.
Custom function
To facilitate its use, you can save and name your custom function.
In the Formulas ribbon, click on Name Manager:
Click on New, enter the name of the new function (for example "TRIPLE_QUANTITY") and the formula with LAMBDA:
You can then easily use the new created function:
And this will avoid you entering the LAMBDA function each time:
=TRIPLE_QUANTITY(A2)Function combinations
The LAMBDA function is necessary for the operation of some other functions and allows to create powerful formulas.
These other functions are:
Staying with this same simple example (which triples the quantity), using LAMBDA with the function MAP allows you to calculate the result for an entire range in a single formula:
=MAP(A2:A8,LAMBDA(number,number*3))If you have created a custom function, simply enter the name of the function:
=MAP(A2:A8,TRIPLE_QUANTITY)