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
Name | Region | Product | Profit |
Amit | East | Apple | ₹10.33 |
Fatima | South | Banana | ₹15.09 |
Amit | West | Mango | ₹7.85 |
Fatima | North | ₹60.82 | |
Fatima | West | Banana | ₹12.25 |
Amit | East | Apple | ₹6.72 |
Fatima | North | Mango |
₹8.54 |
Original Formula
=IF(ISBLANK(FILTER(A2:D8,A2:A8=”Fatima”)),”-“, FILTER(A2:D8,A2:A8=”Fatima”)) |
Formula using LET
=LET(filterCriteria, “Fatima”, filteredRange, FILTER(A2:D8,A2:A8=filterCriteria), IF(ISBLANK(filteredRange),”-“, filteredRange)) |