LET: name a value once and reuse it
LET gives names to values or sub-calculations inside a formula so you can reuse them and read the logic like a sentence.
=LET(rev,B2,cost,C2,(rev-cost)/rev)The sheet: Column B is Revenue and column C is Cost. You want the profit margin and want the formula to stay readable.
How it works, step by step
- 01rev,B2 defines the name rev as the value in B2.
- 02cost,C2 defines the name cost as the value in C2.
- 03The last argument is the calculation that uses those names: (rev-cost)/rev.
- 04The result is the margin as a decimal. Format it as a percentage.
The same logic in four tools
=LET(rev,B2,cost,C2,(rev-cost)/rev)=LET(rev,B2,cost,C2,(rev-cost)/rev)SELECT (revenue - cost) / NULLIF(revenue, 0) AS margin
FROM deals;df["margin"] = (df["revenue"] - df["cost"]) / df["revenue"]Mistakes that break it
- !LET needs Microsoft 365 or Excel 2021, and was added to Google Sheets later, so older files may not support it.
- !Names must not look like cell references, so rate is fine but R1 or REV1 is not.
- !A name is only available inside the LET formula that defines it.
- !A revenue of 0 returns #DIV/0!. Add IF or IFERROR if that can happen.
Common questions
- Does LET make a formula faster?
- It can, because a repeated calculation is evaluated once. The bigger benefit is readability.
- Can I name more than two values?
- Yes, add as many name and value pairs as you need before the final calculation.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.