Photo by Unsplash

Released for Office 365 recently, the new LET function brings clarity and transparency to spreadsheets by assigning names to the results of calculations.

Usage

The use of named ranges in Excel is a good way to help other users understand the workbook. When setting up named ranges, Excel makes the name available for use in the entire workbook by default. However, by restricting the scope to a single worksheet, one can use the same name repeatedly in the same workbook.

Arguments

LET(name1, value1, [name2], [value2], … [namen],[valuen], calculation)
name1 = The name of the first value.
value1 = Value assigned to the first name.
name2, value2 … namen, valuen = Optional additional sets of names with values.
calculation = Calculation that uses names defined by previous arguments.

One can provide multiple name/value sets before using these in the final calculation. These sets can perform calculations using names defined earlier.

Example 1

In the example in the Amort worksheet of the LET demo workbook, the objective is to calculate the annual straight-line amortization cost of some assets, given cost, useful life and salvage value.

The calculation would be simple enough with =(B6-B8)/B7.

But setting out the calculation in the LET function adds clarity and reduces the chance of a calculation error.

=LET(cost,B6,life,B7,salvage,B8,(cost-salvage)/life)

Example 2

Another common situation arises when working with the IF function. A calculation is performed once to determine whether a condition is met, then carried out a second time for the result.

In the example in the BudgetChange worksheet, operating expense budgets for departments of the University of Manitoba are compared and areas with greater than a 5% budget increase are highlighted.

A way to accomplish this is with the formula below.

=IF((B6-C6)/C6>0.05,(B6-C6)/C6,"")

However, LET provides a more readable, albeit longer, formula.

=LET(CurrentBudget,B6,PriorBudget,C6,PercentChange,(CurrentBudget-PriorBudget)/PriorBudget,IF(PercentChange>0.05,PercentChange,""))

To improve readability, add linefeeds after each set of name and value. When entering the formula, press <Alt><Enter> after each set.

=LET(CurrentBudget,B6,
PriorBudget,C6,
PercentChange,(CurrentBudget-PriorBudget)/PriorBudget,
IF(PercentChange>0.05,PercentChange,""))

In the AverageChange worksheet, the percent change is returned for all departments. The data has now been set up in a table to provide additional clarity, so the formula in the Average Change column uses column names and returns an array.

=LET(CurrentBudget,Budget[Budget 2021],
PriorBudget,Budget[Budget 2020],
DollarChange,CurrentBudget-PriorBudget,
PercentChange,DollarChange/PriorBudget,
PercentChange)

At this point, the formula is practically written in English and contains no cell references that are not named.

Recommendation

Any time a longer formula contains more than one instance of the same calculation, consider using the LET function to improve readability and performance of the formula.

Download the LET demo workbook.

Here is the links to the Microsoft documentation for the LET function.