Note: The other languages of the website are Google-translated. Back to English

Excel LAMBDA Function (365)

doc dec2oct 1

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

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

Arguments

  • paremeter1,parameter2,...: Optional, the value that you want to pass to the function, they can be cell reference, ,strings, numbers, Up to 253 parameters are allowed to entered.
  • calculation: Required, the calculation to perform as the result of the function. It must be the last argument and it must return a result.

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.
doc dec2oct 1

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.
doc dec2oct 1

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.
doc dec2oct 1  doc dec2oct 1

Now, you can use the new formula name in a cell to get the calculation result.
doc dec2oct 1


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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL