Purpose
Return value
Syntax
=LET(name1,value1,[name2/value2],...,result)
- name1 - First name to assign. Must begin with a letter.
- value1 - The value or calculation to assign to name 1.
- name2/value2 - [optional] Second name and value. Entered as a pair of arguments.
- result - A calculation or a variable previously calculated.
Using the LET function
The LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write. Once a variable is named, it can be assigned a static value or a value based on a calculation. The formula can then refer to a variable by name as many times as needed, while the value of the variable is defined in one place only.
Example 1 | Example 2 | Example 3 | Example 4 | More examples
Variables are named and assigned values in pairs, separated by commas (name1,value1, name2,value2, etc). LET can handle up to 126 name/value pairs, but only the first name/value pair is required. The scope of each variable is the current LET function and nested functions below. The final result is a calculation or a variable previously calculated. The result from LET always appears as the last argument to the function.
The names used in LET must begin with a letter and are not case-sensitive. You can use names that contain numbers like “count1”, “count2”, etc., but names like “ct1” and “ct2” will fail because Excel will interpret the names as a cell reference. Space characters and punctuation symbols are not allowed in names, but the underscore character (_) can be used.
The LET function is often combined with the LAMBDA function as a way to make a complex formula easier to use. LAMBDA provides a way to name a formula and reuse it in a worksheet like a custom function. Example here .
Key Benefits
The LET function provides three key benefits:
- Clarity - naming variables used in a formula can make a complex formula much easier to read and understand.
- Simplification - naming and defining variables in just one place helps eliminate redundancy and the errors that arise from having the same code in more than one place.
- Performance - elimination of redundant code means less calculation time overall since expensive calculations only need to occur once.
Example #1
Below is the general form of the LET function with one variable:
=LET(x,10,x+1) // returns 11
With a second variable:
=LET(x,10,y,5,x+y) // returns 15
After x and y have been declared and assigned values, the calculation provided in the 5th argument returns 15.
Example #2
A chief benefit of the LET function is simplification by eliminating redundancy. For example, the screenshot above shows a formula that uses the SEQUENCE function to generate all dates between May 1, 2020 and May 15, 2020, which are then filtered by the FILTER function to include only weekdays. The formula in E5 is:
=LET(dates,SEQUENCE(C5-C4+1,1,C4,1),FILTER(dates,WEEKDAY(dates,2)<6))
The first argument declares the variable dates and the second argument assigns the output from SEQUENCE to dates :
=LET(dates,SEQUENCE(C5-C4+1,1,C4,1)
Notice the start and end dates come from cells C4 and C5, respectively. Once dates has been assigned a value, it can be used in the final calculation, which is based on the FILTER function:
FILTER(dates,WEEKDAY(dates,2)<6)) // filter out weekends
Notice dates is used twice in this snippet: once by FILTER, once by the WEEKDAY function . In the first instance, the raw dates from SEQUENCE are passed into the FILTER function as the array to filter. In the second instance, the dates from SEQUENCE are passed into the WEEKDAY function, which tests for weekdays (i.e. not Sat or Sun). The result from WEEKDAY is the logic used to filter the original dates.
Without the LET function, SEQUENCE would need to appear twice in the formula, both times with the same (redundant) configuration. The LET function allows the SEQUENCE function to appear and be configured just once in the formula.
More examples
- For an example of how LET can make a tricky formula easier to follow, see this page .
- For a more complex example of how LET can eliminate redundancy, see this example .
Purpose
Return value
Syntax
=MAKEARRAY(rows,columns,function)
- rows - The number of rows to create.
- columns - The number of columns to create.
- function - The custom LAMBDA calculation to apply.
Using the MAKEARRAY function
The MAKEARRAY function returns an array with specified rows and columns, based on a custom LAMBDA calculation . MAKEARRAY can be used to create arrays with variable dimensions that are calculated. The generic syntax for MAKEARRAY looks like this:
=MAKEARRAY(rows,columns,function)
The MAKEARRAY function takes three arguments: rows , columns , and function . Rows is the number of rows to create, and columns is the number of columns to create. The function is a custom LAMBDA (see below) to use when creating values in the array. The total number of values in the array returned by MAKEARRAY will equal rows * columns.
LAMBDA structure
The MAKEARRAY uses the LAMBDA function to apply the function used to calculate array values. The structure of the LAMBDA used by MAKEARRAY is:
LAMBDA(r,c,calculation)
where r represents the row count, c represents the column count originally passed into MAKEARRAY, and calculation is the formula needed to create the values in the final array.
Note: to generate an array with sequential values, see the SEQUENCE function .
Examples
In the formula below, MAKEARRAY is used to create an array with 2 rows and 3 columns, populated with the result multiplying rows by columns:
=MAKEARRAY(2,3,LAMBDA(r,c,r*c)) // returns {1,2,3;2,4,6}
The result is a 2 x 3 array with six values {1,2,3;2,4,6}. The calculation can be a hardcoded value as well. Below are examples of the same formula, with calculation hardcoded as zero and “x”, respectively:
=MAKEARRAY(2,3,LAMBDA(r,c,0)) // returns {0,0,0;0,0,0}
=MAKEARRAY(2,3,LAMBDA(r,c,"x")) // returns {"x","x","x";"x","x","x"}
Random values
MAKEARRAY can be used to generate random values. In the formula below. The CHAR function is used with the RANDBETWEEN function to generate random uppercase letters A-Z:
=MAKEARRAY(2,3,LAMBDA(r,c,CHAR(RANDBETWEEN(65,90))))
The result is a 2 x 3 array like {“D”,“Q”,“F”;“V”,“C”,“T”}.
