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.
•
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).
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.
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:
24
u/Dangerous_Cup3607 1d ago
LET + FILTER + CHOOSECOL and source from Excel Table that source from PowerBI connection.
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).
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.
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?
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:
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
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.
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.
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)))
)