 # Announcing LET

By
Published Mar 17 2020 02:19 PM 36K Views

# Announcing LET

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:

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
• 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.

 Unfiltered Data Filtered Data  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))

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

That is great addition to modern Excel. By the way, with such technique formula formatter is even more demanded

``````=LET(
filterCriteria, “Fred”,
filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),
IF(ISBLANK(filteredRange),"-",filteredRange)
)
``````

Hi

I'm not sure if it is such a progress.

If it is a complex formula I would use a helper column.

If it is a test for "equals zero" I would use IFERROR(1/(1/number),value_if_error).

Für alle, die die neue Funktion ausprobieren wollen: In der deutschen Excel-Version heißt diese Funktion SEI.

(If you are using a German Excel and want to try the new function: LET has been translated to SEI in the German Excel version.)

I just used inside the filter function with Boolean logic  and it worked like a charm. This is a great addition to the excel formulas

=LET(ZeroValue,ISBLANK(\$H\$18),FILTER(C22:F33,(ZeroValue+(\$D\$22:\$D\$33=H18))*(ZeroValue+(\$E\$22:\$E\$33=\$J\$18))))

Brilliant - Similar to Variables in DAX.

The Formula team has show enormous vision in the last few months starting with the release of Dynamic array formulas.

I which some of the vision would rub off on the Power Query and Power Pivot team - which are basically hibernating

With a little bit more vision this would be ever more brilliant

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

IFBLANK(filteredRange),"-"))

Oh by the way IFBLANK is already an idea on uservoice

Regards

Sam

What a great new function. I just demonstrate how to use the new LET function to model... Check this out in my blood link below

https://www.exceljetconsult.com.ng/home/blog/modelling-using-new-let-function/

I have been waiting for this for FOREVER!!! Thank you so, so, so, so very much!!!

@Ute Simon With new functions we go live with an initial translation and then update translations based on community feedback. We've already received some feedback from German language users that they prefer the English LET instead of SEI as this is a programming term. What are your thoughts?

Interesting; this is what I normally achieve by using a sequence of named formulas.  This has the advantage that, what is in reality a nested formula, appears as a sequence of statements on the worksheet rather than hidden away in name manager.  To be readable it really needs the alt+enter carriage return as @Sergei Baklan suggests.  I wonder whether an alternating pattern of separators might help, e.g.

```= LET(
salesData := Sales[[Region]:[Profit]],
modifiedSalesData := IF( ISBLANK(salesData), "-", salesData ),
FILTER( modifiedSalesData, Sales[Rep]=SelectedRep )
)```

Perhaps that is too drastic since both colon and equals have existing meanings.  A less drastic variation might be to alternate comma and semi-colon as if building an array constant (semi-colon and backslash in mainland Europe).

I had a quick practice with the function and came to the conclusion that, for me, the most effective way to build a multi-element formula was to start with a single formula and once that checks out, to name it and echo it

```= LET( salesData, Sales[[Region]:[Profit]],
salesData
)```

so allowing the overall formula to be built and tested one line at a time.

Awsome.. just done with my YouTube Video demonstration with 4 different example.

Keep it up.. Good work team

One Suggestion to have different color icon for the variable.

If we have name range with the same variable name with different color will easy to to identify the variable. Regards, Faraz Shaikh

If D is the name of a Rectangular Range with r Rows and c Columns

then = INDEX(D,X,Y) converts it into a single column vertical array with  r x c  elements

and UNIQUE(INDEX(D,X,Y)) - would give a list of unique values from a rectangular range

where X = 1 + MOD( SEQUENCE( ROWS(D)*COLUMNS(D)) - 1, ROWS(D))

and  Y = = 1 + QUOTIENT( SEQUENCE( ROWS(D)*COLUMNS(D)) - 1, ROWS(D) )

(X, Y - Inspired from @Peter Bartholomew  Brilliant formula in the comments of  the post announcing XLOOKUP)

