LAMBDA Functions

The following worksheet functions are provided only to Excel 365 users. 

We start with the LAMBDA function, which provides the ability to create custom functions and is the basis for all the other functions described on this webpage.

LAMBDA Function

This function takes the form

LAMBDA(optional parameter list, expression)

parameter list consists of a list of variable names, separated by commas. Up to 253 parameters can be used. expression is an Excel formula that returns the value of the LAMBDA function using elements from the parameter list.

Essentially LAMBDA provides a way to define a new function. E.g. if you want to define a new function that calculates the circumference of a circle, you can define this function as LAMBDA(r, 2*Pi()*r). In order to use this function, you need to give it a name, which is usually done by using the Name Manager.

For example, you can do this by selecting Formulas > Define Names|Name Manager > New (or by pressing Ctrl-F3) and then inserting the following information in the dialog box that appears.

You can then enter the formula =CIRCUM(A1) in say cell B1. If cell A1 has the value 3, then cell B1 will take the value 2π(3) = 18.84956.

Note that you can also obtain this value by placing the formula =LAMBDA(r,2*Pi()*r)(3) in cell B1. This is useful when testing the new LAMBDA-based function and before assigning it a name.

You can create a function that calculates the volume of a cylinder with height h and radius r by using =LAMBDA(r, h, h*Pi()*r^2). To test this function (for r = 3 and h = 5), you can place the formula in a =LAMBDA(r,h, h*Pi()*r^2)(3,5) in a cell. The value should be π*3^2*5 = 141.3717.

Omitted Parameters

You can test whether one of the parameters in the parameter list is missing by using the conditional expression ISOMITTED(y), which takes the value TRUE if the y parameter is missing and FALSE otherwise.

E.g. =LAMBDA(r, h, IFOMITTED(h,”no height”, h*Pi()*r^2))(3,5) again returns 141.3717, but =LAMBDA(r, h, IFOMITTED(h,”no height”, h*Pi()*r^2))(3,) returns the value “no height”.

LAMBDA-based worksheet functions

LAMBDA can be used with a number of new Excel functions without assigning a name via Name Manager. These new functions are BYROW, BYCOL, REDUCE, MAP, SCAN, and MAKEARRAY.  All these functions are available only to Excel 365 users.

Click here for a description of these Excel worksheet functions.

References

Microsoft (2021) Lambda function
https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67

Microsoft (2021) IsOmitted function
https://support.microsoft.com/en-us/office/isomitted-function-831d6fbc-0f07-40c4-9c5b-9c73fd1d60c1