Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
|
In this blog
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!
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:
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!
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.