LAMBDA
The LAMBDA function creates custom functions, enabling you to write reusable formulas with different inputs. It also supports advanced features like recursion and higher-order functions.
=LAMBDA(Parameter 1 [; Parameter 2 [; ... [; Parameter n]...]]; Formula)
Parameter 1: (required) The name of the first parameter to the function. The name must follow the range naming rules and cannot be the output of a reference or text formula. The scope of the parameter name is restricted to the LAMBDA function.
Parameter 2; ...; n : (optional). Additional parameters for the function. The same naming rules for Parameter 1 applies.
Formula: a formula expression. It must be the last argument of the function.
Usage
For interoperability with other office suites, the LAMBDA function must be entered as a named formula.
-
Open menu .
-
Assign a name to the expression.
-
Write the LAMBDA function expression in the box.
-
Use the named expression, passing the parameters for the LAMBDA function.
Example of a simple geometric mean
-
Assign name MY_MEAN as a named expression.
-
Enter =LAMBDA(Number1;Number2;SQRT(Number1*Number2)) in the box.
-
In cell B1, enter =MY_MEAN(2;3).
-
Cell B1 displays the number 2.44948974278318, the square root of 2 times 3.
If you don’t assign a name to LAMBDA expression, you can still use it by entering the formula in cell A1 and enter =A1(2;3) in B1. The LAMBDA function displays the Greek character lambda (λ) in cell A1. However, this makes the formula less readable and will cause interoperability issues with other office suites.
Example of a recursive call of a LAMBDA function
-
Assign name MY_FACTORIAL to a named expression
-
Enter =LAMBDA(n;IF(n>1;n*MY_FACTORIAL(n-1);1)) in the box.
-
Enter =MY_FACTORIAL(5) in cell B1
-
Cell B1 will show 120.
Using ISOMITTED in function LAMBDA
The ISOMITTED function allows you to handle missing parameters in a LAMBDA function. For example, the LAMBDA function named MY_FUNCT:
=LAMBDA(_n1; _n2; IF(ISOMITTED(_n2); "Missing second parameter"; _n1/_n2))
returns "Missing second parameter" if the LAMBDA function is called as =MY_FUNCT(3;). Note: The trailing argument separator (;) must be included in the function call.
COM.MICROSOFT.LAMBDA