And Now with the new LET

=LET(

Num,SEQUENCE( ROWS(D)*COLUMNS(D)) - 1,

Den, ROWS(D),

X , 1 + MOD(Num,Den),

Y, 1 + QUOTIENT(Num,Den),

INDEX(D,X,Y)

)

Fantastic !!

Sam Nice application of LET; it brings the elements of the formula together so that they may be viewed as a single logical entity.

The example

= LET(filterCriteria, “Fred”,

filteredRange, FILTER(A2:D8, A2:A8=filterCriteria),

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

troubles me.

It has many features of a programming approach to the solution; the downside being that it makes the process inaccessible to most end users, though professional developers should welcome it.  Yet, at the same time, one sees

FILTER( A2:D8, A2:A8=filterCriteria)

which includes direct cell referencing.  If one is encouraging developers to use a programming style to create solutions, is it a good idea to uses techniques that would be condemned as poor practice in a programming environment?  There, one is expected to declare all variables prior to first usage [Option Explicit if one is thinking VBA].  I advocate eliminating all direct referencing in favour of defined names, in order introduce a level of prior declaration that prevents cells that do not form part of the model from being referenced.

What may come back to haunt us is Dan Bricklin's comment on VisiCalc:

'It would be possible to do things the programmer's way, but that would be tedious'

Awesome. Now give us the ability to truly format long Excel formulas similarly to how we can in Power BI for long DAX formulas. Linefeeds and indentation are essential to formulas being readable and editable.

For any masochist out there, the following works for me

```= LET(
grp, {0;1;2;3},
N, SUBSTITUTE( TEXT( Convert[@Value], REPT(0,9)&".00" ),".","0"),
H, VALUE( MID( N, 3*grp+1, 1) ),
T, VALUE( MID( N, 3*grp+2, 1) ),
U, VALUE( MID( N, 3*grp+3, 1) ),
H.txt, IF( H, INDEX( Nums, H+1 ) & " Hundred and ", "" ),
T.txt,  IF( T>1, INDEX( Tens, T+1 ) & IF( U>0, "-", "" ), " " ),
U.txt, IF( (T+U),  IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ) ),
CONCAT( IF( H+T+U, H.txt & T.txt & U.txt & Denom, "" ) )
)```

This evaluates as a relative reference formula but also relies upon some named array constants

```Denom	= {" Million, ";" Thousand and ";" Dollars ";" Cents"}
Nums	= {"","One","Two","Three","Four","Five","Six","Seven","Eight"," Nine"}
Teens	= {"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}
Tens	= {"","Ten","Twenty"," Thirty"," Forty","Fifty","Sixty"," Seventy","Eighty","Ninety"}```

What the formula does is convert Dollar amounts up to \$ 1 Billion to text.

@Peter Bartholomew , excellent!

Let me combine all together

``````=LET(
Denom, {" Million, ";" Thousand ";" Dollars ";" Cents"},
Nums, {"","One","Two","Three","Four","Five","Six","Seven","Eight"," Nine"},
Teens, {"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},
Tens, {"","Ten","Twenty"," Thirty"," Forty","Fifty","Sixty"," Seventy","Eighty","Ninety"},
grp, {0;1;2;3},
LET(
N, SUBSTITUTE( TEXT( A1, REPT(0,9)&".00" ),".","0"),
H, VALUE( MID( N, 3*grp+1, 1) ),
T, VALUE( MID( N, 3*grp+2, 1) ),
U, VALUE( MID( N, 3*grp+3, 1) ),
H.txt, IF( H, INDEX( Nums, H+1 ) & " Hundred ", "" ),
T.txt,  IF( T>1, INDEX( Tens, T+1 ) & IF( U>0, "-", "" ), " " ),
U.txt, IF( (T+U),  IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ) ),
CONCAT( IF( H+T+U, H.txt & T.txt & U.txt & Denom, "" ) )
)
)``````

That takes it a step further!  I must confess, I haven't got as far as thinking through the implications of nested LETs or allowing Names to refer to LET formulas.  The formula (I hesitate to say 'final' in case someone takes it in a new direction) reads more as program module than a normal Excel calculation.

I wonder how many times the text-formatted number N would be calculated were it not for the LET function.

Awesome formulas @Peter Bartholomew  @Sergei Baklan . Much of the VBA code i used to write can now be stripped out with the addition of this one function!

It'd be great to be able to define formulas as custom functions eg NumberToText( Value, Currency ) like in PQ and improve the Evaluate formula tool to make it easier to handle longer formulas.

@lori_m , yes, it will be great to have at least more advanced editor and more advanced debugger rather than current formula bar and Formulas Evaluate. I hope functions will be the next step.

Buen Día @lori_m  .
Sobre la función LET().

Buen Día. Sobre la función LET(). ¡Por favor les suplico que añadan un primer argumento NombreDeLaFormula! LET(NombreDeLaFormula;Variable;Valor;...;Formula) De esta forma sería posible hacer referencia a ella en otras fórmulas por su nombre y además da una pista imprescindible para poder entender mejor de que va dicha fórmula sin tener que asignar un nombre a la referencia. Con ello quedaría "casi" perfecta. =LET(VolumenParalelepipedo;x;VarAncho;y;VarLargo;z;VarAlto;x*y*z) =LET(x;VarAncho;y;VarLargo;z;VarAlto;VolumenParalelepipedo;x*y*z) @srdobrais @Sergei Baklan  Yes, function names together with arguments do seem to be the next logical step however one wants to achieve it - ideally with some extra supporting tools.

One possible method for converting formulas to functions at present is to place inside a RETURN function on a macro sheet substituting range inputs for ARGUMENT definitions (and noting macro sheets use implicit intersection evaluation).  See eg @lori_m Great discussion though I would strongly advise against relying on Macro Sheets in new workbooks, they are there for backcompat reasons only. Macro Sheets are not supported on all endpoints and we will not be extending them to deal with new Excel constructs.

Since Excel 2000 there exists a function called BHATTEXT to convert numbers to words but throw the output in Thai language !!

so = BAHTTEXT(101)  gives  หนึ่งร้อยเอ็ดบาทถ้วน

Is it possible to have a similar function to return the output in English, so that we don't have to use VBA to create UDF's / do formula gymnastics with LET... Simply say = TEXTNUMBER(101) = "One Hundred One"

Can this be achieved without submitting an Idea and collecting votes ?

Best Regards

Sam

@Joe McDaid  Thanks for the caveats and agree with Sam on TEXTNUMBER, MS Word has something similar too

For now perhaps adopting a convention with function arguments first and function definition last would help with readability. So the example given by @srdobrais could be translated to:
```=LET(
length, L,
width, W,
height, H,
volume, length * width * height,
volume
) ```
```=LET(
length, L,
width, W,
height, H,
volume, length * width * height
) ```

The last parameter "volume" is unnecessary to repeat.

This LET function is awesome!

However, I am worrying that it might make the debugging harder, since Excel formula does not have "Option Explicit" options.

Please consider to warn the user strongly whenever they are using undefined names.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/40021705...

Good day.
Dear @aoki_taichi , we cannot blame any program for our own mistakes, nor pretend that they can correct them for us.
If when we create a formula the result is incorrect, the first thing to consider is the possibility of having made a mistake.
The purpose of the LET () function is precisely that, to be able to see in a transparent way what we write or say a formula, and detect these inconsistencies.
So my suggestion would help, because declaring the "name" of the calculation gives us a much clearer view.
Greetings.

I remember first discovering that a named formula could form part of a sequence that would be evaluated by Excel as a multiply nested formula but which could be listed to appear as a program segment.  It took me a while to commit to this process and repackage intermediate array formulas, then appearing in helper ranges, as named formulas.  It seemed almost sinful, flying in the face of all the 'tips and tricks' so earnestly published as the right way to use Excel.  Despite that, it has worked just fine for me over a number of years.

Now I have a further choice.  I can strip out all of the intermediate named formulas that I use as building blocks to create solutions and repackage them using LET.  The question is 'how far should I go with this?'  Is 10 lines (19 parameters) OK; what about 20 lines?  When I have created a working solution, how easy would it be to find someone to maintain it?

As a further example, I draw from a discussion I created on Chandoo

```= LET(
dutyTable, SIGN(dataRange="x"),
m, ROWS(dutyTable),
n, COLUMNS(dutyTable),
k, SEQUENCE(m*n),
RNum, 1+QUOTIENT(k-1, n),
Cnum, 1+MOD( k-1, n ),
criterion, INDEX(dutyTable,RNum,Cnum),
combinations,
IF( order, INDEX( employee, RNum), INDEX( weekday, Cnum ) ),
dutyList, FILTER( combinations, criterion,"null"),
SORT( dutyList )
)Order is a range containing {0,1} or {1,0} used to order the columns and so change to effect of SORT.```

It is another unpivoting exercise, this time matching employee names against shifts by entering 'x' into a crosstab array.  The output is a list, sorted by employee or by date of the shift.

Is this a good use of LET or is it simply taking the idea too far?

Is there already a list of the equivalent function name for other languages? I'd like to test it on my italian Excel 365 version (already configured for insider updates).

@Sergei Baklan Thanks for the link, sadly it's just a translation of the standard help page and states that the function is LET for the italian version too while it isn't. The good news is that in the meantime I managed to get from someone else a workbook that used that function and found out the italian name is DEFINISCI so I've already started playing with it

This is what we really need in a long time!

But this is a new indication that Microsoft is actually intending to abandon VBA and deprecated it in favor of Javascript, as they prefer to call this function (Let) instead of (Dim).

FYI
Let: is a new Javascript keyword introduced in the (ECMAScript 2015 Update) used to define variables in a block scope, and it's now in Excel used to define variables in formula scope.

``````= LET(
\0, "This formula aggregates depreciation over expenditure from different years",
depr.year, SEQUENCE(depreciationPeriod),
depr.array, IF( period#>depr.year, INDEX(CAPEX, period# - depr.year), 0 ),
\1, "This line requires SUMCOLS from Charles Williams's SpeedTools",
depreciation, IFERROR(
depreciation )``````

Strictly speaking, they are not comments at all; they are assignments of text to local names \0 and \1 (both valid Excel Names).

p.s. I recorded a video showing how the formula was built (about 9:50)

https://youtu.be/sHkXZZz8ANs

Would anyone care to speculate why period, "." has ceased to be a valid part of a formula-local name within the LET function despite being accepted as a defined Name. Thus

``````= LET(
mission.cost, (mission.duration - 1) * overnight.rate,
mission.cost )``````

is now invalid whilst

``````= LET(
mission_cost, (mission.duration - 1) * overnight.rate,
mission_cost )``````

appears to be OK.  Out of interest names such as those in the following weighted sum are OK

= LET( x̅, SUM(α*x̂) / SUM(α), x̅)

@Peter Bartholomew That might be related to linked data types where a dot is a reference operator for selecting a member as in ref.Price or ref.Description. Disallowing the dot in local names could avoid ambiguity while sheet and workbook names still allow dot for compatibility.

I can well see why MS might think of reserving the syntax for rich data types (when used for a name I have seen the notation referred to as "faux-OO").  Since it can be used in defined names, though, the cat is already (partially)¹ out of the bag.  For example, I can use 'MS.tech' as a name for a cell containing "Microsoft Corp (XNAS:MSFT)", in which case

= MS.Tech.[Change]

works fine despite its hybrid syntax.

I need to check, but I think the cell-localised names that I introduced within LET functions before the change still work.

¹ I say 'partially' because many Greek letters, superscripts, combining characters, marks such as ¶, work but have never received formal blessing as valid characters within a name.

Just checked and entries like MS.Tech raise a #FIELD! error when entered as a return of the LET function. Perhaps noteworthy that the TYPE function distinguishes rich data types as a new enum.

Anxiously awaiting LET() in the Insider Slow/Current Channel (Preview) channel. I would have thought it would be out by now. Seems XLOOKUP went from insider fast to insider slow quicker than LET() has.

And vote for the ability to have structured formatting for formulas. LET() just lends itself to this.

Hey @Ed Hansberry! I wanted to let you know that we are indeed in the Insiders Slow/Current Channel (Preview) rings and rolled out 3 weeks ago. We are currently flighted to a portion of the audience though so I would update to the latest build, give it a shot and see if you get the flight!

Thanks @chgross - It says I am on the latest when I try to update, but still no LET.  @Ed Hansberry that's a bummer

Stay tuned in the coming weeks as we continue to increase our rollout. I will comment here once I have more I can share.

@Ed Hansberry , I had it some ago on Current (Preview). It looks like you didn't win 50/50 deployment lottery.

Thanks @chgross & @Sergei Baklan

I'll keep checking. Anxiously awaiting this. Very cool function!

@chgross - was the rollout for LET() paused? Two months later and none of the tenants I am in that are on the Preview channel have it yet. One of my Excel versions is 2009 build 13231.20152.

@Ed Hansberry it has not been paused! We did find some nasty bugs though in the insider rings which we needed to fix. As of writing this mail we should be completely flighted to all of Insiders so I would give it one more shot

Thanks @chgross - I just closed and reopened Excel and there it is, that beautiful LET() function!

Thanks again.

I look forward to seeing what you get up to with your new found freedom.

There are times when I wonder how far to push the idea.  For example, in response to a recent question on Chandoo I posted:

```= LET(
criterionRange, IF(option="Speciality", PaidVisit[Speciality], PaidVisit[Doctor Name] ),
criterionValue, IF(option="Speciality", [@Speciality], [@[Doctor Name]] ),
previousDate,
MAXIFS( PaidVisit[Bill Date],
PaidVisit[Bill Date], "<="&[@[Bill Date]],
PaidVisit[Patient No.], [@[Patient No.]],
criterionRange, criterionValue ),
elapsed, IF( previousDate, [@[Bill Date]] - previousDate, "First visit" ),
IF(elapsed<=7, "No fee", [@[Gross Amount]]-[@[Discount Amount]]) )```

The first lines just set one of the criterion ranges for the MAXIFS.  I then determine the last matching visit by a patient to establish whether the visit is a follow-up.  I then went on to calculate the days elapsed between the last appointment and tested it to determine whether it is within one week.  If so the fee is waived.  This would normally require a couple of helper ranges to make the formulas manageable but, with LET, the formula remains readable.

I think it may be a while before any consensus is achieved regarding best practice.

BTW. I confirmed that support for the "." within formula names had been intentionally withdrawn.  The formulas I wrote before the cut-off still work but there is no chance of writing similar formulas now.

The first two lines of the LET function work when defined within the function but not as defined names.  When the defined names are used within the MAXIFS function the criterion and value ranges truncate to a single cell.  To get round the problem, requires parameters of the form

IF(1, criterionRange), IF(1, valueRange),

Any thoughts on what is going on?

Interesting... seems you have identified a bug associated with defined names containing a condition not spilling.

For example, if Array:=IF(1,{1,2}) then =Array doesn't spill but =IF(1,Array) does.

This doesn't appear to happen with other related formula constructs like IFS or INDEX.

Thanks @Peter Bartholomew @lori_m for the report, we'll take a look.

@Peter Bartholomew , "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.

Version history
Last update:
‎Jul 23 2020 11:10 AM
Updated by: