Excel users are rightly interested in Lambda() a very powerful addition to Excel. Lambda lets you create user-defined functions without VBA. No need for .xlsm files with all the security concerns they entail. A long formula can be written once then referenced anywhere in the workbook by a short name of your choosing.
User-Defined Functions – UDF’s have been possible for a long time via VBA, we’ve used them ourselves to do things not possible or difficult with standard functions. See Many Ordinal RANK() options in Excel with joint, equal rankings, words and more . But VBA means using .xlsm files which add security concerns about what’s in the VBA.
Lambda() is a partial solution to that problem as well as adding something that’s long been missing from core Excel; recursion.
Basic Lambda() example
Let’s start with a simple example of Lambda() using a long formula from the Office Watch article on nested IF’s in Excel.
This long formula returns a name for a numbered grade.
IF(Mark>89,"HIGH DISTINCTION", IF(Mark>79,"DISTINCTION",
IF(Mark>69,"CREDIT",IF(Mark>49,"PASS","FAIL")))))
That formula has to be copied across many cells and worksheets. There’s a risk of the formula going awry or becoming inconsistent (e.g. the names or levels changing).
Instead of many copies of the formula, Lambda gives you a single custom-named function (such as GradeName() ) which applies throughout the workbook.
If there’s a change in grading rules, there’s only one formula that needs to be fixed.
Setup the custom function in Formulas | Defined Names | Define Name.
Define Name is a truly awful place to edit any Lambda formula. It’s a single line that’s hard to navigate. Eventually Microsoft added a better interface to make Lambda() see Microsoft fixes a big hassle with Excel’s great Lambda()
Name – The name of the custom function
Refers to – the Lambda() formula.
In this case the complete formula looks like this:
=LAMBDA(Mark,IF(Mark>89,"HIGH DISTINCTION",IF(Mark>79,"DISTINCTION",IF(Mark>69,"CREDIT",IF(Mark>49,"PASS","FAIL")))))
The custom function appears as you type.
What about Let()?
If Lambda() seems familiar, it’s because of another Excel innovation – Let() from earlier this year.
Let() allows you to assign a name to a calculation then use that name many times within a formula. Lambda() is similar but works across an entire workbook instead of a single formula.
LET() assigns names to calculations in Excel
Making a Lambda()
Lambda() syntax is:
Lambda( <input names> … , <calculation>)
Multiple input parameters are possible as long as the final item is the formula/calculation).
These are all simple Lambda() examples:
Lambda( x, x * 3.14159 ) multiplies x by Pi
Lambda ( Fred, Wilma, Fred + Wilma) adds the values of Fred and Wilma
The input parameters have names (like ‘Mark’ above) that are referenced in the formula.
There can be up to 253 parameters but there can’t be a period/fullstop in a name.
Dynamic Arrays are supported either as input or returning from a Lambda().
Data Types can also be inserted into a formula or returned.
Recursion
A missing part of core Excel has been recursion – a function calls itself to repeat a process as many times as necessary.
It’s been possible in VBA with a DO … UNTIL or FOR loops but not in plain formulas, until now.
We’ll use Microsoft’s good example of recursion. It loops through a string to remove unwanted characters from a string. In this case, delete any digits to leave text only.
That’s done with a Lambda() function called ReplaceChars() which calls itself.
=LAMBDA(textString, illegalChars, IF(illegalChars="", textstring, REPLACECHARS( SUBSTITUTE(textString, LEFT(illegalChars, 1), ""), RIGHT(illegalChars, LEN(illegalChars)-1) )))
Notice that inside ReplaceChars() (as made with Lambda() ) it calls itself – ReplaceChars(). That means it’ll keep looking for unwanted characters until the IF() test is True. Recursion needs an exit test, if not the formula would continue forever!
Great work!
We could write a lot more about Lambda() but we don’t have to! For once, Microsoft has published good and (almost) comprehensive documentation for a new feature.
Too often, new Office features get more hype than detail. Sometimes just a single, vague paragraph. Customers are left to discover the details and shortcomings for themselves.
The Lambda() team is to be congratulated. The opening article has good examples; we’ve used one above. There’s already standard page for the Lambda() including limitations and more examples – though it’s misleading about which Excel’s have the new feature … see below.
For more on Lambda start with:
Announcing LAMBDA: Turn Excel formulas into custom functions
Then hop over to LAMBDA Examples: Distance between two cities for a longer example using data types.
Essential Lambda Extras
When Lambda() first came out, it was a right PITA to work with because of the limited space to enter a formula in the Define Name line. Check out Microsoft fixes a big hassle with Excel’s great Lambda() for a better way to work with Lambda().
Other Lambda() extensions are now available.
Optional parameters are now possible with an accompanying IsOmitted() test see New Lambda options and helper functions for Excel
Also some Lambda helper functions to apply a Lambda() function to an array: ByRow, ByCol, Map, Reduce, Scan, MakeArray. See Understanding the new Lambda helper functions
Who gets it?
Lambda() is available in Excel 365 for Windows and Mac. Also Excel in a browser.
Microsoft fixes a big hassle with Excel’s great Lambda()
Understanding the new Lambda helper functions
New Lambda options and helper functions for Excel
Easier Excel metric conversion and more with Lambda()
LET() assigns names to calculations in Excel
The big difference between what Excel shows and what Excel knows
Excel is getting better Conditional Formatting with much needed improvements
