# LET Function

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.