Introduction to Lambda

Introduction

LAMBDA is a relatively new function (or feature!) to Microsoft Excel (introduced in 2021) that now allows users, for the first time ever (it’s been over 30 years in the making), to create their own user-friendly custom functions (including, in combination with the new LET function, recursive calculations).

For those who don’t know, in computer programming, the term ‘Lambda’, more generally, refers to what is known as an “anonymous function” - something you can use you to write functions quickly without naming them, or something you can use to embed small functions within other functions.

The concept of Lambdas in functional programming is not really new, (it’s been around for decades), but for spreadsheet users (Engineers, Scientists, Financiers, and so on), it is new (!), and why, when it was introduced into the Excel ecosystem (not that long ago, from the time of writing), it had, for very good reason, stirred up a lot of interest and excitement!

The purpose of this guide is to serve as a curation of what I think to be really useful and helpful Lambdas (those I have found from, from traversing across the world wide web). If you follow the ‘Mr Excel lambda’ forum, one of the users there is really pushing the limits of what one can do with Lambdas (I have to think really, really hard to understand the mechanics of the calculations), so much of the credit to many of the functions in this guide actually goes to him (or her?). I have served as mostly the orgainser.

Where applicable, I have included a reference to the relevant website link at the top each page. I have also done a first pass review, and tested each function, to make sure they work as expected, (and that there is nothing strange in any of them that shouldn’t be there)!

Library

There is A LOT MORE you can now do with lambdas - things that were were either impossible before (e.g. recursions), or which took several hours, or even days, to do (such as array transformations).

So they are very powerful! - and can be real big time-savers!

As you will likely quickly realise, there are many ways you can write a Lambda to perform the same calculation or task. Some may be kinder to Excel’s internal calculation engine than others. However, understanding M.S. Excel’s calculation engine and finely optimising processor calculations is not really what this guide is about. With improvements being made all the time, even the most complex of Lambdas should only take a few hundred milliseconds to complete - so it’s not something you should worry too much about. (If you do find them to be too slow, perhaps consider again whether you need to use expensive Recursion or Matrix operations.)

And also if you prefer not to have real-time calculations or transformation updates in your spreadsheets, then, of course, you can always use E.T.L.s, and benefit from what “Power Query”, or the new “Office Scripts” (Java Script) - two other relatively new features - have to offer.

For this guide, I have tried to group the functions I found or created myself, into a few logical categories. So far, by:

• Standard Excel functions
• Combinatronic functions
• Data & Time functions
• Array Transformation functions
• Array ‘By Element’ functions
• Descriptive Statistic & Basic Maths functions
• Finance & Accounting functions

…though, I am sure, with time, as more get added, the taxonomy will likely change and evolve.

Rest of this Guide

So, if you like what you’ve read so far, and would like to learn and find out more about these delightful - and efficiency boosting! - Lambdas, then please read on!