Introduction
LAMBDA functions, now supported in both Google Sheets and Excel, are a game-changer. As someone who loves programming as a hobby and has used it to extend the usefulness of spreadsheet software, I’ve always been frustrated by the limitations and drawbacks of scripting in these two ubiquitous applications. But with the introduction of LAMBDA functions, a whole new world of possibilities has opened up.
The Trouble with Scripting
Creating custom functions and functionality in Google Sheets with Apps Script (JavaScript) or in Excel with Visual Basic has always had its challenges:
- Security warnings in Excel due to VBA programming make the sheets harder to use and can dull users’ security precautions.
- Advanced functionality in Google Sheets Apps Script can sometimes unpredictably timeout and fail with useless “Loading…” messages.
- Having scripts shown and edited in a separate window makes them inherently less accessible to those who aren’t familiar with programming. The transparency of spreadsheets and consequent feeling of ownership by your users is one of the big benefits of using spreadsheets over custom applications in the first-place.
- Creating programs in a scripting language becomes a maintenance burden. Now you are not just designing a spreadsheet, but you are also maintaining a codebase.
These issues have led me, time and time again, to ultimately redesign my spreadsheets to be script-less, sacrificing ease-of-use features and capabilities in the process. But the arrival of LAMBDA functions has changed everything.
The Power of LAMBDA Functions
At first glance, the examples of LAMBDA functions provided in the Google Sheets and Excel documentation may seem underwhelming. The Google Sheets documentation shows the following examples for LAMBDA functions:
=LAMBDA(Temp, (5/9)*(Temp-32))(85)The Microsoft Office support website is about as useful, with similarly trivial examples of LAMBDA function usage. When I first saw this my thought was “ok… so I can make my cell formulas longer…”
But dig a little deeper, and you’ll discover that LAMBDA functions allow you to:
- Create named functions to simplify complex cell formulas
- Define functions with named arguments for improved readability
- Pass functions as arguments to enable advanced functionality like recursion
LAMBDA Magic Wand 1: LET() Variable Naming
Part of the “LAMBDA family”, the LET function is a game-changer when it comes to making complex formulas more readable. By allowing you to name intermediate calculations, LET eliminates the need to pore over data or outdated documentation to understand a formula’s intent. Take a look at the following example:
=SUM(B3:B6)+SUMPRODUCT(Other!B2:B5,B12:B15)+IF(MAX(FILTER(B20:B23,C20:C23="risk"))*0.9>SUM(FILTER(B20:B23,C20:C23="risk"))*0.3,MAX(FILTER(B20:B23,C20:C23="risk")),SUM(FILTER(B20:B23,C20:C23="risk"))*0.3)
This isn’t an uncommon cell formula for me. Filters, sums, ranges, comparisons, all inscrutable unless you recently crafted the formula. I know what you might be thinking: there are ways to make this more readable. “Helper” cells to perform and document intermediate calculations and named ranges are the two most common methods, but there are many situations where this is not acceptable. Helper cells are my typical go-to for self-documenting sheets, but there are many situations where it causes a lot of sheet bloat, or where there just isn’t room in a dynamic row system to perform all the intermediate calculations, and where a more compact expression is greatly desired. Named ranges are my next method, but they bind the cell formula to absolute data references, and if you inherently want it to reference new data when copied or dragged, you either can’t do it, or you have to craft an even more elaborate cell formula.
Now, what if we used the LET function? The format for the LET function is =LET(name1,expression1,name2,expression2,final_expression). The cool part is that the name1, name2, etc are “locally scoped“: you are creating those variable names within the scope of the LET function, and the names can be used in subsequent statements without cluttering up the “global” scope of named ranges in the sheet. Let’s look at the previous formula, but this time using the LET function with a focus on readability:
=LET(
overhead_costs,B3:B6,
markups,Other!B2:B5,
category_costs,B12:B15,
marked_up_category_costs,SUMPRODUCT(markups,category_costs),
all_risks,FILTER(B20:B23,C20:C23="risk"),
big_risk,MAX(all_risks),
ave_risk_probability,0.3,
ave_risk,SUM(all_risks)*ave_risk_probability,
bid_risk,IF(big_risk*0.9>ave_risk,big_risk,ave_risk),
bid_price,overhead_costs+marked_up_category_costs++bid_risk,
bid_price)
With the LET function, the intent of the formula and step-by-step logic is inherently documented, making it much easier to understand and maintain. The cell references can be absolute if referencing standardized data, or they can be relative, if you want a draggable function. Also, those cell references are just standard cell formula expressions, so they can be cell references, function calls, or anything else.
LAMBDA Magic Wand 2: Passing Functions as Arguments
Another bit of LAMBDA magic comes when you name LAMBDA functions using LET. Check out this example of a recursive factorial function:
=LET(
factorial, LAMBDA(self, depth,
IF(depth=1,1,depth*self(self,depth-1))
),
factorial(factorial,100))By passing the lambda function a reference to itself, we can enable recursive functionality right within a cell formula. Mind-blowing! For those familiar with programming, this unlocks an amazing depth of functionality that just wasn’t possible with spreadsheets cell expressions in the past.
Real-World Applications
With this LAMBDA toolkit, the possibilities are endless. Here are just a few examples of what I’ve been able to create:
- A function that can parse textual formulas
- An iterative solver that can take an equation function as a LAMBDA function and solve for its roots
- Psychrometric functions to solve for properties of moist air (requiring about 20 named functions and iterative solvers)
These are things I had previously attempted with Apps Scripts but had always run into usability or performance issues. LAMBDA functions have opened up a whole new world of possibilities.
Conclusion
The introduction of LAMBDA functions in Google Sheets and Excel is a game-changer for anyone looking to extend the capabilities of these powerful spreadsheet applications. By enabling advanced functionality like variable naming, passing functions as arguments, and recursion, all within the confines of a cell formula, LAMBDA functions eliminate the need for clunky and problematic scripting. The possibilities are truly endless, and I can’t wait to see what other innovative solutions the spreadsheet community comes up with using this incredible tool.
Leave a Reply