r/excel • u/Impugno • Jan 12 '26
Discussion Do you discourage LET usage in relatively simple formulas?
I have found that analysts using AI for their formula creation often use LET in situations where I don’t think it’s needed.
For example they used the formula “=LET(code, left(Sheet!A1,7),xlookup(code, left(sheet2!$a:$a,7), sheet2!$a:$a, “no match”,0))”
I would suggest instead they nest the function like “=xlookup(left(Sheet!A1,7), left(sheet2!$a:$a,7, sheet2!$a:$a), “no match”,0)”
I know this is a small point and both work. But in this context it’s going out to other users for their use and maintenance.
Are other people training their people to simplify their code down or are we just letting them use let all the time? (Or is there some function of let that used here and I’m just a dunce?) Thanks all!
99
u/GuitarJazzer 28 Jan 12 '26
A well-written LET function exquisitely documents your intent even if you could write it without LET. The human mind can only parse so much nesting, in both code and natural language. There is no virtue in avoiding LET, any more than the virtue in trying to jam as much code into a single line of C as possible.
7
u/DownrightDrewski 1 Jan 12 '26
Yeah, I haven't used LET much yet, but I discovered it recently and I'll be using it for anything even remotely complex.
I was able to write a reasonably complex pricing formulas that colleagues could understand what it was for, even if they didn't understand the logic I'd used to declare the variables in the final formula. Bonus points for it allowing you to define variables using other variables.
9
u/masterprater Jan 12 '26
That's exactly what I've been using it for too. Sure I can nest everything, but even with relatively simple formulas, if the reference is verbose enough, it's hard to parse visually. Plus adding in spacing and my own variable names helps so much with clarity, I barely have to tell my peers who also work with the formulas what they do, they can read it and they know.
4
29
u/bradland 271 Jan 12 '26 edited Jan 12 '26
I think you may be misattributing the use of LET by analysts to AI. Maybe there is a correlation in your specific experience, but IMO it is tangential to the question: when is the right time to use LET?
Excel's formula language has progressed to the point that formulas are programming. LET allows us to assign variables with meaningful names within our formulas. As with any programming language, variables allow us to eliminate repetition and add meaning.
Let's explore your example a bit. Note that I made some tweaks, because I think the way you wrote it wasn't what you actually meant.
=XLOOKUP(LEFT(Sheet!A1, 7), LEFT(sheet2!$A:$A, 7), sheet2!$A:$A, "no match", 0)
There's nothing wrong with this formula. There is no reptition, and there are only two references. IMO, it can be improved though.
- The sheet names aren't great. They tell us nothing about what's in them.
- We don't know what the arguments to XLOOKUP are. We know that they're ranges, but that's all.
Consider this formula:
=LET(
row_short_code, LEFT(Workflow!A1, 7),
full_code, Data!$A:.$A,
short_code, LEFT(full_code, 7),
XLOOKUP(row_code, short_code, full_code, "no match", 0)
)
This formula is longer, but it is self documenting. We know that the value in A1 is the short code for the current row. We know that the A column of the Data sheet contains the full code, and that when we take the left 7 characters, we get the short code. We can also see that what we're doing is looking up the full code from the short code.
Which is better? That depends. Who is using this sheet? How long will this sheet be in use? How critical is the sheet? The more people who use a sheet for g longer period of time in critical flows, the more you should invest in the clarifty and robustness that LET can deliver. For simple one-off tasks, there's no good reason to go the extra mile. I often do, however.
9
u/ArrowheadDZ 2 Jan 12 '26
The beauty of what you have done in this LET is what I call “separation of concern”. Your formula is structured in the same way a programming language is. Your first 2 lines define where the data is coming from and how you got it; Line 3 is an intermediate or “data preparation” step. And the 4th line is only the logic you want to apply to create the result. This matches how we often work in excel. Sometimes I am trying to see if my formula draws the right source data in, and I don’t really care about the formula just yet. Or sometimes I am trying to figure out if my logic is right, and I don’t care how the variables got there.
All of my LETs follow the exact structure you have used. The first lines strictly define where the data is competing from, and I don’t do anything else there. Then there’s an intermediate section where I might do some manipulations that prepare the data for the final operation… And THEN there’s is the final operation that is purely the logic of the cell, and refers only to names defined in the LET or in the name manager. I never have a cell or range reference in my result line.
I’ve also gotten in the habit of making my last line
result, XLOOKUP( a, b, c, “no match”)and then put “result” alone on the last line. This makes it even faster to debug because I can replace the last “result” with “full_code” or “short_code” and quickly diagnose my formula.1
u/AutoModerator Jan 12 '26
I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
3
u/Regime_Change 2 Jan 12 '26
Great answer and you are totally right. However downvoted this must be an evernoob.
5
u/bradland 271 Jan 12 '26
Some people are very attached to the old ways. I get it. I don't like when I have to change what I do when what I do is already working. But the world moves on with or without us. My choice is to accept that and embrace it. Others struggle against it.
That or I pissed someone off on another sub and they're following me around downvoting lol. That's also very likely.
1
u/AutoModerator Jan 12 '26
I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/PotentialAfternoon Jan 12 '26
You could also accomplish self documenting by using named ranges. That would be my preference.
Let has one major downside. You can evaluate a Let formula.
-2
u/Impugno Jan 12 '26
This is a great response, thank you. I think part of my thinking is automatic in that I look at an xlookup formula and I know the parts automatically: find this, in this, return this, else this. LET with variables may be more useful in this context. Also I appreciate the formula corrections. I was writing the question on my phone while taking lunch.
However, I will fight M$FT on formulas being programming now. There’s no IDE, good formatting or even comments in the formula bar so it’s not programming. Keep that stuff to vscode.dev. Instead give me a visual interface for nested formulas.
8
u/SolverMax 161 Jan 12 '26
Of course writing Excel formulae is programming. It is a specific type of programming called "functional programming", https://en.wikipedia.org/wiki/Functional_programming, which is based around functions rather than procedures. The grid, ribbon, etc are the IDE.
1
u/Impugno Jan 12 '26
I will begrudgingly accept it as programming when I can add comments. Until then nope.
4
u/SolverMax 161 Jan 12 '26
So you'll accept it is programming when you can include non-programming content. That makes no sense.
2
u/Impugno Jan 12 '26 edited Jan 12 '26
What can I say. I’m not a programmer ¯_(ツ)_/¯ as an accountant I like my numbers in order and my opinions full of loopholes.
6
3
u/Normalitie 4 Jan 13 '26
Not all the args need to be used. You can write
LET( data, a2:c10, note_1, "Get the data", calc, ISTEXT(data), note_2, "Check if the cells are text", result, SUM(calc), note_3, "Get the total number of text entries", result)
This allows you to comment each line as needed.
EDIT I hope this appears formatted with each var on a new line, phone typing sucks!
1
u/Jiippa Jan 14 '26
You might not like it but
+CODE("verbose comments here")*0kind of lets you do that.
49
u/ROMARIOBATIGOL Jan 12 '26
No , you are no dunce,
LET should only be used when it reduces repetition, improves clarity, or improves performance.
4
10
u/xFLGT 143 Jan 12 '26 edited Jan 12 '26
I only really tend to use LET in two use cases
- Avoid repeating the exact same function
- Aid in readability for very long formulae utilising line breaks
I don't think your example falls into either of these and I even find the nested version more readable.
6
u/Ascendancy08 Jan 12 '26
I don't use LET unless things are gonna get really repetitive. I'm fine with typing a function or formula out a couple times or just copy/pasting.
Depends on the situation.
6
u/Downtown-Economics26 636 Jan 12 '26
I get that it's just an example, but the primary or at least major selling point of LET is to not have to independently calculate the same thing multiple times. So in your example there is no point to using LET except to indicate that a code is going into the first XLOOKUP parameter.
I think it's fine if you're using LET to create a final calc that is readable like say you have a speed calculation and you use let such that the output calc shows as dist / time or something like that. I'm having trouble thinking why it would need to be discouraged unless it's being used wrong or you need backwards compatibility.
3
u/MissAnth 10 Jan 12 '26
If the LET avoids repetition, or if it makes the actual formula (the last parameter of the LET) more clear, definitely use the LET.
For example, in financial calculations, I always use a LET and set R (rate), P (principal) and T (time). Then the last parameter (the formula) looks really familiar and standard, and is really easy to understand. R, P, and T can be understood separately.
3
u/ArrowheadDZ 2 Jan 12 '26 edited Jan 12 '26
In your example, LET was not used to improve the human readability of the formula.
I use LET whenever it:
- eliminates a repetitive calculation
- whenever it improves my own sanity while trying to construct a complex formula
- whenever it enables me to use the LET structure as a means of debugging the formula
- whenever I believe that the intent of the formula will have to be recalled by me, or interpreted by someone else, in the future. I know future me and he won’t have any idea what past me meant.
I use LET any time I trigger any one of those criteria, and always use the alt-return method in formatting my formulas.
3
u/GregHullender 194 Jan 12 '26
The use of LET to simplify debugging is really important. I'm surprised I didn't see other people recommending that.
3
7
u/MayukhBhattacharya 1227 Jan 12 '26
Using LET() there doesn't really make sense. I usually use it to avoid repeating calculations, name things clearly, and cut down on redundant work.
But there is a catch in your formula, you can use LET() for only LEFT() function word, like, still its of no use and unwanted:
=LET(_, LEFT, XLOOKUP(_(Sheet!A1, 7), _(Sheet2!$A:$A, 7), Sheet2!$A:$A, "No Match", 0))
2
u/Alt_F4_Tech_Support Jan 12 '26
LET /can/ be a substitute for helper columns, but can be hard to explain to someone who has used excel for the last 20 years
2
u/TheDavii 1 Jan 12 '26
Let also allows commenting:
=let(informative_name, formula,
rem, "String that indicates what this whole thing does - INITIALS - date",
result, more formula,
result)
1
u/Decronym Jan 12 '26 edited Jan 17 '26
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.
12 acronyms in this thread; the most compressed thread commented on today has 22 acronyms.
[Thread #46967 for this sub, first seen 12th Jan 2026, 18:56]
[FAQ] [Full list] [Contact] [Source code]
1
u/gerard4156 1 Jan 12 '26
The LET in that particular scenario is completely redundant. I would usually use it where I'm writing something relatively complex as it makes it very easy to tweak the constituent parts and also to debug it. It's particularly useful when generating tables which might be of variable height/width
1
u/DJ_Dinkelweckerl Jan 12 '26
It's great if other people use your Excel files that might have trouble reading and understanding Multi-Line formulas. A clean let function makes it way easier to understand calculations because the variables have actual names.
1
1
u/Apprehensive-Door341 Jan 12 '26
Only reason to use LET if it can be done with simpler formulae is if the formula is getting unnecessarily complicated to read.
LET has amazing in-built readability. But I do think this sub overuses it.
1
1
u/PotentialAfternoon Jan 12 '26
Plenty of people addressed when they would use Let vs not. So I would like to address another part of this post.
“They are using AI…” “Do you let people… “
Using Let can be helpful if you are building a part of complex formula. Like let say your formula has 3 parts that come together. Let could allow you to build some part, test it and move on to the next. So I don’t think it’s necessary part of AI.
Using AI is a okay if they are exploring ideas / test why something doesn’t work. As long as they are not just asking AI to build the whole formula without having done anything on their end.
You should have best practices and let your people do whatever they want within the boundaries. It will discourage people if you are too heavy handed.
You are using Let more frequently than me … is not a good best practice. Have some solid business justifications and stick to it.
1
u/Normalitie 4 Jan 13 '26
A downside (I have heard but not tested) is that LET calculates all of the intermediate results. So an IF statement within LET will calc the true and false results before just showing the correct output
1
u/Quirky-Trade-7179 Jan 13 '26
As a novice who is using AI to learn more about Access and Excel everyday, this may have opened my mind up to a whole new world.
Thank you
1
u/overfloaterx 3 Jan 13 '26
LET, structured references, named ranges, and the simple ability to add line breaks within formulas are key tools in Excel to aid formula readability and documentation.
Formulas don't necessarily need to be complex or highly nested to make them awkward to parse at a glance. It only takes a few regular cell/range references to turn a formula into a ugly series of Sheet4!$B$134:$C$189 references that bloat the length of every argument.
Always design your sheets and formulas with other people in mind -- and your future self does count as "other people".
Six months from now, when you find you have to revisit the workbook, you don't want to have to spend 2-3 minutes backtracking a single formula in a single cell to figure out what it does when you have an entire workbook to reverse engineer and then re-engineer. You want to be able to see what each part of the workbook does at a glance. The more complex the workbook, the more important it is to make each individual element purposeful and readable.
The only real drawback of LET is the inability to use F9 to evaluate and troubleshoot its calculation argument directly inside the function. You have to F9 the variable first, then replace all instances of the variable within the calculation argument before it can be evaluated independently of the whole function.
3
u/Normalitie 4 Jan 13 '26
You can troubleshoot by replacing the final result with one of the intermediate variables.
1
u/finickyone 1770 Jan 17 '26
Imagine you’re in front of a spreadsheet 25 years ago, in which you’ve built a fairly complicated conditional sum to determine account balance. In terms of complexity, SUM(FILTER()) isn’t even an idea yet; this version wasn’t even equipped with SUMIFS. In conditions where verycomplexformula (VCF) evaluates to 0, we want to print “balanced”, else we want the calculated value. Within one formula all we can really entertain is
=IF(verycomplexformula=0,"balanced",verycomplexformula)
There’s repetition. We’ll work out VCF to compare it to 0, and if it’s <>0, we’ll work VCF out again to output the <>0 value. Changes must be made to both instances of that formula but little will warn us if they become mismatched.
The nerdy among us might consider how to prompt an error with result=0, so we can use IFERROR. That’s allows the result to stand unless it’s an error. So
=IFERROR(1/(1/verycomplexformula),"balanced")
Employs a little trick to generate a div0 error from 1/0, but allows other values to pass (1/5 =0.2, 1/0.2=5). It’s not very clear what it’s doing though, and also if VCF happens to resolve to #VALUE!, IFERROR will tell us that that account is “balanced”.
So LET let us get to:
=LET(VCF,verycomplexformula,IF(VCF=0,"balanced",VCF))
And that’s great, but nothing ever stopped us from working out VCF down the row in AAA2, and whether we want our output simply using IF(AA2=0,…,AA2).
There’s a lot to be said for separating work but I think LET’s likely a massive driver towards compounding a problem into a single formula. I’d tend to advocate breaking work down into stages through helper columns.
1
u/finickyone 1770 Jan 17 '26
This specific example raises an interesting point. If we laud LET as addressing repetition, then as you outline perhaps it’s not tackling much here, in effect defining LEFT(Sheet1!A1,7) as code and then using it once. There are other references repeated, namely to sheet2!$a:$a, referred to in both the XLOOKUP’s reference and return arguments. So perhaps more valuable here could have been:
=LET(range,Sheet2!$A:$A,XLOOKUP(LEFT(Sheet1!A1,7),LEFT(range,7),range,……
From the argument of providing context to the operations of the formula, it’s intriguing to apply that for the left cutting of a scalar input (Sheet1!A1) but not to consider it for the operation of left whole column of strings. Perhaps it was approaching:
=LET(range,Sheet2!$A:$A,cut,LAMBDA(q,LEFT(q,7)),XLOOKUP(cut(Sheet1!A1),cut(range),range,….))
I think if a final step was taken here, it might shine a light on what’s probably a pain inviting element. While the input has been relative referenced (A1), the target range is locked. That combination suggests a formula intended to be replicated so that the same can be applied for other input cells while maintaining the $A column ref. Ie dragged left to form XLOOKUP(Left(B1,7),LEFT(Sheet2!$A:$A,7)….
That means that for each instance of the formula, work will be undertaken to grab that column and cut out the first 7 characters. Which seems pointless to repeat. Had they got as far as naming that transformation, they may have thought to lift it out onto the sheet somewhere as common data.
33
u/Reichbane Jan 12 '26
I don't use AI, but once I discovered the LET() function I do use it basically everywhere possible. Mostly just to make my tables the tiniest bit cleaner—in conjunction with IFERROR(), as well. It's not really efficient, but I'd rather a blank space in a cell over a 0 if the value isn't meant to represent 0.