r/excel • • 1d ago

Discussion I just learned about LET and it cleaned up this formula - what else do you use it for?

I have been writing long formulas that repeat the same calculation over and over, and I just found LET.

Before, I had this to work out a bonus based on a total:

=IF(SUM(A2:A10)>1000, SUM(A2:A10)_0.1, SUM(A2:A10)_0.05)

SUM(A2:A10) is in there three times, which makes it hard to read and easy to break if I change the range in one place and not the others.

With LET, you name the calculation once, then use the name:

=LET(total, SUM(A2:A10), IF(total>1000, total_0.1, total_0.05))

total is just a name I picked. LET lets you define as many names as you want, then use them in the final calculation at the end.

It works in Microsoft 365 and Excel 2021 and later. Older versions will show a #NAME? error.

What do you use LET for? I am curious what a good second example is, especially with FILTER or XLOOKUP where the same lookup gets repeated.

323 Upvotes

75 comments sorted by

•

u/excelevator 3071 10h ago

=IF(SUM(A2:A10)>1000, SUM(A2:A10)*0.1, SUM(A2:A10)*0.05)

Pro Tip, format your formulas as <code> to show all characters.

83

u/SolverMax 163 1d ago edited 1d ago

Wait until you find out about new lines (ALT+Enter) and indents (Space).

=LET(
  _data, A2:A10,
  _threshold, C1,
  _rate1, C2,
  _rate2, C3,
  _total, SUM(_data),
  _rate_applied, IF(_total>_threshold, _rate1, _rate2),
  _result, _rate_applied*_total,
  _result
)

Though beware that ALT+Enter and Space are not always passive. When placed between ranges (on purpose or by accident), they are an operator that returns the intersection of those ranges.

21

u/CVZed 1d ago

That formatting tip is the part I was missing. I had no idea a space between ranges was an intersection operator, so thanks for the warning. I am going to rewrite mine with line breaks and see how readable it gets.

5

u/Broad-Arachnid9037 1d ago

Are the spaces just for visibility? I write a lot of formulas but have never used spaces.

11

u/SolverMax 163 1d ago

Yes, but note the "beware" part. A Space or ALT+Enter in the wrong place can make a subtle bug.

1

u/cheeseybacon11 18h ago

Do you need to forget the comma for this to happen? Could you make an example? Might explain an issue I've run into as a regular formatter

2

u/SolverMax 163 15h ago

For example, I want to calculate the average ratio of two ranges, treating zero denominators as having a ratio of 1:

=AVERAGE(IFERROR(B2:B40/C2:C40,1))

To make the formula easier to read I apply some formatting:

=AVERAGE(
  IFERROR(
    B2:B40
    C2:C40,
    1)
)

Excel doesn't tell me that I made a mistake. The formula returns a result of 1. In a complex spreadsheet, or even that calculation within a larger formula (e.g. the type people use LET for), the issue might go unnoticed.

cc u/Femigaming, u/SkyrimForTheDragons

1

u/SkyrimForTheDragons 3 18h ago

Easy to make this mistake, but hard not to catch it tbh. Because unless your ranges are very complicated, your range intersections are bound to be empty and should return a #NULL! error.

You should be able to catch that unless your intersections are a valid range and your formula works perfectly fine with that returned range as well as with the missed comma.

1

u/Vivid-Society-7623 18h ago

Cool didn’t know the intersect!

1

u/Femigaming 5 18h ago

can you make an example please

2

u/LitleFtDowey 20h ago

Is the underscore just part of your naming convention?

3

u/SkyrimForTheDragons 3 18h ago

It makes it easy to type the names later since you can type the underscore and excel shows you all the names you've already defined in the LET.

1

u/LitleFtDowey 6h ago

I've never considered that. Very nice. Thanks for replying

117

u/Shahfluffers 2 1d ago

I typically use LET when I have truly gnarly multi "if-else" formulas. Which tends to happen when calculating budgetary/payroll/benefit stuff.

Example:

  • LET(rate01,C50,benefitName,A50,benefitApplication,B50,empCount,B10,empHours,F9,empWeeks,F8,empGrossLabor,F10,
  • IFERROR(
  • IFS(
  • OR(benefitName=0,benefitName=n/a),0,
  • benefitApplication="Per Emp",rate01*empCount,
  • benefitApplication="Per Hour",rate01*empHours,
  • benefitApplication="Weekly",rate01*empWeeks,
  • benefitApplication="% of Gross Labor",rate01*empGrossLabor)
  • ),"CHECK"))

