- Microsoft (Office) 365
|[parameter]||Optional. Argument(s) that you want to use in your function. You can enter up to 253 parameters.|
|calculation||The formula of your function using parameters if available.|
Testing a LAMBDA function
First, the LAMBDA function is used in named ranges, not cells. However, you can still test a LAMBDA function in a cell before adding it into a named range. Once you create the function, you won’t be able to debug it as easily as you could inside a cell.
Begin test by creating the LAMBDA function in a cell. Let’s say you want a formula which calculates the area of a circle by a given radius value with a formula like below.
Only variable in this formula is the radius value, which will be our parameter. With the help of the LAMBDA function the area formula would be like following:
If you copy paste the above formula, you will see a #CALC! error. This is because our formula is missing an argument.
Instead, use a static value or a cell reference with a value in parenthesis to see the result.
Creating a LAMBDA Function
Once you are satisfied with the result, assign the formula to a named range to use it as a custom function.
- Open the New Name dialog by following the Formulas > Define Name path in the Ribbon.
- Type in a friendly name for your formula.
- Enter the LAMBDA formula with any argument value.
- Click the OK button to create your user defined function.
- Once the named range is saved, you can use it just like any other formula.
Using the LAMBDA function recursively
Recursive calculation is functions that call themselves from within their own code. This way, you can create loops in your own custom functions.
Let’s take the Fibonacci sequence - a sequence of numbers where each number is the sum of the two that precede them, starting from 0 and 1. The formula is shown below.
The nth number of the Fibonacci sequence needs to calculate the (n-1)th and (n-2)th numbers. We can handle this recalculation by creating a recursive function. The following formula belongs to a function named Fib.
Note that the function fib is called twice inside its own function.
- When creating a formula, you can use Excel syntax rules for names, with one exception: You can’t use a period (.) in parameter names.
- You can add up to 253 parameters.
- If you enter more than 253 parameters, Excel returns a #VALUE! error.
- The LAMBDA function returns a #VALUE! error if an incorrect number of arguments is passed.
- If you call a LAMBDA function from within itself and the call is circular, Excel returns a #NUM! error.
- If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.