Excel LAMBDA Function (365)
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.
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.
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.
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
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:
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.
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.