LET

What it does

LET(name1, value1, [name2, value2], …, calculation)

LET(name1, value1, [name2, value2], …, calculation) names intermediate values inside one formula, then returns the final calculation. Each later binding and the final calculation can use earlier names. Microsoft allows up to 126 name/value pairs.

=LET(price,100,tax,0.15,price*(1+tax)) calculates 115. The names belong to that expression; they do not create workbook names or fill cells.

Try it

Try =LET(x,3,x*x), then =LET(x,3,y,x+2,x*y). The second example uses an earlier binding to calculate the next one. For a reusable calculation, put LET inside a LAMBDA.

Quirks

The last argument is an expression to return. Ending with a name/value pair leaves no final calculation. Names must satisfy Excel's naming rules; names that conflict with reference syntax can be refused. A short name such as c can conflict with R1C1 notation.

An inner LET can shadow an outer name. A lambda created in a LET may use its enclosing names; those names must remain meaningful when the callable is invoked. Worksheet references and volatile expressions make the timing of evaluation observable, so a mathematical substitution is not by itself a compatibility rule.

How to implement

Parse LET as a binding form, preserving the name tokens and the unevaluated expressions. Build a lexical scope and add bindings in order. A binding's expression resolves names against the scope that exists before that binding, while subsequent expressions see it. Evaluate the final expression in the resulting scope and return its value unchanged.

Keep local bindings separate from workbook-defined names and from cell references. A cached binding must preserve value kind, array shape, reference identity and callable environment. Measure evaluation of unused and repeated volatile bindings before choosing between eager binding and evaluation on demand. LET's advertised benefit is avoiding repeated calculation; that does not settle every unused-binding case.

Rust code

/// `LET(name1, value1, [name2, value2, ...], calculation)`: each value is stored in a local
/// of its own, in order, its name in scope for the values after it and for the calculation.
/// Every value runs, whether or not a later expression uses it.
fn let_call<'t>(
    &mut self,
    span: TextSpan,
    arguments: &[&'t SyntaxNode],
    tail: bool,
    block: usize,
    place: Place,
    tasks: &mut Vec<Task<'t>>,
) -> Result<(), CompileError> {
    let argc = arguments.len();
    if argc < 3 || argc.is_multiple_of(2) {
        return Err(CompileError::Arity {
            span,
            supplied: argc,
        });
    }
    // Where a legacy formula's LET intersects is not measured: refused there.
    let _ = place;
    let inner = Place::Unknown;
    let pairs = argc / 2;
    tasks.push(Task::Unbind(pairs));
    // LET's result is not adjusted: `=LET(a,1,(1+7*POWER(2,-52))-a)` is 7*2^-52 (dc-b1l).
    let _ = tail;
    tasks.push(Task::Lower {
        node: arguments[argc - 1],
        tail: false,
        block,
        place: inner,
    });
    for pair in (0..pairs).rev() {
        let name = self.declared_name(arguments[2 * pair])?;
        if name.1 {
            return Err(CompileError::Syntax {
                span: arguments[2 * pair].span(),
            });
        }
        tasks.push(Task::Declare {
            block,
            name: name.0,
        });
        tasks.push(Task::Lower {
            node: arguments[2 * pair + 1],
            tail: false,
            block,
            place: inner,
        });
    }
    Ok(())
}

No implementation has been published with a Handbook test suite for this function.

Version differences

Microsoft lists LET for Microsoft 365, Excel 2021 and Excel 2024 on Windows and Mac. There is no build-by-build comparison of evaluation timing recorded here.

Alternatives

Use LAMBDA to define a function with arguments for later calls. Workbook-defined names provide a wider scope. Helper cells expose intermediate results in the worksheet when that makes a model easier to inspect.

Named variants

No named variants are recorded yet.