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)