Logical
LET function: name intermediate calculations once inside a formula
Store repeated sub-expressions in readable local names with LET so tiered pricing and margin formulas run cleaner and faster.
Stop repeating the same sub-formula three times
Tiered invoice formulas often compute A2*B2 repeatedly to check every discount bracket and then compute the final total again. LET(name1, value1, [name2, value2, ...], calculation) binds intermediate values to local names that exist only inside that formula.
Each name can reference names defined earlier in the same LET call, making complex business rules read top to bottom.
| Units (A) | Unit Price (B) | sub = A*B | Tiered Rate Rule | Net Total |
|---|---|---|---|---|
| 25 | 24 | 600 | >= 500 -> 12% off | 528 |
| 10 | 24 | 240 | >= 200 -> 5% off | 228 |
| 8 | 25 | 200 | Exact 200 boundary -> 5% off | 190 |
| 5 | 25 | 125 | < 200 -> 0% off | 125 |
Bind subtotal and discount rate with LET
Here sub computes A2 * B2 once, rate selects 12%, 5%, or 0% using sub, and the final expression rounds sub * (1 - rate) to cents.
=LET(sub, A2*B2, rate, IF(sub>=500, 0.12, IF(sub>=200, 0.05, 0)), ROUND(sub*(1-rate), 2))Variable naming rules
Choose descriptive variable names like sub, qty, or rate that do not collide with valid cell coordinates such as A1, C5, or R1C1.
=LET(x, A2, y, B2, x*y)Try the example
Use the original values or change them to test your understanding.