The new LET Function recently added in Microsoft Excel seems so simple but is a powerhouse for improving calculation performance for specific formulas.
Sometimes it is necessary to repeat the same calculation more than once in a formula, usually when the calculation involves several separate cases. Each case needs to repeat some calculations. In such formulas, the new LET Function improves calculation performance. The LET Function also clarifies the logic of complex formulas by assigning useful names to formula expressions and then using those names to create a new and more easily readable formula expression. We are thrilled to include this robust feature in Spread. NET v14.
This blog includes the following sections:
- LET Function Definition
- Basic Usage
- Custom Name
- Nested LET Functions
- Simplify Complex Formulas
- Aggregate Using LET Function
- Performance Improvement Using LET Function
- LET Function Demo Samples
LET Function Definition
The LET Function defines one or more local names used in the final argument to specify the formula expression to evaluate:
LET(name1, name1expression, [name2, name2expression, ...] namesExpression)
name1: Local name1 used in namesExpression
name1expression: Value of name1
name2: Local name2 used in namesExpression name2expression: Value of name2
namesExpression: Formula expression using name1, name2, etc.
Define any number of names to be used in the namesExpression, or use the LET Function recursively to define new local scopes for names. Workbook and worksheet names can be used inside the name definition expressions (name1expression, name2expression, etc.) and the namesExpression. The local names (name1, name2, etc.) will override the workbook and worksheet names or local names defined at a higher scope of nested LET functions.
Basic Usage
Figure 1 LET Function Basic Usage examples
Some simple examples illustrate the primary usage of the new LET function.
The first example, Sample pair variable, shows a simple LET Function defining the name x as 2 and returning x+3 = 2+3 = 5 in the Result cell:
=LET(x,2,x+3)
The second example, Multiple pair variables, shows the LET Function defining multiple names x, y, and z and using those names to compute a value:
=LET(x,1,y,2,z,3,x+y+z=x*y*z)
In the third example, Invoked formula, the LET Function defines multiple names x and y and uses those names with another function, SUM, to perform a calculation:
=LET(x,1,y,2,SUM(x,y))
The expression using the names defined inside the LET Function can be complicated and use expressions that return multiple values in a dynamic array that spill into adjacent empty cells.
The fourth example, Dynamic Array, shows the LET Function used in conjunction with the OFFSET function, using a range reference to C18:E18 to return a dynamic array in C19:E19:
=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))
Custom Name
Figure 2 LET Function Custom Name examples
These examples demonstrate how custom names defined in the workbook or worksheet interact with local names defined inside the LET Function. This example defines the name user to use the value "Michael," which is referenced in the formula in cell C5:
=user
The formula in cell C9 uses the LET function to redefine the name user to use the value "Ivy" instead:
=LET(user,"Ivy","The actual user is: "&user)
The result is that C9 shows the text. The actual user is Ivy since the locally defined user overrides the worksheet- and/or workbook-defined user.
You can use custom names defined in the worksheet or workbook inside the nameExpression for any name or in the namesExpression if a local name does not override them. The formula in cell C13 defines the local name user using the worksheet name user, and now the result shows "Michael" again:
=LET(user,user,"The actual user is: "&user)
Nested LET Functions
Figure 3 LET Function Nested examples
These examples demonstrate the LET function's scoping rules when used recursively inside the namesExpression of another LET Function. The same scoping rules are followed in the nested LET Function, and the names defined in the outer LET Function are available for use in the nameExpressions and the namesExpression of the inner LET Function. Those names can be overridden with new local names defined in the internal LET Function.
The first example defines the name var as "First Scope" in the outer LET Function, then redefines var as "Second Scope" in the inner LET function:
=LET(var,"First Scope",LET(var,"Second Scope",var))
The result shows the Second Scope because the nested LET Function overrides the outer LET Function, just as it would if the name var was defined as a worksheet or workbook name.
The second example defines the name var as "First Scope" in the outer LET Function, then redefines the name var using the name var (which was just described as "First Scope" in the external LET function) in the inner LET Function:
=LET(var"First Scope",LET(var,var,var&"[from the second scope]"))
The result shows First Scope[from the second scope].
Simplify Complex Formulas
Figure 4 LET Function Simplify examples
These examples show how some complex formulas can be simplified and optimized using the LET Function. These examples show how repeated expressions in a formula can be replaced with local names to optimize the calculation.
The first example uses the LET Function to define the name filterCriteria as the cell reference H7 and the name filteredRange to use the FILTER function on the range B7:E13. To return the rows where the value in B7:B13 is "Fred," use the IF and ISBLANK functions to replace empty values in the result with a padded dash:
=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange)," - ", filteredRange))
Note that filteredRange is used twice, which reuses the array result already calculated to evaluate each cell's IF function in the array. Without using the LET Function, this formula would need to recalculate the filteredRange again inside the IF function:
=IF(ISBLANK(FILTER(B7:E13,B7:B13=H7))," - ",FILTER(B7:E13,B7:B13=H7))
The next example uses the LET Function to define the name dates using the SEQUENCE function to return a vertical dynamic array of date values from the start date in C18 to the end date in C19. Then it uses the FILTER function to return only the dates where the WEEKDAY function returns a value indicating the day of the week is Monday through Friday:
=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))
Note that dates is used twice, which reuses the array result already calculated to evaluate the FILTER and WEEKDAY functions on each cell in the array. Without using the LET Function, this formula would need to recalculate the dates in both the FILTER and WEEKDAY functions:
=FILTER(SEQUENCE(C19-C18+1,1,C18,1),WEEKDAY(SEQUENCE(C19-C18+1,1,C18,1),2)<6)
Aggregate Using LET Function
Figure 5 LET function Aggregate example
This example shows the LET Function used together with ComboBoxCellType to create a drop-down list for selecting the aggregation function to calculate a formula.
The ComboBoxCellType uses EditorValue.ItemData to update C9 using the number associated with the selected aggregation function in the drop-down list, and StopEditingAfterDropDownItemSelected to stop edit mode after a selection is made in the list so that cells are recalculated immediately.
The formula in D9 uses the LET Function to define the name data using a structured reference to the column Salary in Table2 and also define the name aggregate to reference C9, then uses SUBTOTAL to calculate the value:
=LET(data,Table2[Salary],aggregate,C9,SUBTOTAL(aggregate,data))
Performance Improvement Using LET Function
Figure 6 LET function Performance example
This example shows the performance improvement when using the LET Function to calculate a dynamic array value and repeatedly reference that array in a formula.
Both spreadsheet controls are initialized with the same list of 500 names and addresses, and both spreadsheets use this formula in cell N2 to return a sorted list of unique states in a dynamic array:
=SORT(UNIQUE(Table1[state]))
Both spreadsheet controls use a formula in the cells O2:O48 to calculate the number of rows in the table and return a string value based on the number of rows in nested IF functions.
The left side uses the LET Function to optimize this calculation and reuse the dynamic array result inside the IF Functions:
=LET(cities,ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,""))),IF(cities<10,"Less than 10.",IF (cities<20,"Between 10 and 19.",IF(cities<30,"Between 20 and 29","30 or more."))))
The right side does not use the LET Function and instead repeats the expression for cities inside the IF Function:
=IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"")))<10,"Less than 10.",IF(ROWS(UNIQUE(FILTER(Table1
[city],Table1[state]=$N2,"")))<20,"Between 10 and 19.",IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"")))
<30,"Between 20 and 29","30 or more.")))
Activating the Recalculate menu item for recalculation utilizes unique code to disable CalculationOnDemand in the CalculationEngine. This action forces all cells to recalculate. The results are shown in the TitleInfo across the top of each spreadsheet control.
Using the LET Function the left side calculates 2 to 4 times faster than the right side, which does not use the LET Function.
LET Function Demo Samples
Demo samples for the LET Function with the full source code showing the above examples are available for both C# and VB:
Related Blogs
Try Our .NET Spreadsheet Components
Spread.NET Resources
Sean Lawyer