Get a free download with 30 sample LAMBDAs here.
Background
In the book I’ve described the benefits of using Name Manager formulas to package logic into short names that can be used as arguments in worksheet formulas. For example, these are particularly useful for returning Boolean conditions from table-formatted data. Where Conditional Formatting and Data Validation formulas do not allow table notation, Name Manager steps in as translator, allowing you to maintain table structure discipline with transparency.
One limitation of Name Manager formulas is that they can’t take arguments. You can work around that, to a degree, by referencing input cells. This approach has the benefit of formula transparency, with the drawback that the argument location cannot be changed. In these examples, input names are used to refer to adjacent or fixed cells where arguments are fed to the named output formulas.
Table 1: Name Manager formulas using cell arguments
Another approach is the VBA udf (user-defined function). VBA allows the custom function to be named and referenced just like built-in functions, with arguments that can be defined as certain data types, and as optional or required. The arguments are supplied by the user either directly or by cell reference. The drawback is that, as VBA, the function requires the host file to be macro-enabled, which for some users is opaque or does not work at all.
As an answer to these lesser alternatives, the LAMBDA function extends Name Manager formulas to accept arguments. This combines the best features of Name Manager transparency with VBA flexibility. LAMBDA is unique in that it is intended for use solely within Name Manager. It spotlights and enhances the value of Name Manager as a custom formula repository.
LAMBDA
LAMBDA syntax begins with up to 253 (what!?!) optional arguments, followed by the formula to process those arguments. While no argument is required, it wouldn’t make sense to use LAMBDA without at least one argument, and most cases will likely use just one or a few. The arguments can be letters or words, and while there is no error raised when using an existing name, good practice is to keep argument names separate from other names to avoid confusion.
Function help gives step-by-step instructions and examples, summarized here.
Test the LAMBDA function in the worksheet
Name the LAMBDA function in Name Manager
Use the named function in the worksheet
Test the LAMBDA function
While LAMBDA is intended for use only in Name Manager, it works in the worksheet as well, with a small work-around. Entering a correctly formatted LAMBDA in a cell raises a #CALC! error, unless it is followed by a set of parameters in parentheses. This provides an easy way to validate the LAMBDA before setting up Name Manager. Here are two examples for the area of a circle and of a rectangle.
Table 2: LAMBDA examples in the worksheet
Using single letter parameters is the most compact, but full words or abbreviations may be preferred if interpretation is a concern.
Name the LAMBDA function
Once the LAMBDA function is validated, you need to give it a name in Name Manager. First, edit the cell and copy the LAMBDA part (without the ending parameter list). Open Name Manager (Ctrl+F3) and click New... (Alt+N) to create a new name. Naming convention is the same for all names, i.e. cannot be an existing name or reference, and syntax as defined in Name Manager help. Tab twice to Comment. Comment is not required, but it’s good practice to describe the function and parameters. Tab again to Refers to, and paste the LAMBDA copied from the cell. If editing is needed, hit F2 to enter edit mode.
LAMBDA in Name Manager
Use the named LAMBDA function
Once Name Manager is closed, test the new function in a cell, noting the parameter prompt.
Enter: =AreaCircle(2) Result: 12.56637
Usage
As with any new functionality, test LAMBDA for yourself to fully understand both how and why to use it. Once you master workbook-level usage, then how will you apply it? Before you jump into writing formulas, think about what a sustainable model looks like, including error handling, troubleshooting, documentation, maintenance, and sharing. Don’t use it if there’s a simpler solution for the objective at hand.
LAMBDA formulas must be copied if they are to be used in new workbooks. One way to do this is to save them in a template file in your startup folder (XLSTART). Then, when you right-click on a sheet tab to insert a new sheet, you can pick the “Lambda” template.
Recursive LAMBDA?
After viewing several recursive LAMBDA tutorials, I was disappointed in the examples presented, which focused on correction of spelling errors. The presenters demonstrated looping through a column of incorrect words or phrases, looking them up in a table, and replacing them with the correct ones. This was effective in demonstrating bad practice as much as the functionality. I thought, “why would you pick that?”, and quickly noted three reasons not to:
Spelling errors in a spreadsheet form are probably from bad form design. Address the root cause.
If I use formulas to correct spelling, then I need to keep the bad spelling, unless I convert the formula to values. Either way is inelegant.
Can you spell “AutoCorrect”?
My point is not to pick on anyone, but to emphasize design principles. Many trainers struggle to find good examples, and may end up with whatever gets them through the presentation versus a well thought out use case. This reveals a bias towards how over why.
I tried to come up with a more relevant recursive example and failed, but the quest got me somewhere unexpected and is worth sharing as a story of inspiration and discovery. The best I could think of was to determine if a given number is prime. It’s not really very relevant, but at least it’s something for which a formula, if one can be found, would be the most elegant solution.
To identify primes I would need to factor the number. I struggled with the recursive logic to find factors, soon resorting to brute force visualization. I built a 100-by-100 matrix, ordinals in column A and row 1, and the formula =IF($A2>=B$1,MOD($A2,B$1),"") to fill the grid. Then I added a column off to the right to count the zeroes in each row, =COUNTIF(B2:CW2,0), etc. This gave me the number of factors of that row’s number, and if there are exactly two factors, it’s prime. This helped me visualize the direction I needed to go, but I was no closer to a LAMBDA solution.
So once I’d given myself the requisite chance, I turned to the internet to search for something like “identify prime numbers in Excel”. After some filtering I came upon an ingenious solution in a post by jimishravat2802 at geeksforgeeks.org (where else?). The logic is broken down pretty well there, but it still took me a while to catch it, so I’ll explain it here.
=IF(F6=2,"Prime",IF(AND(MOD(F6,ROW(OFFSET($C$2,,,ROUNDUP(SQRT(F6),0)-1)))<>0),"Prime","Not Prime"))
The method is not to use recursive logic but rather dynamic array logic. For any input number, the possible factors that would flag it as non-prime are between two and the square root of the number. By constructing a dynamic array of those factor candidates, MOD of the input number against that array will result in another array of MOD results. If that result array contains no zeroes, then there are no factors, and we have a prime. The inequality turns zeroes to FALSE and non-zeroes to TRUE, then AND is used to reduce the array to a singular Boolean, "is prime" or not.
So how to generate the dynamic array of possible factors? The author uses OFFSET to create a range of row numbers from $C$2 (“reference”) to the rounded square root of the input number (“height”). This OFFSET range has no effect on the sheet, $C$2 is just a cell from which to get row number 2.
I was quite impressed with this solution. As I recommend to anyone with enough curiosity, I dissected the formula so that I could see what happens at each step. I didn’t think I needed to change anything, and created a LAMBDA in Name Manager.
When I copied the name to a new workbook, Name Manager prefixed the sheet name to the cell reference. This doesn’t affect results, but I decided to find a more appropriate array generator that didn’t require cell reference. This is where the dissected parts of the original formula come in handy. With the factor array in one column,
=ROW(OFFSET($B$2,,,ROUNDUP(SQRT(x),0)-1))
I put another one next to it for comparison,
=SEQUENCE(ROUNDUP(SQRT(x),0)-1,,2)
feeding them several inputs until sure that the two arrays were identical.
I also added error prevention,
IF(OR(number<2,MOD(number,1)),FALSE,....
to select only natural numbers greater than 1, and converted the output from text to Boolean, making it ready to use in Conditional Formatting. Here's the final version:
IsPrime =LAMBDA(number,IF(OR(number<2,MOD(number,1)),FALSE,
IFERROR(OR(number=2,AND(MOD(number,
SEQUENCE(ROUNDUP(SQRT(number),0)-1,,2))<>0)),FALSE)))
Here is an example number table with primes highlighted by Conditional Formatting:
To practice LAMBDAs, I went back to my cheat sheet from GED tutoring to get the basic geometry and algebra formulas. This sampling gives a decent preview of possibilities. Some revealed the need for error handling, like quadratics and slope. To aid learning I made a single reference sheet with the 30 formulas in Name Manager.
I then saved the file as a template, LAMBDA.xltx, in XLSTART. This makes it accessible to insert in any file by right-click on a sheet tab. The sheet can then be hidden or deleted, and the functions remain in Name Manager.
To include the LAMBDA functions in new workbooks, I saved it again as XLSTART/Book.xltx, with a blank Sheet1 and the LAMBDA reference sheet hidden. To download the zip file with both templates, click here, or use this method to create your own.
LAMBDA.xltx (and Book.xltx)
Conclusion
LAMBDA functions are a nice nod to Name Manager fans, and a worthy alternative to VBA udfs. As with any new feature, this one should be in the developer’s toolkit, but take care not to proliferate frivolous formulas. For example, the functions in LAMBDA.xltx above are relevant in a math class, so it could be named Math.xltx. As new LAMBDA opportunities appear, they can be categorized and saved as separate templates. Share with caution to keep design simple and robust, i.e. elegant.
If you find a good use for recursive LAMBDA, let me know!
Comentarios