The LET function can define names within the scope of a formula. This essentially offers better readability and help with calculation performance. In this guide, we are going to show you how to use the LET function and go over some tips and error handling methods.
- Microsoft 365 (currently only available to some Office Insiders)*
|name1||The first in-formula name. The name must satisfy named range naming convention in Excel.|
|name_value1||The value of the name1. This can be a static value, a reference, or another formula.|
One of the following:
· If this is the last argument, it should be the calculation uses the names defined within the function.
· The second in-formula name.
|[name_value2, calculation_or_name3...]||Optional. The value of the name2. Otherwise, you can omit this value.|
LET Function Examples
The LET function gives you the ability to add names like variables in programming, and use them as much as you want in the formula.
The first arguments should be name and name_value pairs. You can define up to 126 pairs. The last argument should always be the calculation which uses the defined names.
For example, the following formula defines the name “x” and assigns 3 to it. The x will be used in the calculation defined in the last argument, x*2+1.
Here are more examples for the LET function. As shown in the next example, with this formula you can,
- add multiple names
- use a defined name in another name’s expression
- choose to use a reference as a name_value
- use the defined named range multiple times in the calculation
Let's say you want to label your departments based on employee’s average performance scores. An expression like below can be used for this.
- if the average score is below 3 then it is “below average”.
- if the average score is below 4 then it is “average”.
- the rest is “above average”.
If the average formula is like the following:
The expression should be:
This a relatively simple formula, but it's hard to read as the average expression is used twice.
Here is how the formula would be using the LET function instead:
The average expression is defined to a name called “AvgScore”. Once defined, you can use the name in the calculation, instead of the full formula.
Tips and Remarks
- Use the LET function if you need to use the same expression multiple times in a formula. If you define the expression by a name in the LET function, Excel calculates it only once.
- Note that you may not have access to this formula even if you are an Office Insider subscriber.
- When creating names using the LET function, you must follow the same rules for creating named ranges. Avoid using references as names, otherwise you will get an error message. For example, “x1” gives an error because it's a cell reference.