BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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
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:
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:
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).
- The new Excel LET function lets you store your workings
- Using the LET function (this blog)
- Getting access to the LET function