Announcing LAMBDA Helper Functions

Silver Contributor

Aug 07, 2021

I agree that both planning meaningful names and knowing their significance at a later date can be challenging. As far as I can see, though, there are no better alternatives. If one uses deeply nested formula one is worse off; then one only has the partial formula. Using LET, there is also a clue as to the significance of the calculation within the name which is a bonus.

The sheet or workbook scoped names have the opportunity to insert a comment. I try to cover:

1. the business significance of the named object

2. how it is calculated

3. why it was calculated and its intended use

For a named Lambda function, I keep the description short and focus on describing the function signature in terms of its parameters.

Yes, as Dan Bricklin suggested, it can be tedious, but I believe it is worth the effort if the workbook is important. As an aside, I enclose a LET formula with a multitude of names. To find out what is going on one just has to work through line by line. It is an effort but the formula is doing many things.

= MAP(ValuationDate,
      LAMBDA(vDate,
         LET(
  \1, "Select Investment",
           InvestmentAmounts, XLOOKUP(@Investment, Investment, InvestmentTable),
  \2, "Set up filter",
           nonBlank?, ISNUMBER(InvestmentAmounts),
           cashTransaction?, LEFT(EntryType, 4)="Cash",
           withinDate?, TransactionDate<vDate,
           valuationDate?, TransactionDate=vDate,
           combined?, nonBlank? * (cashTransaction?*withinDate? + NOT(cashTransaction?)*valuationDate?),
  \3, "Apply filter",
           filteredDates, FILTER(TransactionDate, combined?),
           InvestmentFiltered, FILTER(InvestmentAmounts, combined?),
  \4, "Evaluate IRR",
           XIRR(InvestmentFiltered, filteredDates)
  ) ) )

Blog Post

Announcing LAMBDA Helper Functions: Lambdas as arguments and more