Excel’s new LET() function

I’ve spent the last week experimenting with the features introduced in Excel 2021. The most promising, by a long way, is the new LET() function. It takes a little patience to understand LET because it is not a traditional calculation function, but it’s time well spent.

With LET, you can define variables, and values to those variables, using pairs of arguments. LET’s final argument is a formula that calculates the function’s return value, and you can use the variables assigned by the previous arguments to write it. Here are two examples that Microsoft provide on their support site:

In the first example, the variable ‘x’ (argument 1) is assigned the value ‘1’ (argument 2). Argument 3 holds a functions that adds ‘1’ to x and returns the result ‘2’. The second example is similar but uses two variable/value pairs with argument 5 returning the sum of the two variables.

That doesn’t look too exciting until you realise that the variable assignments are not restricted to constants but can be complex calculations with references to other cells.

Here is an example I put together using the workbook  we distribute with our CC-Excel add-in:

Column E is for entering the number of calls received in an hour. Column F is an estimation of the number of agents needed to handle those calls and uses our CCXLAgents() function. I wanted to add a column that displays the text ‘PEAK’ in the row representing the hour when most agents are needed. In the other rows, I wanted to show the percentage of agents required in that hour compared with the peak. Before Excel 2021, for Row 3 I would have entered:

=IF(F3=MAX(F:F), “PEAK”, F3/MAX(F:F))

In plain English, the maximum number of agents is MAX(F:F). If the agents in F3 is equal to this, the result is ‘PEAK’, otherwise it’s the number of agents divided by the maximum. Significantly, MAX(F:F) is entered and calculated twice.

In the final column, I used the LET function, assigning MAX(F:F) to the variable x in LET’s first two arguments:

=LET(x, MAX(F:F), IF(F3=x,”PEAK”,F3/x))

The third argument is an IF statement that uses the previously calculated value of x to establish LET’s return value (either ‘PEAK’ or a percentage).

It’s a simple example, but the important point is that I only entered MAX(F:F) once, and that’s the idea behind LET: to avoid the need for repetition. You can break complex calculations into smaller chunks to make them easier to read, simpler to maintain and perform faster. I’m now working through my workbooks looking for opportunities to simplify them with LET().