Top 12 Excel Combinations: 5. SCAN LAMBDA

For 2024, I thought I would count down a very subjective “Top 12” of Excel function combinations. And this month’s offering makes it very clear that this is a highly subjective list. If you don’t like this month’s suggestion, that’s fine; life would be boring if we were all in agreement.

Continuing our Top 10 countdown, we now get to the Top 5. The 5th of 12, SCAN LAMBDA, is another new combination only available in Excel 365 – and again uses LAMBDA. Therefore, apologies for the partial repetition this month, but I have to explain LAMBDA once more for those who are not regular readers.

As always, I need to introduce the two functions individually, but this time, I shall be doing it in reverse order.

Only available in Excel 365, LAMBDA “completes” Excel and allows you to define your own custom functions using Excel’s formula language. Moreover, one function can call another (including itself!), so there is no limit to the power you can deploy with a single function call. Additionally, you can use other functions (which we’ll get to shortly) to apply these functions in more versatile ways. This will be essential to building up our financial model later in the book.

One of the more challenging parts of working with formulae in Excel is that you often get fairly complex formulae that are re-used numerous times through the sheet (often by just copying / pasting). This can make it hard for others to read and understand what’s going on, put you more at risk of errors, and make it harder to find and fix the errors. With LAMBDA, you have reusability and composability. You can create libraries for any pieces of logic you plan to use multiple times, which offers convenience and reduces the risk of errors.

Some people are put off by LAMBDA and “lambda”. A lambda function (sometimes referred to as a “Small Anonymous Function”) is IT parlance for a self-contained block of functionality that is transferrable / portable throughout your code.

Think of Excel formulae as your “code”. You can create a user-defined function (a lambda function) using the Excel function LAMBDA to define it. Clear as mud, yes?

So how does it work? The syntax of LAMBDA is perhaps not the most informative:

That’s, er, great. Its syntax suffers because it is so flexible. Perhaps a run-through might be best. There are three key pieces of LAMBDA to understand:

1. LAMBDA function components
2. Naming a lambda
3. Calling a lambda function

Let’s go through these steps.

I will begin with a simple example. Consider the following formula:

=LAMBDA(x, x+1)

where we have x as the argument, which you may pass in when calling the LAMBDA, and x+1 is the logic / operation to be performed. For example, if you were to call this lambda function and define x as equal to five [5], then Excel would calculate

5 + 1 = 6

Except it wouldn’t. If you tried this, you would get #CALC!

Now that you have done this, your first new lambda function may be called in just the same way as every other Excel function is cited, eg,

=MyLambda(5)

which would equal six [6] and not #CALC! as before.

You DON’T have to do it this way though if you don’t want to. You may call a lambda without naming it, and this is the second way to fix the #VALUE! error we had earlier. If we hadn’t named this marvellous calculation, and simply authored it in the grid as we had first attempted, we could call it by simply typing:

=LAMBDA(x, x+1)(5)

As you become more experienced and your expertise grows, you will realise that some transformations may be cumbersome using the functions you already know from “legacy Excel”. For instance, you might find the following awkward:

• combining arrays
• shaping arrays
• resizing arrays.

This where a set of “lambda helper functions” come into play. These allow you to write lambdas more easily. Like MAP last month, one such helper function is SCAN…

This function scans an array by applying a LAMBDA to each value and returns an array made up of each intermediate value (so it is used in combination with LAMBDA by its very definition). The syntax is as follows:

SCAN([initial_value], array, lambda)

where:

• initial_value: this is an optional argument and represents the starting value for the accumulator, ie, the “running total” prompted by the lambda expression
• array: this is a required value and represents the array to be scanned
• lambda: this is also a required value and represents a LAMBDA function called to scan the array, that consists of two parameters:
• accumulator: the returned (aggregated) value from LAMBDA
• value: a value from array.

As a simple example, let’s consider a common problem when working with structured references, i.e. Excel Tables (CTRL + T). Imagine I have the following sales for the first six [6] months of the year:

This is a horrible formula, consisting partly of Excel references and partly of Excel Table’s Structured References:

=N(C1) + [@Sales]

It mixes Excel cell referencing (cell C1, because you cannot refer to a value for a different record simply in an Excel Table), structured referencing ([@Sales]) and the N function, in order to treat the numerical value of text as zero [0] and therefore avoid #VALUE! errors when adding amounts together.

It seems to work if values are added:

This is where SCAN comes to the rescue. Assuming the Table is also called Sales (not just the field in column B), we can create the formula

=SCAN(0, Sales[Sales], LAMBDA(accumulator, value, accumulator + value))

SCAN “scans” the array (i.e. the Excel Table Sales) by applying a LAMBDA to each value. It then returns an array of results corresponding to the accumulator value returned by the LAMBDA. As stated above, SCAN takes two parameters:

• accumulator: the initial value returned by SCAN and each LAMBDA call
• value: a value from the supplied array.

As above, the initial_value is zero [0] so that the running total calculates correctly.

Do note this formula cannot be part of the Table, as dynamic array calculations cannot spill in a (CTRL + T) Table.

SCAN LAMBDA therefore provides running aggregations (the ‘+’ operator may be substituted for other operators such as multiplication, division and subtraction, plus more complex expressions too) – something that is more awkward to do using dynamic arrays or structured referencing otherwise.

Similar to last month’s comment, if you don’t have dynamic arrays or Excel 365, it is still a good idea to consign this combination to the memory banks. Excel is evolving and dynamic arrays are likely to become more and more pertinent. Knowing tricks such as SCAN LAMBDA will never hurt you and more likely, make you the dynamic array guru.

The top 5 continues next month.