Let() is a new function coming to Excel 365 which will be popular with makers of long, complicated functions.
The new 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.
Has the calculation INT(SUMPRODUCT(A2:A4)) done three times.
The repetition makes the whole thing harder to read and easier to mess up. 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.
LET fixes those problems by allowing you 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:
You can add more pairs of variable & value like this:
(these are just examples, in practice you’d use PI() and EXP(1) for those values).
Microsoft has a little graphic to explain
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.
Changing the test now needs only a single change. Excel only evaluates ‘ix’ once instead of three times.
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 also 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().
LET() is currently only available for Fast track Insiders with Excel 365 for Windows. It’ll gradually be rolled out to other Insiders and eventually public releases.