Excel LAMBDA Function (365)
Description
The Excel LAMBDA function is used to create custom functions that can be reused throughout a workbook. And once a LAMBDA function is created and tested, it can be defined as a friendly name for calling.
Formula syntax
Arguments
|
Remarks
LAMBDA names and parameters follow the Excel syntax rules for names, except that, do not use period (.) in a parameter name.
In computer programming, the term LAMBDA refers to an anonymous function or expression, which means that once a generic version of LAMBDA function has been created and tested, you can port it to Name Manager, and formally define this formula as a name.
There just needs one copy of code to update when fixing problems or updating functionality, and changes will automatically propagate to all instances of the LAMBDA function in a workbook because the logic contained in the formula that LAMBDA function created exists in just one place.
Errors
1) #Value! Error appears when:
More than 253 parameters are entered in the formula;
An incorrect number of arguments is passed to LAMBDA function.
2) If a LAMBDA function is called from within itself and the call is circular, Excel can return a #NUM! error if there are too many recursive calls.
3) #CALC! error appears when you create a LAMBDA function in a cell without also calling it from within the cell.
Version
Excel 365
How to use LAMBDA to create a custom formula
Take an instance, to get the result of x*y+1, x in column A2:A7, y in column B2:B7, please follow the below steps to create the custom formula by using LAMBDA function and name it.
1. Test the formula
Firstly, you need to test that the arguments you will use in the calculation are working correctly.
In a cell, type the standard formula
=A2*B2+1
Press Enter key to test if the arguments in the calculation are correct.
2. Create the LAMBDA formula
To avoid the #CALC! error, please add a call to the LAMBDA function to get the result correctly.
In a cell, type the LAMBDA function as this:
=LAMBDA(x,y,x*y+1)(A2,B2)
Press Enter key.
Here A2 = x, B2=y.
3. Define a name for the LAMBDA formula
After the LAMBDA formula is created and tested, add it to the Name Manager and define a name for recalling next time in the workbook.
Click Formulas > Define Name, in the popping New Name dialog, define a name in Name textbox for the new formula, and type the LAMBDA formula into Refers to textbox. Click OK.
Now, you can use the new formula name in a cell to get the calculation result.
Other Functions:
Excel bycol Function
The Excel BYCOL function applies a LAMBDA function to each column in a given array and returns the result per column as a single array.
Excel BYROW Function
The Excel BYROW function applies a LAMBDA function to each row in a given array and returns the result per row as a single array.
Excel Z.TEST Function
The Excel Z.TEST function (2010) returns the one-tailed P-value of a z-test which is useful for various analyses.
Excel F.DIST Function
The Excel F.DIST function returns the F probability distribution which is usually used to measure the degree of diversity between two data sets.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.