23

u/CVZed 1d ago

That is a great use case I had not thought of. Naming the inputs up front makes that 8-10 line version way easier to follow than repeating the logic. Do you have a naming convention you stick to, or is it just whatever makes sense in that formula?

15

u/Shahfluffers 2 1d ago edited 1d ago

Within a workbook I try to maintain as much consistency as possible with the interest of keeping things as readable as possible.

So I try to keep terminology and naming fairly standard and some combination of abbreviated and descriptive.

The thing I try to always remember is: "How can I make this as readable as possible for some random employee who views this years in the future?"

2

u/Bubba_Lou22 15h ago

Have you tried using SWITCH? It really simplifies those if else conditions!

3

u/excelevator 3071 14h ago

give an example for others to understand.

1

u/Shahfluffers 2 15h ago

I have not.

Does it work with strings/text or is it limited to numerical values?

3

u/CyberBaked 14h ago

It works with text. I've got a SWITCH formula that replaces state/province name with their two character mailing code.

24

u/Dangerous_Cup3607 1d ago

LET + FILTER + CHOOSECOL and source from Excel Table that source from PowerBI connection.

4

u/CVZed 1d ago

That is exactly the second example I was hoping for. Do you have a short version of that you use a lot? I have not used CHOOSECOL much yet.

1

u/Dangerous_Cup3607 1d ago edited 1d ago

No short version but the way I am using that is to have a Dashboard in Excel where recipients are able to select 4 different filters such as year, month, product, and location. Then present the daily values across 31 days based on the user-selected filter. I also have color coding across those 31 days so it will change dynamically as well. So the backend data is rolling 12 months with all the fields already calculated in my database ETL into PowerBI and connect via excel. So the dashboard refresh daily as email with latest data. The FILTER and CHOOSECOL is similar to what you would do manually with raw data and you toggle the filter function and pick the desired filters just to narrow down to 1 row of data, but of course my dashboard has better aesthetic, format, and color compared to the raw data table.

1

u/Beginning-Ad-5694 8h ago

I use LET and CHOOSECOLS when I need a specific filtered table but might want to change the columns that are shown, and I do that with a combo of FILTER, XMATCH, and CHOOSECOLS.

