Introducing the New LET Function in Excel

Introduction

In a previous post, I talked about the different ways you could create a football league table in Excel. Rather than show just one way, I wanted you to be able to see the old and new ways of doing it. Since dynamic array capabilities were unveiled, it has unravelled a plethora of possibilities with how Excel can be used.

Here, we are going to look at the new LET function. It is only available in beta versions of Excel at the moment, but it’s worth checking out because it will challenge your existing way of writing formulas.

If you have any experience with programming languages, you’ll probably be aware of the concept of a variable. A variable is a pre-defined name used to store information that is typically referenced multiple times throughout a coding script.

Based on this very concept, LET effectively allows you to use variables in formulas.

This is the syntax:

LET(name1,name_value1,calculation_or_name2…

name1 is the name of the first assigned value.

name_value1 is the value assigned to name1.

calculation_or_name2 can either be a calculation or another name if you have one. If name2 is specified, then a value must be given to name_value2. You are permitted to use up to 126 names, but the formula must always end with a calculation, however.

Before We Start

Download the example workbook: LET-Function-Example.xlsx.

I will also be referring to a workbook from a previous post, so download it if you haven’t already: League-Table-Examples.xlsx.

Please note: the LET function is only available to Office Insiders at the moment. To become an Office Insider, you need to be running a copy of Excel that’s part of Microsoft 365.

Go to File >>> Account and change your channel (if necessary) to Current Channel (Preview).

Using the LET Function

Let’s now apply the LET principles to Table C3, which was the SWITCH variant featured in the league table workbook within Part C.

We start the formula off with the LET function and specify all the column names we want to use along with their associated values. The clever thing is: instead of repeating part of a formula more than once, we can use its assigned name instead. The name TEAM has been defined first, which allows us to avoid typing UNIQUE(DataTable[team_home]) every time we want to extract all the team names from the team_home column in DataTable.

For GD, as F and A have already been defined, it’s a simple case of calculating F minus A to return the goal difference. You’ll also see that for PTS, we only have to refer to the win and draw outcomes with W and D respectively whilst multiplying by the correct points number.

=LET(
TEAM,
UNIQUE(DataTable[team_home]),

P,
(COUNTIFS(DataTable[team_home],TEAM,DataTable[played],1)
+
COUNTIFS(DataTable[team_away],TEAM,DataTable[played],1)),

W,
(COUNTIFS(DataTable[team_home],TEAM,DataTable[Result],"H")
+
COUNTIFS(DataTable[team_away],TEAM,DataTable[Result],"A")),

D,
(COUNTIFS(DataTable[team_home],TEAM,DataTable[Result],"D")
+
COUNTIFS(DataTable[team_away],TEAM,DataTable[Result],"D")),

L,
COUNTIFS(DataTable[team_home],TEAM,DataTable[Result],"A")
+
COUNTIFS(DataTable[team_away],TEAM,DataTable[Result],"H"),

F,
SUMIFS(DataTable[home_goal],DataTable[team_home],TEAM)
+
SUMIFS(DataTable[away_goal],DataTable[team_away],TEAM),

A,
SUMIFS(DataTable[away_goal],DataTable[team_home],TEAM)
+
SUMIFS(DataTable[home_goal],DataTable[team_away],TEAM),

GD,
F-A,

PTS,
(W*3)+(D*1),

By defining the names first, it makes the second part of the formula a lot easier. The actual formula structure here mirrors what was found in Table C3. However, all we are doing instead is referring to the names and making sure they output in the correct column.

SORT(SWITCH(
{"TEAM","P","W","D","L","F","A","GD","PTS"},
"TEAM",TEAM,
"P",P,
"W",W,
"D",D,
"L",L,
"F",F,
"A",A,
"GD",GD,
"PTS",PTS),

{9,8,6,1},{-1,-1,-1,1})
)

The sort_index argument consists of {9,8,6,1} (PTS, GD, F, TEAM) and determines the priority order of the column sorting. After this, sort_order decides whether each of these indexes should be sorted in ascending (1) or descending (-1) order.

Aside from the main formula just shown, two additional formulas were used.

One was an array constant for the table headings:

={"POS","TEAM","P","W","D","L","F","A","GD","PTS"}

Array constants allow you to store multiple values in the same formula. In this case they’ve been used to house all the column headings of the table, and will spill across appropriately. Only text strings and numbers are permitted in array constants—you cannot use formulas.

The other formula was for the position numbers:

=SEQUENCE(COUNTA(D17:D36))

The COUNTA function counts how many team names in the range are not empty, and the result of this is wrapped inside the SEQUENCE function as part of the rows argument. As there are 20 teams, the result is 20 position numbers incrementally spilled down.

To be precise, we have used just three formulas to create this table. That is despite it being made up of 210 cells. Amazing, eh?

Final Words

Hopefully you’ll be able to see how convenient the LET function can make things. By stripping out the repetition, the readability of your formulas will improve. And if you are working with a large dataset, there will be a noticeable difference in calculation speed. What has been shown is just one way of using LET. Just like with the dynamic array functions, over time people will discover new possibilities with what can be achieved. Harnessing the power of LET whilst combining it with other functions will enable you to do a lot more with less.