Announcing LET (original) (raw)

Blog Post

Have you ever had to repeat the same expression multiple times within a formula, created a mega formula or wished that you had a way to reuse portions of your formula for easier consumption? With the addition of the LET function, now you can!

Introducing LET

LET allows you to associate a calculation or value in your formula with a name. It's names except on a formula level.

The main benefits are:

1. Readability

No more having to remember what a specific range/cell reference referred to, what your calculation was doing or duplicating the same expression within a formula. With the ability to name expressions, you can give meaningful context to readers of your formula.

2. Performance

If you reuse the same expression multiple times in a formula, Excel calculates that expression multiple times. LET allows you to name the expression and refer to it using that name. Any named expression is calculated only once, even if it is referred to many times in the formula. This can significantly improve performance for computationally complex expressions.

The function definition for LET works as follows:

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

• name1: The name for the 1st value
• value1: The value to associate with the 1st name
• name2 (optional): Additional names
• value2 (optional): Additional values
• calculation: The calculation to perform. This is always the final argument and it can refer to any of the defined names in the LET.

Deconstructing the parameters, there are two things to make note of

1. The names and their values must be in pairs.

For example:

=LET(total, SUM(A1:A10), total * 3).

In this case, i) total and ii) SUM(A1:A10) are a pair.

Taking this one step forward, if we wanted to add another name, we just need to define a new pair...

=LET(total, SUM(A1:A10), count, COUNT(A1:A10), total / count)

1. The last parameter of the function is the calculation which can use the values you named. A properly structured LET will have an odd number of arguments.

In the prior cases this is “total *3” or “total / count”

Example

Suppose you have some raw sales data, and you'd like to filter that data to show one person, and add a dash to any blank cells.

This formula can be authored traditionally using the following formula. However, this formula suffers from a common occurrence where you have to make use of the same expression twice, in this case it’s the FILTER expression.

By using a LET we can abstract on this value and even add an addition to fix up the criteria, which in this case is “Fred” but you might want to change to “Amy” in the future or point it at a cell reference.

The other thing to make note of is precedence when referencing names; Name definitions can only make use of prior and not subsequent names. For example, you can see that filterCriteria is able to be used by the filteredRange name definition because it comes afterwards.

An added benefit of using a LET in this formula is that Excel will calculate this formula 2x as fast with the LET because Excel does not have to repeat the same calculation.

Original Formula

=IF(ISBLANK(FILTER(A2:D8,A2:A8="Fred")),"-", FILTER(A2:D8,A2:A8="Fred"))

Formula using LET

=LET(filterCriteria, “Fred”, filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),

IF(ISBLANK(filteredRange),"-",filteredRange))

Learn More

To learn more about LET, please check out our help article.

LET Help

Availability Notes

LET is now available to Office 365 Subscribers in the Insiders Channel (Beta and Current Channel Preview) and will be available to users of other channels later this year. I’ll update this blog as LET becomes available to more channels.

In the meantime, please provide feedback either in the comments below or through our normal channels and please note the function signature is subject to change based on feedback before moving to further rings.

To stay connected to Excel and its community, read the Excel blog posts and send us ideas and suggestions via UserVoice. You can also follow Excel on Facebook and Twitter.

Chris Gross
Program Manager, Excel

Updated Jul 23, 2020

Version 10.0

Excel Blog

Subscribe to the Excel Blog to get the latest product announcements and updates

59 Comments

• 
  PeterBartholomew1
  In this proposal, the LET function is being used to pass a function input using DEF (sometimes referred to as an anonymous function) and the MAP function then iterates over the value inputs (this is standard terminology in functional languages).
  As an example of how this LET syntax could be used in practice, suppose you want to evaluate one of three UDFs: x(Range), y(Range) or z(Range) according to an input value i = 1, 2 or 3. Either of the following formulas will do the job:
  =CHOOSE(i,x(Range),y(Range),z(Range))
  =LET(f,CHOOSE(i,x,y,z),f(Range))
  to be concrete one could define:
  Function x(range)
  x = Application.Min(range)
  End Function
  Function y(range)
  y = Application.Max(range)
  End Function
  Function z(range)
  z = Application.Average(range)
  End Function
• 
  lori_m
  Sorry, I am being a bit dim

= LET(x, {1,2,3},  
  fSquare(x))  

works fine but I am not sure I understand your example. Both the 'fSquare' and function 'sq ()' give #NAME! errors?

• 
  lori_m
  Thanks for the input. I confess that my IT background (I was raised on FORTRAN) is not sufficient to make me the ideal person to judge the relative merits of candidate syntax. What I am sure of is that there is a need to be able to pipe a sequence of values through a calculation and collect the results. This should provide a means of bypassing pairwise lifting in circumstances where it is not applicable.
  Another thought I had was to introduce the UNIX pipe operator to feed values sequentially (is that what your MAP does?), so the formula might read

= LET( p, | period,  
  INDEX(flow, p) + IF(p=1, 10000, INDEX(Balance, p-1)))  

where the operator "|" provides the instruction to break up the array. The values would be recombined into a result array when p goes out of scope.

• 
  PeterBartholomew1
  Good idea. It occurs to me that the FOR.EACH concept could be extended by splitting into DEF and MAP functions which could respectively define and execute multiple formula inputs.
  =DEF(ret, var1, var2, ...)
  =MAP(def, arg1, arg2, ...)
  Given the above, the following formula would return {1,4,9}:
  =LET(Sq,DEF(x^2,x),MAP(Sq,{1,2,3}))
  Note this appears to be feasible since LET permits functions as arguments. For instance the following formula also returns {1,4,9}:
  =LET(sq,fSquare,sq({1,2,3}))
  where fSquare refers to the vba function:
  Function fSquare(x)
  fSquare = Application.Power(x, 2)
  End Function
• 
• 
  PeterBartholomew1 , that's great approach, thank you for sharing. I played on another model, could be quite useful in some situations.
• 
  SergeiBaklan
  The idea is to build formulas that need to be evaluated row by row using relative referencing (here using the @ operator) but, rather than having a formula copied down, the function would return an array which would either spill or be an argument within a further formula (e.g. to calculate the minimum row max).
  In this case the function causes the Balance array to breakup so that it may be accumulated
  The formulas look pretty 'clunky' but that stems from the use of the INDEX function to specify an element of an array rather than introducing more mathematical or programmatic notation e.g.
  Array[ k, ] has to be rendered as INDEX( Array, k, {1,2,3} )
  Flow[p] and Balance[p-1] become INDEX(Flow, p) and INDEX(Balance, p-1) respectively.
• 
• 
  SergeiBaklan
  That is interesting. I must admit, I had wondered why the calculation does not attempt to evaluate 'formula' and 'return' within
  = LET( name, formula, return )
  before even recognising the function as being LET.
  Another function I am tempted to try to implement is
  = FOR.EACH( element, Array, Formula )
  which would run through each element of an array and evaluate the formula, once for each element value. The problem there too, is that I cannot evaluate the formula until I have evaluated at least the first element of the array.
• 
  PeterBartholomew1 , "The formulas I wrote before the cut-off still work but there is no chance of writing similar formulas now.". My understanding formula is translated into calculation chain within HTML structure of Excel file and the latest works with calc engine. Thus, once parsed, LET() formula is not parsed again with re-calculation. That's if only you re-enter it again.
  That was with dots and with length bug.