LET() assigns names to calculations in Excel

, , , ,

Let() is a new function in Windows and Mac versions of Excel 365 and Excel 2021 (plus Excel Online) which is popular with makers of long, complicated functions.

The function lets you name a temporary variable to use within a formula.  That might seem a strange thing to do but it’s a godsend for advanced Excel formulas.

Longer Excel formulas often have repetition, the same calculation is done many times. Or, in other words, the same sub-formula repeats.

For example, nested IF statements usually have the same test repeated many times.

=IF(B1>INT(SUMPRODUCT(A2:A4)),"Higher",IF(B1<INT(SUMPRODUCT(A2:A4)),"Lower",IF(INT(B2=SUMPRODUCT(A2:A4)),"Equal")))

Has the calculation INT(SUMPRODUCT(A2:A4) done three times.

The repetition makes the whole thing harder to read and easier to mess up.  Such as accidentally changing one of the sub-calculations and not the others.

Repeating formula strings have to be separately processed by Excel which is a waste of computer resources.  That’s a minor issue in the above example but imagine a big worksheet with hundreds or thousands of similar statements, all being individually calculated.

Introducing LET()

LET fixes those problems by telling Excel to calculate a formula once, give it a name, then use that name many times in the cell formula.

In it’s simple form, LET() looks like this:

LET(<variable name>,<value or calc for the variable>,<main formula for the cell>)

a very simple example could look like this:

LET(pi, 22/7,pi*(Radius^2))

You can add more pairs of variable & value like this:

=LET(pi, 355/113,e,2.718282,pi*(Radius^e))

(these are just examples, in practice you’d use PI() and EXP(1) for those values).

Microsoft has a little graphic to explain

Source: Microsoft

LET can have up to 126 pairs of name and variable.

Here’s the same formula that we started with, this time using LET()

The calculation INT(SUMPRODUCT(A2:A4)), is only done once and named iX.

=LET(iX,INT(SUMPRODUCT(A2:A4)),IF(B1>iX,"Higher",IF(B1<iX,"Lower",IF(B1=iX,"Equal"))))

Changing the test now needs only a single change. Excel only evaluates ‘ix’ once instead of three times.

What’s new?

Hang on, I hear you cry?  We can do LET already by putting the calculations in another cell, giving that cell a name then using the name in the calculation.

That’s true, our example could be done like this:

  • Enter the formula =INT(SUMPRODUCT(A2:A4)) into a cell.
  • Name that cell ‘iX’
  • In the main cell use a formula that references the named cell: =IF(B1>iX,”Higher”,IF(B1<iX,”Lower”,IF(B1=iX,”Equal”))))

That gives you the same result by a similar method.  It has the advantage of working for multiple cells and in earlier versions of Excel.

However, it’s less efficient than using LET() within a single cell.

LET is self-contained and readable without reference to other cells. Generally speaking, if the same calculation is being done within many cells, it’s better done once in a single named cell.

Calculations repeated within a single cell can use LET().

Lambda – strange name for a really good extra in Excel
Microsoft fixes a big hassle with Excel’s great Lambda()

Track COVID-19 stats in your own Excel spreadsheet
Cool Excel Year Planner now available for download

IF and Nested IF Statements in Excel

More Excel alternatives to nested IF statements

SumIF, a better way to Sum in Excel