Function: LAMBDA

by | Jan 22, 2021 | Excel Formulas

Excel LAMBDA Function

LAMBDA is a special function that can create custom functions in the scope of a named range. You can essentially create a user defined function without any VBA, macro, or JavaScript knowledge. In this guide, we’re going to show you how to use the LAMBDA function and also go over some tips and error handling methods.

Supported versions

  • Microsoft (Office) 365

Syntax

LAMBDA([parameter1, parameter2, …,] calculation)

Arguments

[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.

Examples

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.

A = π * radius²

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:

=LAMBDA(radius,PI()*POWER(radius,2))

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.

=LAMBDA(radius,PI()*POWER(radius,2))(5) returns 78.54

Excel LAMBDA Function Testing

Tip: If your function has more than one parameter, separate them using commas. For example, a trapezoid’s area function can look like the following: =LAMBDA(a,b,h,0.5*(a+b)*h)(2,3,4) returns 10

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.

  1. Open the New Name dialog by following the Formulas > Define Name path in the Ribbon.
  2. Type in a friendly name for your formula.
  3. Enter the LAMBDA formula with any argument value.
  4. Click the OK button to create your user defined function.
  5. 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.

=LAMBDA(n,IF(n<=1,n,Fib(n-1)+Fib(n-2)))

Note that the function fib is called twice inside its own function.

Excel LAMBDA Function 05

Download Workbook

Tips

  • 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.

Issues

  • 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.