COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
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.

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

Posted by Andy Brown on 24 October 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

First owl 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:

Formula without LET

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:

The formula using LET

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:

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:

MATCH / INDEX 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:

Formula using two variables

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).

This blog has 0 threads Add post