LET: Naming the Steps Inside a Formula

AnalyticsDecision SupportModelling & Forecasting7 min readMicrosoft 365 for Windows

The problem

Everyone who has inherited a workbook has met a formula like this one, which calculates a rebate from a list price, a volume and a channel:

=IF(C2>=$H$2,IF(D2="Hospital",B2*C2*$H$3,B2*C2*$H$4),IF(D2="Hospital",B2*C2*$H$5,B2*C2*$H$6))

It works. But to understand it you need thirty seconds and a piece of paper. The sales value B2*C2 appears four times, the channel test twice, and the logic lives in the nesting: a condition inside a condition. Real models are worse, with formulas of two hundred characters and three levels of IF, the territory of errors found on a Monday morning.

LET brings into Excel an idea that exists in every programming language: giving a name to an intermediate result and using the name instead of repeating the calculation.

How LET works

The syntax is a list of name and value pairs, followed by a final calculation that uses the names:

=LET(name1, value1, [name2, value2, ...], calculation)

Each value can use the names defined before it, so a long calculation becomes a sequence of small steps, each with its own label. The rebate formula becomes:

=LET(sales, B2*C2,
     big, C2>=$H$2,
     hosp, D2="Hospital",
     rebate_rate, IF(big, IF(hosp,$H$3,$H$4), IF(hosp,$H$5,$H$6)),
     sales*rebate_rate)

The line breaks, inserted with Alt+Enter while editing, are optional, but they turn the formula into something close to a specification: the sales value, the two tests, the rebate rate, the result. The Nested vs LET sheet has both versions side by side, and a check column confirms that they return the same rebate for every order.

Same rebate, two formulas: the LET version names each step, and column G confirms every result matches.

Three properties make LET more than a cosmetic change.

Each name is calculated once. If sales is used four times, Excel still computes B2*C2 only once. With small formulas the gain is invisible; with a lookup over thousands of rows used three times in the same formula, it is real.

A change is made in one place. If the rebate base changes from gross sales to sales net of returns, only the line that defines sales changes.

The logic is visible. Whoever opens the model next year reads the steps instead of reconstructing them.

LET accepts up to 126 name and value pairs. Names must start with a letter and must be valid as names in the Name Manager, which excludes anything that Excel could read as a cell reference. This produces two small surprises: tax1 is not a valid name, because TAX1 is a real cell (column TAX exists), and neither are c and r, which Excel reserves for the R1C1 notation. Descriptive names such as net_price or NetPrice avoid both problems and make the formula self-explanatory. It also pays to avoid names that coincide with Excel functions, such as rate, price or n: they can confuse the reader, and in some cases Excel itself. Short names are fine only when the meaning is obvious, such as x in a purely mathematical expression.

A safer break-even

The break-even volume of a launch is the fixed cost divided by the unit contribution margin, the difference between net price and variable cost per pack. The classic formula is short:

=B2/(B3-B4)

It has two weaknesses. The contribution margin, the key concept, does not appear anywhere. And when the margin is zero or negative, the formula returns either #DIV/0! or a meaningless negative volume. With LET, on the Break-even sheet:

=LET(fixed_costs, B2,
     net_price, B3,
     unit_cost, B4,
     margin, net_price-unit_cost,
     IF(margin>0, fixed_costs/margin, "No break-even: margin <= 0"))

The margin now has a name, and the formula defends itself. The second scenario on the sheet has a variable cost above the net price, and returns the message instead of an absurd number.

Calculate once, use many times

LET also works well when one value feeds several results. On the Reuse sheet, the base net sales are revalued for the expected price increase, and the revalued figure drives three indicators: the commercial target (95% of it), the bonus threshold (90%) and the stretch target (110%). Without LET, the expression B2*(1+B3) would be written three times. With LET and HSTACK, a single formula returns all three, side by side:

=LET(revalued, B2*(1+B3),
     HSTACK(revalued*B4, revalued*B5, revalued*B6))

The result spills into three cells. If the revaluation rule changes, one line changes.

LET as a debugging tool

A long formula that returns an unexpected value is hard to inspect, because the intermediate results are invisible. With LET, the fix is simple: temporarily replace the final calculation with the name of the step you suspect. Ending the break-even formula with margin instead of the IF shows the margin; ending it with net_price shows what Excel is really reading as the price. Once the faulty step is found, the final calculation goes back. The Break-even sheet includes a copy of the formula that returns the margin, to show the idea.

The same trick can turn a formula into a checklist: define each check as a name (check1, check2, check3) and end with check1&" | "&check2&" | "&check3 to see at a glance which one fails.

Anatomy of a modern formula

LET shows its full strength when it combines the other modern functions. The Forecast sheet holds a five-year projection of net sales built with a single formula. The inputs are the first year in B2, the number of years in B3, a default growth rate in B4 and the starting net sales in B5. A small table in H3:I9 lists the growth rate expected for specific years; years not in the table use the default. The table sits to the right of the output on purpose: the formula spills into five columns, A to E, and anything placed in that area would stop it with a #SPILL! error.

=LET(num_years, B3,
     years, SEQUENCE(num_years, 1, B2, 1),
     rates, IFNA(XLOOKUP(years, H3:H9, I3:I9), B4),
     closing, B5*SCAN(1, rates, LAMBDA(acc, g, acc*(1+g))),
     opening, closing/(1+rates),
     HSTACK(years, opening, closing-opening, closing, rates))

Read aloud, the formula is a specification. Generate the requested number of years starting from the first year. For each year, find its growth rate in the table, or use the default if the year is not listed (IFNA catches the "not found" result). Compound the rates year after year (SCAN keeps every intermediate result) and apply them to the starting value. Derive the opening value of each year. Return five columns: year, opening, growth, closing, rate.

The output is a table of five rows and five columns that spills from one cell. Change B3 from 5 to 8 and the table grows by three rows; nothing else needs to be touched, as long as the cells below and beside the output stay empty. This is the declarative way of building a model: describe what must be calculated, and let the formula size itself.

The forecast table spills from A8; each line of the LET formula is one step of the projection.

Two notes of realism. First, a formula like this is excellent for a small, self-contained projection and for parts of a model that change often; the core financial calculations of a large model can stay in classic, cell-by-cell form, which is easier for most colleagues to audit. Second, LET requires Microsoft 365, Excel 2021 or Excel 2024. A colleague on Excel 2019 will see #NAME?, so check who will open the file before building on it.

Common mistakes

Choosing names that look like cell references. tax1, q2 or ab12 are cells, not names; c and r are reserved. Excel refuses the formula.
Using a name before it is defined. Each value can only use the names above it in the list.
Forgetting the final calculation. The last argument must be a calculation, not another name and value pair, or Excel reports an error.
Putting anything in the cells where a LET formula spills. Excel returns #SPILL! until the area is empty again.
Copying LET formulas into older versions of Excel. The file shows #NAME? in Excel 2019 and earlier.
Rewriting every formula with LET. A two-step calculation does not need names; LET pays off when an expression is repeated or the logic has several steps.