Overview
The Excel LET function is used to assign calculation results to names. This can be useful for storing intermediate calculations and values by defining names inside a formula. Unlike the Name Manager, these names only apply within the scope of the LET function.
Syntax
=LET(name1, name_value1, [name2, name_value2], …, calculation)
Arguments
name1 – The first name to assign.
value1 – The value assigned to name1.
name2 – [optional] The second name to assign.
value2 – [optional] The value assigned to name2.
calculation – A calculation to be performed on the name/value pairs.
Version
Excel 2021
Purpose
The LET function allows you assign names to values or calculations. There are two main reasons why you might use the LET function:
- To improve workbook performance. If your formula contains the same expression multiple times, Excel can calculate that expression once and assign the result to a name. The LET function then uses the result assigned to the name instead of performing the calculation each time the expression appears.
- For easy reading and composition. Using an assigned name throughout a formula allows for the user to understand the logic of the formula, without having to focus on syntax errors or cell references. Using a name removes all the guesswork from formula comprehension.
=LET(name1, value1, calculation)
Examples
Let’s first look at two simple examples of LET, to better understand how the function works.
Using a single name/value pair, we can use LET to evaluate the following:
The formula we have in cell D5 (and apply to the cells below) is:
=LET(x, B5, x*2)
Here we are assigning the value in cell B5 to the name ‘x’, and then multiplying ‘x’ by 2 in the calculation of the LET function. When x = 1, the result is 2. When x = 2, the result is 4.
For clarity, we’ll break down the LET function into it’s components:
Going a step further and using two name/value pairs, we get:
The formula we have in cell E5 is:
=LET(x, B5, y, C5, x*y)
Now we are assigning two different names based on two different values. We have the name ‘x’ assigned to the value in cell B5 and the name ‘y’ assigned to the value in cell C5. Our calculation in the LET function now multiplies ‘x’ and ‘y’, based on their cell values that can be seen above.
In both of these cases, the LET function does not result in any performance improvements or in any composition / readability improvements as the calculation is very simple. Let’s take a look at a more complex example, demonstrating how both performance and readability improvements can result from use of the LET function.
In this example, we are using the IF function to check and see if a formula result is less than zero. If it is, we don’t want to show the negative number, we just want the value to be zero. Otherwise, we want the formula to calculate as it would normally. Let’s first take a look at what this would look like without the LET function:
The formula we have in cell F5 is:
=IF(B5+C5–D5 < 0, 0, B5+C5–D5) // IF without LET
In the formula above, if the condition evaluates to TRUE, Excel must calculate what B5+C5-D5 results in twice. This is a performance gap that we can improve on using the LET function, as follows:
The formula we have in cell G5 is:
Looking at the IF function in isolation, it’s very easy to read and it’s immune to any syntax errors as ‘val’ has already been defined. Additionally, Excel now only has to calculate the ‘val’ once, which in a large workbook with thousands of calculated fields, will save a significant amount of time and processing power.
Notes
- At least one name/value pair must be defined in the LET function
- LET supports up to 126 name/value pairs
Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
