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***, etc.) and the**

*name2expression***. The local names (**

*namesExpression***,**

*name1***, etc.) will override the workbook and worksheet names or local names defined at a higher scope of nested**

*name2***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

**if a local name does not override them. The formula in cell**

*namesExpression***C13**defines the local name

**using the worksheet name**

*user***and now the result shows**

*user,***"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

**and the**

*nameExpressions***of the inner**

*namesExpression***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

**as**

*var***"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

**using the name**

*var***(which was just described as**

*var***"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

**to use the**

*filteredRange***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

**again inside the**

*filteredRange***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**: