Long formulas often repeat the same piece: =IF(C2*D2>2000, C2*D2*0.9, C2*D2) works out C2*D2 three times. LET names the piece once and then uses the name:
=LET(fee, C2*D2, IF(fee>2000, fee*0.9, fee))
The arguments come in pairs, a name and its value, and the last argument is the calculation that uses the names. Later values can use earlier names: LET(revenue, C2*D2, cost, C2*35, revenue-cost).
Three rules save time:
- A name is a word such as
fee or tax_rate. A name that looks like a cell address, such as q1 or tax1, is read as that cell, so LET refuses it.
- Names only exist inside their own LET. Another cell can't use them.
- A name can hold a whole range, or a list from FILTER. Range arithmetic such as
C2:C9*D2:D9 still needs ARRAYFORMULA to give a whole column, inside LET too.
=LET(fee, C2*D2, IF(fee>2000, fee*0.9, fee))
=LET(revenue, C2*D2+E2, cost, C2*$K$1+E2, (revenue-cost)/revenue)
A fee with a volume discount; a margin built from two named parts.