Let Excel

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.

LET began appearing in select releases of Excel in July 2020 and will continue to roll-out over the upcoming year. In essence, LET allows you to declare and store a variable inside a formula. Once you establish the variable with LET, you can then use that variable repeatedly in the same formula. The Excel file also can be made to read only by Read-only Recommended or restrict with modifying password. The Excel spreadsheet is locked with password. If the Excel sheet or cell is locked with protection password, you won't be able to make change to it unless.

Supported versions

Microsoft 365 (currently only available to some Office Insiders)*

The Excel LET function makes it easier to write certain complex formulas, by making it possible to declare and assign values to variables inside a formula. Purpose Assign variables inside formula. You save an Excel workbook to a location that does not have sufficient storage space. The connection to the Excel workbook is lost. There is a conflict with an antivirus software program. You save an Excel workbook that is shared. The 218-character path limitation is exceeded when you save an Excel workbook. Workarounds to save Excel workbooks.

*At the time of writing this article, this is a beta feature.

Syntax

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3…])

Arguments

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.
calculation_or_name2	One of the following: Mac cnet. · 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

Simple Usage

The LET function gives you the ability to add names like variables Which apple mac to buy. 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.

Let Excel Determine A Chart Type

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

Advanced Scenario

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:

Let Excel Automatically Calculate Cell

AVERAGEIF(Employee[Department],M2#,Employee[Performance Score])

Let Excellence Be Your Brand

The expression should be:

=IF(AVERAGEIF(Employee[Department],M2#,Employee[Performance Score])<=3,“Below Average”,IF(AVERAGEIF(Employee[Department],M2#,Employee[Performance Score])<=4,“Average”,“Above Average”))

This a relatively simple formula, but it’s hard to read as the average expression is used twice.

If you are wondering what M2# means, this is a dynamic array reference which refer the cells starting from M2 cell. Please see the Spill Range Operator article to learn more about this feature.

Here is how the formula would be using the LET function instead:

=LET(AvgScore,AVERAGEIF(Employee[Department],M2#,Employee[Performance Score]),IF(AvgScore<=3,”Below Average”,IF(AvgScore<=4,”Average”,”Above Average”)))

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.