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(Optional) (or name3) The formula that uses both the "name1" and "name2" variables (or the name of a third variable).

Remarks

* Only available in Microsoft 365 and has not been released to everyone yet.
* 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 127 (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 either True or False.
* You can use the RANDBETWEEN function to return the random number between two specified numbers (inclusive).
* You can use the SUM function to return the total value of the numbers in a list, table or cell range.
* For the Microsoft documentation refer to support.microsoft.com

 AB
1=LET(myvar,5,myvar+1) = 610
2=LET(myvar,5,myvar+100) = 10520
3=LET(myvar,100,myvar/5) = 2030
4=LET(myvar,5,SUM(myvar,1)) = 650
5=LET(myvar,5,myvar +B1+B2) = 3560
6=LET(myvar,B1,myvar+1) = 11text
7=LET(myvar,SUM(B1:B4),IF(myvar>100,myvar,"less than 100")) = 110 
8=LET(myvar,10,myvar2,20,myvar+myvar2) = 30 
9=LET(myvar,RANDBETWEEN(1,2),myvar=myvar) = True 
10=LET(myvar,5,SUM(differentvar,1)) = #NAME? 
11=LET(myvar,5,myvar +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.

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