Table of Contents

**About**

LET function in Excel allows us to declare variables and intermediate calculations inside a formula to create meaningful formulas that work faster.

When the same expression is used multiple times inside a formula, Excel will recalculate it multiple times. LET function allows us to name expressions which can be called multiple times thereby reducing the calculation time.

**Purpose**

Improve readability and performance of formulas.

**Return value**

Result of the expression defined as calculation.

**Syntax**

=LET(name1,name_value1,calculation or name2,[name_value2, ...])

**Arguments**

**name1** – The first name to assign. Cannot be the output of a formula or conflict with range syntax. name1 should start with a letter.

**value1** – The value that is assigned to name1.

**calculation_or_name2** – Either one for the following

The calculation that uses all names within the LET function or the second name. If it’s a calculation it must be the last argument in the LET function.

A second name to assign to a second name_value. If a name is specified, name_value2 and calculation_or_name3 should follow.

**name_value2** – The value that is assigned to calculation_or_name2.

**calculation_or_name3** – The calculation or the third name_value.

**Example** 1

In this example, LET function is used declare two variables ‘x’ and ‘y’ with values ’10’ and ’20’ respectively. Following that the variables x and y are used in the calculation part to return the sum of values assigned to them.

=LET(x,10,y,20,x+y)

In the formula above,

‘x’ is * name1*,

’10’ is * value1*,

‘y’ is * name2*,

’20’ is * value2* and

‘x+y’ is the **calculation**

**Example** 2

In this example, you can see 6 formulas using LET function. The common thing between every formula is the two variables ‘A’ and ‘B’ which refer to the cells in the Columns B and C. Then, each formula uses an arithmetic operation between the variables ‘A’ and ‘B’ in the calculation part.

=LET(A,B3,B,C3,A+B)

=LET(A,B4,B,C4,A*B)

=LET(A,B5,B,C5,A/B)

=LET(A,B6,B,C6,A^2+B^2)

=LET(A,B7,B,C7,B-A)

=LET(A,B8,B,C8,A+2*B)

**Example** 3

In this example, LET function is used to find out the area of a triangle with sides A, B and C.

=LET(A,C2,B,C3,C,C4,S,(A+B+C)/2,SQRT(S(S-A)(S-B)*(S-C))

In the above formula,

A is **name1**,

the cell C2 contains **value1**,

B is **name2**,

the cell C3 contains is **value2,**

C is **name3**,

the cell C4 contains **value3**,

S is **name4**,

the expression (A+B+C)/2 is **value4 **and

the expression SQRT(S(S-A)(S-B)*(S-C)) is the **calculation**.

**Example** 4

All 3 examples explained above were about understanding what LET function is. In this example, LET function is used to shorten the length of a popular Excel formula which will extract the numbers from an alphanumeric code.

The length of this classic formula is ‘283’ characters.

=IF(SUM(LEN(B3)-LEN(SUBSTITUTE(B3, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0,SUMPRODUCT(MID(0&B3, LARGE(INDEX(ISNUMBER(--MID(B3,ROW(INDIRECT("$1:$"&LEN(B3))),1))ROW(INDIRECT("$1:$"&LEN(B3))),0),ROW(INDIRECT("$1:$"&LEN(B3))))+1,1)10^ROW(INDIRECT("$1:$"&LEN(B3)))/10),"")

If you see the formula carefully, you will notice that the expression **‘ROW(INDIRECT(“$1:$”&LEN(B3))),1))’** is repeated 3 times. And this is where we can make use of LET function.

So, I have wrapped the entire formula using LET function, have assigned the expression **ROW(INDIRECT(“$1:$”&LEN(B3))),1))** to a variable ‘X’ and has replaced this expression with ‘X’ at every location in the formula.

=LET(X,ROW(INDIRECT("$1:$"&LEN(B3))),IF(SUM(LEN(B3)-LEN(SUBSTITUTE(B3,{"0","1","2","3","4","5","6","7","8","9"},"")))>0,SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,X,1))X,0),X)+1,1)10^X/10),""))

The length of the formula is now ‘206’ characters and this modified formula is faster than it was earlier.

**Example** 5

Following an awesome Array formula created by John Walkenbach aka Mr.Spreadsheet which will create the calendar of the given month.

{=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1))<>MONTH(DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

The length of this formula is ‘256’ characters.

Using LET function I have created a shorter version of the same formula and the length of the formula is now ‘173’ characters.

=LET( x,DATE(YEAR(B2),MONTH(B2),1), y,WEEKDAY(x), array1,{0;1;2;3;4;5}, array2,{1,2,3,4,5,6,7}, IF(MONTH(x)<>MONTH(x-(y-1)+array17+array2-1),"",x-(y-1)+array17+array2-1))

### Notes

At present, LET function is available only to the Office 365 subscribers who are a part of Office Insiders channel.

Excel Functions in Alphabetical Order (Complete list)

Complete List of Excel Functions (Category wise)