LET

LET(name1, name_value1, calculation [,name_value2] [,calculation] [..])

Returns the result of a formula that can use variables.

name1The name of the first variable.
name_value1The value or expression to assign to the first variable.
calculation(or name2) The formula that uses the "name1" variable (or the name of a second variable).
name_value2(Optional) The value or expression to assign to the second variable.
calculation(or name3) (Optional) The formula that uses both the "name1" and "name2" variables (or the name of a third variable).

REMARKS
* This function was added in Excel 2021.
* For an illustrated example refer to the page under Advanced Functions
* This function can create a Dynamic Array Formula.
* This function allows you to create internal variables that can be used in the calculation/formula.
* This function can remove duplicates in a formula and guarantees the expression will only be calculated once.
* You can have a maximum of 126 (name,value) pairs.
* If you use a variable in your calculation that has not been declared then #NAME? is returned. Example 10.
* You can use the IF function to return the value based on a logical test that is True or False.
* You can use the RANDBETWEEN function to return a random integer between two values (>=bottom and <=top).
* You can use the SUM function to return the total of the numerical values.
* For the Microsoft documentation refer to support.microsoft.com

 AB
1=LET(v_one, 5, v_one+1) = 610
2=LET(v_one, 5, v_one+100) = 10520
3=LET(v_one, 100, v_one/5) = 2030
4=LET(v_one, 5, SUM(v_one, 1)) = 650
5=LET(v_one, 5, v_one + B1 , B2) = 3560
6=LET(v_one, B1, v_one, 1) = 11text
7=LET(v_one, SUM(B1:B4), IF(v_one>100, v_one, "less than 100")) = 110 
8=LET(v_one, 10, v_two, 20, v_one, v_two) = 30 
9=LET(v_one, RANDBETWEEN(1, 2), v_one=v_one) = -1 
10=LET(v_one, 5, SUM(v_two, 1)) = #NAME? 
11=LET(v_one, 5, v_one + B6) = #VALUE! 

1 - Declare a variable that contains the value 5 and add 1 to its value.
2 - Declare a variable that contains the value 5 and add 100 to its value.
3 - Declare a variable that contains the value 100 and divide it by 5.
4 - Declare a variable that contains the value 5 and use that variable for an argument in the SUM function.
5 - Declare a variable that contains the value 5 and add the contents of cells B1 and B2 to its value.
6 - Declare a variable that contains the value in cell B1 and add 1 to its value.
7 - Declare a variable that contains the result from the expression "SUM(B1:B4)" and use that variable twice in the IF function.
8 - Declare two variables, the first containing the value 10 and the second containing the value 20 and add those values together.
9 - Declare a variable that contains the result from the expression "RANDBETWEEN(1,2)" and check if both the occurrences of that variable contain the same value.
10 - Declare a variable that has a different name to the one used in the calculation.
11 - Declare a variable that contains the value 5 and add the contents of cell B6 which contains text.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top