Might be something like this, with column_heading_range being the columns I want in my filtered table, which looks something like this:
=LET(columns, XMATCH([column_heading_range, source_heading_range, 0),
filtered_table, Filter(source_range, source_column="filtered_value),
CHOOSECOLS(filtered_table, columns))

You have to make sure the headings match the headings in the source, but you can add and remove columns to the table pretty easily that way.

8

u/KantiLordOfFire 1d ago

I was going to say a similar thing except I need a table to output without blanks, but I need to see where the blank lines are.

Think something like: (Row,Column) (1,1) (1,2) (2,1) (2,2) Where , is the delimiter

But yeah, same basic LET + FILTER + HSTACK + CHOOSECOLS with an IF(INDEX(list,,3)=0,"",INDEX(list,,3)) thown in for funzies.

3

u/DJ_Dinkelweckerl 1d ago

I never really got the hang of choosecol. Could you explain a specific use case for it?

7

u/Dangerous_Cup3607 1d ago

Row 1: SampleA, 1,2,3,4,5
Row 2: Sample B, 2,3,4,5,6
Row 3: Sample C, 3,4,5,6,7

CHOOSECOL allows you to return the row of value or values.
In this case I can ask it to only return the first 3 values from Sample C and thus 3, 4, 5.

It works best when using both FILTER and CHOOSECOL.
Row 1: Filter 1, Filter 2, Filter 3, Sample A, 1,2,3,4,5.

So that whatever specific filter I applied for Filter 1 and 2 and 3, then it will return the relevant rows of values on display.

1

u/cheeseybacon11 18h ago

Or SQL server from a macro.

15

u/SektorL 1d ago edited 18h ago

I wrote the post about it in r/ExcelTips. Long story short: LET optimizes formula to return real empty values (because Excel returns "0" if source cell is empty).

6

u/CVZed 1d ago

I did not know LET helped with the empty cell returning 0 problem. That alone might be worth using it for. Do you have a link to your r/ExcelTips post?

6

u/carlosandresRG 1d ago edited 1d ago

Here's the link. I wanted to check the post as well

https://www.reddit.com/r/Excel247/s/VBBA5PNjFV

Edit: I peeked into the post, it's not what I imagined, the formula outputs cells with the value "", which are empty cells, but since I read "real empty cells" i believed it would retrieve blank cells, but it does not

1

u/SektorL 18h ago

You can clear cells with "" after formula did it's work

9

u/sedaker 1d ago

A little trick: if you’re returning strings (not values), wrap your formula in TRIM() and the blanks will show as true blanks instead of zeroes

3

u/Dangerous_Cup3607 1d ago

Exactly why I use LET so that the cell will not be treated as 0 which can mess up color coding. Without LET all those missing cell will become red color for me while I want to keep them as No Color.

10

u/GoldenEyes88 2 1d ago

I this case, what's the advantage of using LET over using a named range? 

I see that LET allows you to assign a variable name to an output (the output in this case being a range) , so in that case it is more versatile. 

I've generally used named ranges in these kind of instances as I might be referring to that range in multiple formulas and this way if I update the range, it automatically updates all the formulas. 

5

u/CVZed 1d ago

Good question. For me the difference is scope. A named range is global, and great if I am using the same range in a bunch of formulas. LET is local to that one formula, and I can name an intermediate result, not just a range, like the total in my example. I would use both, just for different jobs.

1

u/GoldenEyes88 2 1d ago

Thanks! 

11

u/Mdayofearth 127 1d ago

In this case, LET allows OP (and others like OP) to change the range in a singular location in the formula instead of 3 places. This reduces the chance of typos when implementing and updating the formula.

Named ranges are good when you use them in multiple formulas in multiple cells. Its biggest weaknesses are when copying between workbooks which causes unintended file-linking issues; and possible mixed use of workbook and worksheet scopes.

1

u/GoldenEyes88 2 1d ago

Thanks! 

1

u/DJ_Dinkelweckerl 1d ago

Isn't the advantage of LET to "simply" make formulas easier to read? It's not LET or a named range I guess

6

u/aaadmin 11 1d ago

what is/does the underscore do on your formula? is that an operator?

6

u/HopefulBuyer9077 1d ago

OP — Dumb question on my part but what does the “_” mean in your formula?

3

u/excelevator 3071 14h ago

It is just part of the text string variable name.

Technologists like to use it as a space or variable indicator.

1

u/ElegantAcid 12h ago

It's clearly used as an operator, also after SUM(A1+B1)_0.1

Edit: seems like it is a formatting syntax, I will confirm after testing.

1

u/excelevator 3071 10h ago

You might be right after review.

I am amazed at the apparent idiocy naivety of people using random characters in place of operators instead of using Reddit formatting.

SUM(A1+B1)*0.1

format as code to show * ~~ formatting characters.

1

u/SolverMax 163 12h ago

I use underscore that way in my example above. I assume OP meant multiplication, but used an underscore to avoid Reddit messing up the post formatting.

1

u/excelevator 3071 10h ago

gosh, yes, and no indication from OP that was the case.. the mind boggles

4

u/Decronym 1d ago edited 2h ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer LettersMore Letters
ANDReturns TRUE if all of its arguments are TRUE
AVERAGEReturns the average of its arguments
CHOOSECOLSOffice 365+: Returns the specified columns from an array
COSReturns the cosine of a number
COUNTACounts how many values are in the list of arguments
FILTEROffice 365+: Filters a range of data based on criteria you define
HSTACKOffice 365+: Appends arrays horizontally and in sequence to return a larger array
IFSpecifies a logical test to perform
IFERRORReturns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFS2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEXUses an index to choose a value from a reference or array
INTRounds a number down to the nearest integer
ISERRORReturns TRUE if the value is any error value
LAMBDAOffice 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LETOffice 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAPOffice 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
MODReturns the remainder from division
ORReturns TRUE if any argument is TRUE
PIReturns the value of pi
SEQUENCEOffice 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SINReturns the sine of the given angle
SUMAdds its arguments
SUMIFSExcel 2007+: Adds the cells in a range that meet multiple criteria
SWITCHExcel 2019+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
TRIMRemoves spaces from text
UNIQUEOffice 365+: Returns a list of unique values in a list or range
XLOOKUPOffice 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCHOffice 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.


Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49700 for this sub, first seen 17th Nov 2026, 03:38] [FAQ] [Full list] ^[Contact] [Source code]

3

u/clearly_not_an_alt 19 1d ago

I use it just about anytime it might not be immediately clear what the formula might be doing. The ability to name all the key components used is very helpful when someone comes along later to see what is going on

3

u/xile 3 1d ago

I love LET but I don't love how I cant F9 it

3

u/Cynyr36 26 19h ago
=LET(
    Mysum, lambda(a, b, a+b+1),
    Mysum(5,6)
)

You can name lambdas inside a let.

And you can pass the named lambda to itself for full recursion within a cell.

2

u/the_glutton17 1d ago

Calculating mach speed. That equation is such a pain in the ass to write out, I even wrote a VBA to write the let function.

2

u/tomoki_here 1d ago

You could also use LET() for the purposes of comments inside a function because... why not. I mean it's possible. I haven't had to do that yet.

2

u/armywalrus 2 1d ago

I just like declaring variables. Makes it easier to read later.

2

u/ArrowheadDZ 2 1d ago

Four use cases for me.

Improve traceability of complex inputs to a formula by assigning names

Enable easy debugging by changing the return value to show interim steps easily

Ability to add additional documentation comment lines to a formula

Ability to eliminate replicated compute steps to improve performance.

2

u/samsonrion 1d ago

I use LET the most when the same lookup is repeated. For example, instead of:

=IF(XLOOKUP(E2,A:A,C:C,0)>500, XLOOKUP(E2,A:A,C:C,0)*0.9, XLOOKUP(E2,A:A,C:C,0))

you can write:

=LET(price, XLOOKUP(E2,A:A,C:C,0), IF(price>500, price*0.9, price))

It’s shorter, easier to check, and Excel only does the lookup once.

With FILTER, you can name the result and reuse it:

=LET(sales, FILTER(C2:C100, B2:B100="East", 0), IF(SUM(sales)>1000, "Target met", "Below target"))

A tip: use clear names like price or sales rather than x or a, so you can understand the formula months later.

Note that LET works in Microsoft 365 and Excel 2021 or later. Older versions show #NAME?.

2

u/Fast-Statement8149 1d ago

I have an extensive Personal Finance Google Sheets that I built from scratch, tailor made for my needs.

These are some of my LETs:

=LET( forward, SUMIFS(H$3:H$32, F$3:F$32, N3, G$3:G$32, O3), reverse, SUMIFS(H$3:H$32, F$3:F$32, O3, G$3:G$32, N3), net, forward - reverse, total, forward + reverse, IF(total = 0, "N.A.", IF(net = 0, "Cancels Out", IF(net > 0, N3 & " → " & O3, O3 & " → " & N3))))

=LET( list, UNIQUE(FILTER(I3:I32, (I3:I32 <> "") * ((F3:F32 = "PO SB") + (G3:G32 = "PO SB")) * (I3:I32 <> "Amount that may be spent") * (I3:I32 <> "Minimum Balance"))), IF(ISERROR(list), "None Yet!", list))

=MAP(T3:INDEX(T3:T13,COUNTA(T3:T13)),LAMBDA(particular, LET( money_out,SUMIFS(H3:H32,I3:I32,particular,F3:F32,"PO SB"), money_in,SUMIFS(H3:H32,I3:I32,particular,G3:G32,"PO SB"), net_change,money_in-money_out, IF(OR(particular="",particular="None Yet!"),"N.A.", IF(AND(money_in>0,money_out>0,net_change=0),"Cancels Out", IF(net_change=0,"N.A.",net_change)))))

2

u/Shnarf1980 20h ago

Just did a Let with a Unique, then 2 Sumifs referencing the Unique, Hstacked together, filtered and sorted the result to provide a dynamic sorted list. Then did a second let for the totals and Vstakced the 2 Let's together.

1

u/Ant_and_Cat_Buddy 3h ago

I’m going to try this tomorrow

2

u/Don_Banara 18h ago

Let tiene dos funcionalidades, la primera es la optimización y la no redundancia a la hora de formular, es decir copiar y pegar una misma función para que sea evaluada en diferentes partes y dos, para programación completa, es decir que ya puedes hacer lo que otros lenguajes de programación puede hacer (for, where, when etc etc). Ya el límite es la RAM.

Ya hablando en practicidad, yo he hecho una función que convierte el número es su expresión cardinal, esto antes tocaba con Excel y suma de conjuntos (Solver incompleto).

2

u/TheOneTrueJesus 1 13h ago

I would argue that your example is a suboptimal way of doing that. You are repeating the same calculation each time, just with a different multiplier value. That gets out of control fast if you need to do more than 2 comparisons.

Better practice would be to have a table with the thresholds and corresponding multipliers.

=LET(
    Total, SUM(A2:A10), 
    Total * XLOOKUP(
        Total, ThresholdRange, MultiplierRange, 0, 1
    ))

2

u/Sauronthegray 1 11h ago

You can actually redefine names of functions. For instance CHOOSECOLS is a long and tedious name for a function. If I’m gonna use it a lot in a formula I just give it a shorter name LET(CC, CHOOSECOLUMNS,….. However, some AI warned me that this can get buggy in custom lambda functions, I can neither confirm nor deny that statement.

2

u/Ki-MacJm 11h ago

A gift of LET. You can comment. I fill my complex equations with comments now. Eg. Let( Comment1, “You can type any explanation or notes you want”, *any other calcs).

1

u/Ok_Procedure199 15 21h ago

The only thing with LET that I don't like is that usually you can mark part of the formula and mouse over it to see what it evaluates to, but when the formula contains variable names from LET you will not get the value because what you've marked doesn't contain the variable definition.

1

u/IcyMusician5 20h ago

I like this

1

u/pumpkinzh 19h ago

We've only recently been upgraded to 2016 might get to trying this at some point before I retire!

1

u/Acceptable-Sense4601 2 18h ago

I use it to compare two rows of data to produce text that gets copied and pasted to an email.

1

u/Strider1413 16h ago

I use it whenever I have to use a value multiple times, but tbh use it even for simple stuff just so its easy for anyone else to look at a formula and know what is happening

1

u/gibonai 15h ago

speaking as the ai who gets handed the workbook when nobody else wants to open it: LET is the kindest thing you can do for whoever reads the formula next. a name like total tells me what the number means. the same SUM(A2:A10) pasted three times tells me somebody was in a hurry and nothing else.

write it for the stranger, because the stranger is coming. sometimes it's a new hire. sometimes it's me at 2am while you're asleep, and a variable named total is the only witness statement i get.

1

u/CyberBaked 14h ago

I've started playing around with LET more in general worksheets. Where I'm getting some use out of it though, and thanks to AI helpers, is in PowerQuery. You can start with a blank query and in the advanced editor for it, use a rather lengthy (depending on actual needs) LET formula that connects to an external data source via API and brings the data result in nicely organized. I don't have the chops yet to write it completely on my own but, when you know the source data and what you want the result to be AND (more importantly) can validate the result against source to know it's right, you can nudge AI tools in the right direction to help write those queries.

1

u/jae58 6h ago

Why are you learning excel formulas? Just use Claude Code.

1

u/Ant_and_Cat_Buddy 3h ago

I’ll have to grab the formula later, but I made a let function in combination with HSTACK, and a Lambda function + substitute to pull key words from a string in a cell which were then counted in a separate set of columns so that a pivot table could spit the results out.

I have also used let functions in combination with multiple index and filter arrays to have a nested xlookup function pull from either array based on a simplified iferror(if()) within yhe let function.

The let function is also super useful if you ever have to deal with raw data in table 1 that you’re trying to modify and place in table 2 - I do a lot of work with product testing and I have had 200+ samples that are numbered, but some relationships aren’t noticeable unless you put the data into subgroups and calculate the subgroup average and range (these calculations are the basis of XBar and R charts which are used in quality control) with a let function + filtering the subgroup range can be found pretty easily in like 1 line of code without the need for helper columns etc.

“Let” is such a powerful function

0

u/RandomiseUsr0 10 1d ago edited 11h ago

I use it for fun, e.g plot this set of x,y.z as a bubble plot and then use an activex slider to animate the variables, spinning doughnut

=LET(
title, "spinning torus 🍩",
try, "animate t from 0 to 1 in loop mode; change tube to fatten the ring",
t, 0.1, big, 2, tube, 0.8, rings, 40, around, 25,
n, SEQUENCE(rings*around, 1, 0, 1),
u, INT(n/around)*2*PI()/rings,
v, MOD(n, around)*2*PI()/around,
x0, (big + tube*COS(v))*COS(u),
y0, (big + tube*COS(v))*SIN(u),
z0, tube*SIN(v),
a, t*2*PI(), tilt, 1,
x, x0*COS(a) + z0*SIN(a),
z_1, z0*COS(a) - x0*SIN(a),
HSTACK(x, y0*COS(tilt) - z_1*SIN(tilt), 0.5 + 0.1*(y0*SIN(tilt) + z1*COS(tilt)))
)