Joseph, I noticed in your new Budget Status sheet, you use the Lambda Function. I have been trying to figure out the use case for this new function, but always go back to an array functions as I just don’t see the point.
Can you offer your smarts on how to use it, and why you chose it.
I really am looking to be educated here.
thanks
Rich
It’s very new to me, have only been aware of it for a few weeks. My main reason for using it is that it also works in Excel, and I’ve been trying to keep parity on my templates between the platforms. Beyond that, I like the combination of MAP and LAMBDA, which allows you to define a bunch of ranges, and then just use terms in the formula in place of those ranges. That helped me convert Budget Plan from copied formulas to arrays, and made them much easier to read. Still learning how to best use it, but so far seems very powerful!
I haven’t tried using it yet, @richl, but, when I restarted making Excel template, I was impressed with the LET() function… which seems like the inspiration for Sheet’s LAMBDA().
How I imagine using LAMBDA() is to address the performance issues associated with needing to make the same large non-performant queries multiple times in the same formula. In pseudo code, the problem it solves looks something like this:
=ARRAYFORMULA(IF(ISBLANK(VLOOKUP(GIANT_ARRAY_A,...)),"No Data", VLOOKUP(GIANT_ARRAY_A,...)))
Do you ever run into this problem in your formulas?
Without LAMBDA() this structure always felt incredibly inefficient to me… but maybe Sheets is smart enough to cache results (e.g. VLOOKUP(GIANT_ARRAY_A,...)) and a change to LAMBDA() is not necessary.
Would love to keep this thread rolling in case someone comes up with better answers.
While checking if Sheets supports LET (It doesn’t) I found this option related to LAMBDA and another new function, SCAN. In my Account Register template, I use the following formula to calculate the running balance:
=ARRAYFORMULA(
IF(
T4:T = "",
"",
SUMIF(
ROW(T4:T), "<=" & ROW(T4:T), T4:T
)
)
)
Here’s another way of getting the same results using SCAN and LAMBDA, and it also works in Excel:
=SCAN(
0, T4:T,
LAMBDA(
runningTotal, currentValue,
IF(
currentValue = "",
"",
runningTotal + currentValue
)
)
)
I wish there was a way to tell if it’s more or less efficient!
Thanks for this, this is really helpful. I just tried the SCAN and Arrayformula on my transaction tracker, and the SCAN is much faster and also much easier to use.
-edit- I was playing around more and theMAP function in conjuction with LAMBDA should be very useful to lots of people.
=MAP(E2:E,S2:S, LAMBDA(amount,type, if(type="Expense",amount*2)))
In this case, E is my amount and S is my Type. This returned almost immediately on my 4000+ row Transactions sheet.
Great thread, everyone. Hadn’t seen these new functions. I love how you are already putting them to use.
I see how LAMBDA() and SCAN() are new and powerful but I don’t quite understand how MAP() unlocks features previously not possible with ARRAYFORMULA().
So far I’m mostly using it to make formulas easier to read. Seeing ‘amount’ instead of ‘INDIRECT($CA$12)’ makes formulas much easier to understand. I also realized when setting up a LAMBDA, you aren’t limited to just putting a range, you can have a formula that produces a range, which does what @randy was mentioning about not having to repeat VLOOKUP(GIANT_ARRAY_A,…).
i have been learning a lot about these the last few days. Definitely valuable especially when you add them as Named Formulas which allow for re-use.
I found it to run faster than ARRAYFORMULA, although with how Sheets loads, I don’t know if it’s accurate, but as was pointed out, it’s also easier to read and edit.
If you haven’t seen it, Ben Collins included some information on these function (including a note about performance) in his newsletter today.
I also watched this cool video on how to use Lambda in recursive functions. Pretty cool
Wow, a lot of power there! She does a good job of explaining the process, I’ll have to check out some of her other videos. The more I learn the build process the less I’ll have to rely on Google to find similar existing code to copy!
Yes she is crazy smart. Lots of videos for both sheets and excel.
