LET: formulas you can read

Long formulas fail in a specific way: the same piece shows up three times, someone fixes two of them, and the sheet is wrong for a month. LET fixes that by naming the pieces first:

=LET(name1, value1, name2, value2, ..., result)

Every pair is a name and what it means. The last input is the answer, written with the names. Compare:

=IF(SUM(C2:C7)/SUM(B2:B7) >= 4, "Scale", IF(SUM(C2:C7)/SUM(B2:B7) >= 2, "Hold", "Cut"))

=LET(roas, SUM(C2:C7)/SUM(B2:B7),
     IF(roas >= 4, "Scale", IF(roas >= 2, "Hold", "Cut")))

The second one says what it does. It's also faster: Sheets works out roas once instead of twice. Press Ctrl+Enter (⌘+Enter on a Mac) inside the formula bar to break a long LET over several lines, as above.

Later names can use earlier ones, so a LET can read like a short calculation:

=LET(rev, SUM(C2:C7),
     spend, SUM(B2:B7),
     cost, spend*(1+E2),
     (rev-cost)/rev)

Three habits:

• Pick names that can't be cells. rev and spend are fine. Q1 and ROI2 look like cell addresses and return an error.
• Debug by swapping the last input. Change the result to just cost to see that one piece, then put the real result back.
• Array math still needs ARRAYFORMULA. LET(x, B2:B7, SUM(x*2)) errors in Google Sheets. Wrap it: ARRAYFORMULA(LET(...)), or keep the array math inside a function that handles ranges, like SUMPRODUCT.