LET Function Arguments Explained
name1 - The name of the variable (cannot be the output of a formula or conflict with range syntax)
value1 - Values can be text, formulas/expressions, arrays,
numbers, cell references, Boolean values or defined names
name2 - Optionally add more name and value pairs as required
value2 - As above
calculation - the formula/expression that uses the names within the LET function.
Notes:
- LET will not error if you don't use all the names, but any unused names are redundant, so you should remove them to avoid confusion and unnecessary work for Excel
- The formula should have an odd number of arguments i.e. pairs of names and values, then a calculation
- The ‘calculation’ can be contained inside of a name and value pair. e.g. here I’ve declared a name for the formula; ‘result’ =LET(x, 5, y, 10, result, x + y, result)
- Values can reference previously declared names, but not names downstream e.g. x in this formula is later referenced in y’s value argument =LET(x, 5, y, x+1, total, y * 2, total) = 12
- Avoid using names in the LET function that are
already defined names in the name manager. If you use a name already defined in the name manager, LET will ignore the name manager version
- Names defined inside of LET are available in the intellisense drop down.
- LET formulas can be defined as names in the name manager
When to use the LET Function
Improve Formula
Readability
Naming variables, like ranges being referenced etc., makes it easier to understand what the formula is doing. Before the LET function we would define names in the Name Manager for this purpose, however LET now enables us to define
names more quickly inside of the formula itself making it quicker to write and quicker to interpret.
For example, in the formula below we can easily understand that it is calculating sales from cells C2:C500 including GST of 10%, without the need to refer to any other cells, sheets or defined names:
=LET(
GST, 10%
Sales, C2:C500,
SalesIncl.GST, Sales*1+GST,
SalesIncl.GST)
The limitation of defining names inside of LET is that those names can only be used inside of that specific LET formula whereas names defined in the name manager can have the scope of the workbook.
Improve Calc Performance
The other
reason to use LET is for improved performance through the elimination of duplicate calculations. By naming calculations inside of LET they are being evaluated once at the beginning of the formula and the results can then be re-used multiple times in the formula without requiring further calculations. This can significantly improve performance.
For example, the formula below performs the SUM at least twice. Once for the logical
test and again for either the TRUE or FALSE outcome:
Whereas with LET we can reduce this to a single calculation by declaring it as a name, SalesCY, and then reusing the name later in the formula:
Obviously in practice there will be more efficiency to be gained with more complex formulas.
Excel LET Function Examples
Referencing Names in Values
Names you define can be used inside of downstream value arguments. For example, the value
argument for the name ‘agg’ in the formula shown below references the name ‘select’ which was previously declared.
When you’re authoring a LET function, prior names will be available in the intellisense drop down to choose from, just like a name defined in the name manager:
Relative References
In this example I want you to take notice of the use of relative references in the name arguments.
First, the data I’m using is called Table1. It contains sales by brand and product category:
Below I’ve summarised the table by
brand and month using a LET formula:
The formula in cell G4 (wrapped onto separate lines for readability) is:
=LET(
MonthStart, G$3,
MonthEnd, EOMONTH(G$3,0),
Brand, $F4,
BrandRng, FILTER(Table1[Sales], (Table1[Brand]=Brand)*(Table1[Date]>=MonthStart)*(Table1[Date]<=MonthEnd)),
SUM(BrandRng)
)
You can see in the image above the LET formula nests the EOMONTH function and the FILTER function which perform calculations that are later used in the SUM calculation argument.
Also note that the first three ‘value’ arguments for each name use relative references in either the row or column, thus allowing the LET function to be copied to the remaining cells
in the table.
Tip: When copying formulas with Table Structured References, be sure to copy and paste as opposed to left clicking and dragging to ensure the absolute referencing of the structured references is
applied correctly.
Arrays as an Input and Output
You may have already noticed in the second example that the LET function can accept arrays as inputs, but it can also return arrays as outputs. The ‘RankArray’ name in the example below has an array as a value {1;2;3}, and it returns an array, being the list of top 3 brands which spills to the cells D5:D7:
Debugging LET Formulas
You can easily check the results
returned by a name and value pair by placing that name in the last argument (which is ‘calculation’). In the example below in cell C6 the calculation has been declared as a name (calc) and value pair, and the last argument simply repeats the last name to return the result:
However, we can test any name and value pair by replacing the last argument with a different name. For example, say I want to check how the ‘Rng’ name evaluates. In the image below I’ve placed ‘Rng’ in the last argument of the LET formula and you can see in cell C6 it
now spills the sales values from Table1:
I can
then simply replace ‘Rng’ with ‘Calc’ in the last argument to return the final calculation I want, without losing the formula I authored for ‘Calc’.
Learn With Us
