How to Use the LET Function in Google Sheets
=LET(name1, value_expression1, [name2, value_expression2, ...], formula_expression).
What LET does
Long formulas often repeat the same calculation. A rating label might need the average of three cells in every branch of an IF. LET stores that calculation once under a name, so the formula is shorter, easier to read and, per Google, each value expression is evaluated only once even if you use its name several times.
LET syntax
=LET(name1, value_expression1, [name2, value_expression2, ...], formula_expression)
name1: the identifier you assign. Usage is case-insensitive.value_expression1: the formula whose result is stored under that name. It can use names declared earlier in the same LET.formula_expression: the last argument. It uses the names and produces the output.
Names are scoped left to right, so a name can only be used after it is declared. A name cannot be a cell reference such as A1, cannot contain spaces or special characters, and cannot start with a number.
A step-by-step example
Here are three quarterly ratings in cells A1:C2.
| A | B | C | |
|---|---|---|---|
| 1 | Q1 | Q2 | Q3 |
| 2 | 5 | 4 | 3 |
=LET(avg, AVERAGE(A2:C2), IF(avg >= 4, "Great", IF(avg >= 3, "Good", "Poor")))
This returns Great. Here is why: avg stores the average of 5, 4 and 3, which is 4, and the average is then tested twice without being calculated twice. This is the same shape as Google's own example. Without LET, you would write AVERAGE(A2:C2) in every condition.
A simpler use
=LET(total, SUM(A2:C2), total / 3)
This returns 4. The name total holds 12, and the last argument divides it by 3.
Common mistakes
- A name that looks like a cell. Something like
B2triggers an error. - Using a name before declaring it. Declare it earlier in the same LET.
- Forgetting the final formula. The last argument is the result.
Try it yourself
Use the table above (A1:C2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Rating label
Write a formula that stores the average of A2:C2 under a name, then shows Great if it is 4 or more, Good if it is 3 or more, and Poor otherwise.
Exercise 2: Name the total
Write a formula that stores the sum of A2:C2 under a name, then returns that name divided by 3.
What to learn next
- MAP and LAMBDA: run your own formula on every value in a range.
- IFS: test several conditions in order without nesting.
- AND, OR and NOT: combine several true or false tests.
Syntax and rules in this guide were checked against Google's LET help page. See all guides.