Using the LET Function in Excel
This LET Function in Excel tutorial is suitable for users of Excel for Microsoft 365.
OBJECTIVE
Use the new LET function in Excel to define a calculation once and use it multiple times in a single formula.
THE LET FUNCTION EXPLAINED
The new LET function is part of the Text group of functions in Excel. It allows you to assign names to calculate results inside a formula.
If you find yourself writing the same expression multiple times in a formula, LET basically allows you to name the expression. Then Excel will calculate the result using the name.
Not only is this a great time-saving function, but it also makes your formulas easier to read and understand, as you don’t have to remember what a specific cell reference or range refers to. It’s right there in the formula! In front of your eyes!
If you like the LET Function, you might also enjoy learning about Dynamic Array Functions in Excel.
Syntax
=LET (name1, value1, [name2/value2], …, calculation)
To use the LET function, you must define at least one name/value pair (variable), and LET supports up to 126 pairs.
BASIC EXAMPLES OF LET
Let’s start with a very basic example using one named variable:
In this calculation, I am declaring my variable name as ‘var’ and assigning a value of ‘10’ to the variable. I am then recalling the variable name and adding 5. So, the answer here is 15.
Let’s add a second variable:
This time I am defining two named variables and assigning values.
My first variable, ‘var’, has been assigned the value of 10. The second variable, ‘x’, has been assigned the value of 2.
So therefore, the result of the calculation var+x is 12.
Let’s add a third variable:
In this example, I am defining three named variables and assigning values.
My first variable, ‘var’, has been assigned the value of 10. My second variable, ‘x’, has been assigned the value of 2. My third variable of y has been assigned the value of 3.
The calculation contained in brackets (parenthesis) is calculated first.
So, var/x equates to 10/2 to give the result of 5.
5+y equates to 5+3 to give the result of 8.
PRACTICAL EXAMPLES OF LET
EXAMPLE 1
In this example, I am using the LET function to work out each employee’s total salary once a $2000 bonus has been applied.
The variable ‘bon’ has been assigned to the value ‘2000’. The formula then calculates the result of salary+bon.
EXAMPLE 2
In this example, I want to sum the totals sales for all the travel companies on the list and, if the total is above $300,000, apply a 10% sales tax. If not, then apply a 5% sales tax.
This can be accomplished by combining the SUM and IF functions.
You’ll notice with this calculation that we have the calculation SUM(C4:C9) repeated three times in the formula.
If we use LET, we can assign the name ‘q1s’ to the SUM calculation and then use the name in the rest of the formula.
This is a much more efficient way of working.
Both formulas produce the same result. The sum of the sales equals $362,000.
As this is greater than $300,000 the result is 10% of $362,000, $36,200.
Once a name has been defined for a variable, it can be reused in other places within the workbook.
EXAMPLE 3
In this example, I am using the FILTER function to filter the table’s data on the left and return only results that match my FILTER criteria. For example, I only want to see the results for ‘West’ and ‘Maths’.
In the LET formula, I have named three variables and assigned values to them:
tab – cell range A2:C12 (the entire table)
bloc – cell range A2: A12 (the block column)
sub – cell range B2:B12 (the subject column)
Once the variables have been defined, I have then completed the rest of the FILTER formula using the named variables instead of cell references.
Whilst the LET formula is longer, it’s much easier to read and see which ranges relate to which named variables.
Both formulas produce the same result.
Once you have defined variables in a workbook, you can continue to reuse them in other calculations. The names will also appear in IntelliSense, much like a defined table or range name.
LET is such a versatile formula, and we’ve only touched on a few examples here. If you would like to learn more, please check out the following links:
Andrew Moss – Introducing the new LET function in Excel
ExcelExciting – The Excel LET function
Take a look at other free Excel tutorials from Simon Sez IT.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
