This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

You can use the new LET function in Excel to store interim formulae calculations Part two of a three-part series of blogs
The new Excel LET function lets you create variables to store the results of calculations, making formulae easier to read and quicker to calculate. The new Excel LET function lets you store your workings Using the LET function (this blog) Getting access to the LET function

You can use the new LET function in Excel to store interim formulae calculations

Part two of a three-part series of blogs

The new Excel LET function lets you create variables to store the results of calculations, making formulae easier to read and quicker to calculate.

The new Excel LET function lets you store your workings
Using the LET function (this blog)
Getting access to the LET function

Posted by Andy Brown on 24 October 2020

In this blog

A simple example - avoiding duplication of calculations
Example - making a formula easier to read (using multiple variables)
Conclusion and verdict

Using the LET function

This new Excel function allows you to store intermediate calculations in formulae, before returning the final result.

You can download the Excel workbook I used for the two case studies on this page here, should you want to follow along!

A simple example - avoiding duplication of calculations

Here at Wise Owl we've branched out into mail order goods, and we're excited to announce that we've received our first 4 orders:

The problem is: how much postage to charge?

Here are the rules we plan to follow:

Amount of sales	Postage
Up to £10	10% of the sales value
Otherwise	5% of the sales value

With postage calculations like this it's questionable whether this is a viable business, but that's not the point of this exercise. Here's what one of our junior owls has created:

This formula is messy, because it refers to the expression C3 * D3 - the total sales value - three times.

Not only is this formula hard to read, but it will also calculate slowly (Excel has to repeat the price-times-quantity calculation three times).

The obvious solution would be to add in an additional column giving the sales amount and reference that in our postage calculation, but to help to address the global shortage of spreadsheet columns we're trying to cut down on our column consumption.

What would make this clearer if we could calculate C3 * D3 once only, and store this result in a variable. Like this in fact:

I've pressed Alt + Enter to create the blank lines in the formula, to make it easier to read.

The syntax of the LET function is thus:

=LET(
VariableName,
Expression to calculate,
Value to return
)

Example - making a formula easier to read (using multiple variables)

Sometimes you might use variables just to make formulae easier to understand. Consider this example:

After choosing a product and quarter, cell C4 shows the sales for this combination. Cells B8:B11 have been given the range name Products, and cells C7:E7 have been given the range name Quarters.

Note that this might be better implemented using the new XLOOKUP function, but that's not the point of this blog!

You could make this easier to read by creating two variables as follows:

Variable	To hold	Our example would give
ChosenProduct	MATCH(C2,Products,0)	4 (ie the 4th row)
ChosenQuarter	MATCH(C3,Quarters,0)	2 (ie the 2nd quarter)

This would give the following final formula:

Whether you think this is an improvement on the original formula will depend on your personality!

Conclusion and verdict

Creating variables within the LET function brings 3 benefits:

Benefit	Notes
Avoiding repetition	Whenever you find yourself including the same calculation more than once in a single formula, it would be better stored in a variable.
Speed	It's quicker for a formula to evaluate a calculation once at the start, and store this value in a variable, than it is to repeat the same calculation two or more times.
Ease of reading	Using variables can make complicated formulae easier to read (particularly if you choose sensible variable names).

Of these 3 advantages, it seems to this owl that the last one is the best.

MMy own opinion is that while the LET function is useful, its day has gone a bit functions like IFERROR and XLOOKUP mean that there is less need to repeat calculations within a single formula these days).

Parts of this blog
The new Excel LET function lets you store your workings Using the LET function (this blog) Getting access to the LET function

Some other pages relevant to the above blogs include:

Training courses in Excel

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using the LET function

A simple example - avoiding duplication of calculations

Example - making a formula easier to read (using multiple variables)

Conclusion and verdict

Head office

London

Manchester