LET function
LET function
The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126.
Benefits
Improved Performance If you write the same expression multiple times in a formula, Excel calculated that result multiple times. LET allows you to call the expression by name and for Excel to calculate it once.
Easy Reading and Composition No more having to remember what a specific range/cell reference referred to, what your calculation was doing or copy/pasting the same expression. With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula.
Examples
Example 1
Consider the simple expression "SUM(x, 1)" where x is a named variable that can be assigned a value (in this case, x is assigned the value 5).
=LET(x, 5, SUM(x, 1))
When this formula is input into a cell, it will return the value 6.
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.
Unfiltered Data | Filtered Data |
|---|---|
|
|
While the above can be done without a LET, using LET improves readability of the formula and computes twice as fast as without LET.
Example-3
Sample data
Copy the following table to cell A1 of a blank worksheet if you'd like to walk through this example yourself.
Rep | Region | Product | Profit |
Amy | East | Apple | $1.33 |
Fred | South | Banana | $0.09 |
Amy | West | Mango | $1.85 |
Fred | North | $0.82 | |
Fred | West | Banana | $1.25 |
Amy | East | Apple | $0.72 |
Fred | North | Mango | $0.54 |
Comments
Post a Comment