Google Sheets Function: LET
The Excel LET function assigns a name to values and then returns the result of the formula.
This function allows reusing a result multiple times without having to recalculate it multiple times (if you are familiar with the concept of variables, this function allows assigning values to variables and then using these variables in the formula).
Usage:
=LET(name1, value_expression1, formula_expression)
or
=LET(name1, value_expression1, name2, value_expression2, ..., formula_expression)
Example of use
In this simple example (with IF and AVERAGE functions), the formula displays one of 3 pieces of information depending on the average of the scores:
=IF(AVERAGE(B2:C2)<3,"Fail",IF(AVERAGE(B2:C2)<4,"Pending","Pass"))
In this formula, the same average is calculated twice, so the goal here will be to use the LET function to avoid this double calculation.
Enter the LET function followed by the name of the variable (for example, "average"):
=LET(average
Then enter the formula that will correspond to "average":
=LET(average,AVERAGE(B2:C2)
And to finish, insert the previous formula in its entirety and replace AVERAGE(B2:C2) with average:
=LET(average,AVERAGE(B2:C2),IF(average<3,"Fail",IF(average<4,"Pending","Pass")))
The LET function will then calculate the average once and then calculate the result of the IF formula: