LET( ) Function in Excel

What is a LET function in Excel?

The LET function in excel is used to assign the name of the calculations of the result. This allows storing of intermediate results of some calculations, values, name of some functions defined inside a formula. These name only apply within the scope of the LET function.

The LET function is defined with a pair of names and values associated with it, which are used in the calculations in it. LET function supports up to 126 name values pair, you must define at least one name/value (a variable) pair.

Syntax :

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

Explanation:

LET function has three parameters for its operations

Here, name is the name of the variable which we use throughout our program.

name_value is the value of the variable that is given to the variable.

calculation is the logic of calculation on the variable.

Example:

LET (x, 1, x+1)  -> 2 (output)

Here, x is the first parameter which is the name of the variable,

1 is the second parameter and is the value given to the variable x (first parameter)

x+1 is the operation/ calculation that takes place on the variable

so, the calculation along with the variable and the value gives the output as 2

x = 1

1 + 1 = 2

Example with multiple variable

LET(x, 1, y, 1, x + y) -> 2 (output)

Here x, 1 and y, 1 are the names of the variable and their corresponding values and

x + y is the calculations logic which gives the output as 2

Benefits

• Improved Performance : LET allows you to call the expression by name and for Excel to calculate it once. If you write the same expression multiple times in a formula, Excel calculated that result multiple times.

• Easy Reading and Composition:  With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula.  No more having to remember what a specific range/cell reference referred to, what your calculation was doing or copy/pasting the same expression.

Example 2

Suppose you have some raw sales data, and you’d like to filter that data to show one person, and add a dash to any blank cells.

Sample data

Original Formula

Formula using LET