Excel Tips and Tricks #449 - Let LET simplify formulas

Why LET matters

Back in 2018 the ICAEW Excel Community released the third edition of its thought leadership publication “20 principles for good spreadsheet practice”. Among the twenty principles were the following:

13. Keep formulae as short and simple as practicable

15. Perform a calculation once and then refer back to that calculation

This proved to be very prescient as in 2020 Microsoft announced the LET function in Excel designed to address these points!

The basic idea of the LET function is that we separate our formula into steps, and give each step a name along the way.

Let’s dive straight into the LET function in its simplest form - using just one step.

One step

The syntax in the one step case is as follows:

=LET( name1, value1, calculation )

The following example demonstrates this:

=LET( kpi, 10, kpi * kpi )

Here, we assign the value of 10 to the name “kpi” and then multiply “kpi” by itself. The resulting value of this formula will be 100.

This demonstrates the point that once we have named a step we can refer back to it multiple times in subsequent steps (as shown here, kpi * kpi).

While in this example, there is no benefit to replacing 10 with “kpi”, if 10 was in fact a calculation in its own right - say, (3x4)-2 - then using LET starts to make sense; =LET( kpi, (3x4)-2, kpi * kpi ) is much easier to read than =((3x4)-2)*((3x4)-2). And Excel finds it easier to calculate too.

Where complex calculations are involved this also means that we do not need to recalculate earlier steps multiple times, as it has already been calculated and “saved” to the name.

In assigning a name, we can choose any name we want provided it doesn’t conflict with an existing name or function e.g. SUM, it doesn’t include spaces, and, curiously, it has to start with a letter. Where it is helpful to have more descriptive names we can use any sensible workaround such as using capitalisation or underscores e.g. instead of “total score” we have “totalScore” or “total_score”.

Once a name is defined, Excel provides a helping hand by suggesting previous names as you type:

Two steps

Let’s build up LET to something slightly more sophisticated and introduce an intermediate step between the first name and the calculation. The formula syntax will be as follows:

=LET( name1, value1, name2, value2, calculation )

Here’s an example using two names:

=LET( kpi_1, 10, kpi_2, kpi_1 + 5, kpi_1 * kpi_2)

Hang on… (I hear you say).. I thought LET was supposed to increase readability?

How about we rewrite our LET function over multiple lines:

name1 = value1
name2 = value2
calculation

So for our example we have:

kpi_1 = 10
kpi_2 = kpi_1 + 5
calculation = kpi_1 * kpi_2

In this case kpi_1 = 10, kpi_2 = 15 so the result of the formula, kpi_1 * kpi_2, will be 150.

As formulas build complexity, this is conceptually much easier to follow and highlights the idea that intermediate name/value pairs can refer to previously defined names (as shown here, kpi_2 is based on kpi_1).

(As an aside, Excel allows you to write formulas over multiple lines in a cell - simply resize the formula bar so that you have more space on screen,. then line breaks can be inserted into your formula by pressing Alt+Enter - very handy for complex formulas like LET!)

Multiple steps

To unleash the full power of LET we can include as many intermediate steps as required:

name1 = value1
name2 = value2
name3 = value3
name4 = value4
…
(further name/value pairs)
...
calculation

The formula syntax will remain the same, with the calculation always being the last argument:

=LET( name1, value1, name2, value2, name3, value3 [,... nameX,valueX], calculation )

And that’s it! The LET formula is as simple as it is powerful. Let’s move on to a couple of real world examples.

Example 1 - calculating prepayments

Let’s say you are tasked with producing a monthly balance sheet and profit and loss for a client.

There are P&L timing differences e.g. prepayments that need to be calculated and adjusted for each month by way of a journal posting.

The attached worksheet (first tab) utilises the LET formula to calculate the outstanding prepayments held on the balance sheet at each month end.

In this example we have carefully separated our inputs (left hand side) from our outputs (right hand side) to give a clear structure to our workings.

By using the LET formula we have avoided the need to clutter our spreadsheet with intermediate “workings” columns in order to produce the desired result.

Example 2 - keyword search

In this example we are tasked with categorising a list of transactions based on certain keywords. For example you may wish to sort a list of purchase ledger transactions by the supplier to which they relate.

The catch in this scenario is that the supplier name (or keyword) is buried in the narrative description for each entry and cannot be easily extracted.

The attached worksheet (second tab) utilises the LET formula to dynamically extract this keyword and categorise transactions based on a keyword list.

Conclusion - Excel formulas as code

For years Excel users wishing to handle more complex scenarios have had the choice between writing near-unreadable formulas or switching to another solution altogether such as VBA code.

LET is one of the first steps in bridging the gap between general every day Excel users and more advanced developer users. It is empowering those without a technical programming background to write more structured and powerful formulas, all within the familiar environment of the Excel grid.

For those wishing to take this a step further, LET can be used in conjunction with the recently released LAMBDA function so that users can encapsulate their formula as a user defined function and reuse this across the entire workbook.

Author

Tom Edmunds works as Financial Controller at mgr and has spent much of the last 15 years tinkering with Excel and building spreadsheet solutions for the firm and its clients.