The power of LAMBDA

One common complaint about formulas in Excel is that they’re hard to read, especially if you’re not the author. Formulas in Excel also lack in reusability: a formula calculates its result there and then, for one cell. You can extend the formula across multiple cells or copy them elsewhere, but an Excel modeler must take great care to apply any change in the calculation logic to all formulas consistently.

Common Solutions

To make a formula more readable, a typical solution is to break it up into sub steps, so intermediate steps are not as lengthy and can be labelled individually. To solve the maintainability issue, more drastic solutions are sometimes employed; from generated Excel sheets to VBA-scripts that add formulas in all the right places.

Meet LAMBDA

What if I told you there was a native way to solve both in Excel? Meet LAMBDA(). With LAMBDA you can specify a formula, but the formula isn’t resolved to a value immediately. Instead, you enter the formula wrapped in a LAMBDA-function, place it in a defined name, and call upon the calculation from different places in your spreadsheet.

Example: Hypotenuse

For example, you could enter a Pythagorean formula to calculate the longest side of a right-angled triangle in a defined name **hptn** (for ‘hypotenuse’, using lowercase to distinguish it from native Excel functions):

=LAMBDA(a, b, SQRT(POWER(a, 2) + POWER(b, 2)))

As you can see, the actual calculation takes place in the third argument of the LAMBDA-function, the first two specify the parameters required by this formula (it should be noted that the number of parameters is flexible).

Next, you can calculate the hypotenuse (length of the slope) anywhere in your workbook using:

Neat right?!

Why use LAMBDA-functions?

You might argue that LAMBDA-functions hide functionality, but by choosing careful names you can really provide clarity to formulas in your model. Let’s be honest, =getLastCell(A:A) just reads a bit easier than =LOOKUP(2,1/(A:A<>””),A:A)

LAMBDA functions can be called upon in any formula, including its own definition! Let’s say Excel didn’t have the FACT-function to calculate a factorial, then you could actually implement a recursive version of it yourself! Just enter the following defined name:

To be crystal clear: a factorial is a notoriously bad example to implement recursively, but it provides a very clear example of the power of LAMBDA’s. A common, real-life example is to have a function that does repeated text substitution, but its implementation is somewhat too extensive for our purposes here.