LET Function Transforms Messy Formulas - Comprehensive Guide
ฝัง
- เผยแพร่เมื่อ 31 พ.ค. 2024
- The new Excel LET function enables you to declare variables. It improves readability and performance of your formulas. Download the Excel file here: www.myonlinetraininghub.com/e...
Chapters:
0:00 LET Function Syntax
1:51 Using LET for Efficiency
4:41 Referencing Prior Names
8:39 Relative References
13:32 Arrays as Inputs and Outputs
15:54 Debugging
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
Thanks Mynda, for the articulate demonstration of how the LET function works, providing various scenarios. It's a strong programming concept for Excel users!
Thanks for your kind words 🙏
There're so less number of women on the web doing intellectual and tech work. You're one of those few. Keep it up👏👏👏👏👏!
Thanks, I appreciate that!
Last example on debugging rocks! Great way to manually evaluate your formula!
Glad you think so!
This is such a magic function! And, especially for remembering, as well as transmitting your logic to others, when you look at it at any later time. Also, absolutely clear and precise presentation. I always find your video's very high-level, utterly professional, with plenty of practical, useable examples and pure learning. Thanks, Mynda!
Glad you found the video helpful! Have fun withe LET.
Amazing! I just suggested my team subscribe due to all of your content being relevant. Thank you again!
Awesome, thank you, George!
Clear, concise and relevant! I appreciate your style of delivery and the examples given. Just need to put some of these options in use. Thank you!
Thanks for your kind words, Sue :-) Glad it was helpful.
This is absolute magic. I'm already thinking of going back to some of my excel spreadsheets and applying the LET function. Thank you so much.
Awesome to hear :-)
Thanks so much for this well explained and succinct introduction for this new function. Really liking what you're doing, keep it up Mynda!
You're very welcome, Kenneth!
Thanks for the video! This changes so many things I've done in previous spreadsheets. Started seeing your channel today as a recommendation from TH-cam and can't be happier for it. I'm having my teams subscribe to your channel for sure, easy to understand videos and very relevant.
Wow, thank you! Awesome to hear :-)
I had a chemistry problem today I shirked because it would take a few hours to transpose into the right excel form and the resulting formula would look like inpenetrable garbage... this will make a huge difference without having to create a bunch of defined variables. Thanks for the clear walkthrough, and peppering it with a few neat uses of other functions to boot!
So glad you found the video helpful, Dave!
That's the easiest examples to make it understand at once. Thank you.
Glad it was helpful! 🙏😊
Such a great and informative channel. Thank you. So happy for youtube to recommend it.
Thanks so much, Bartosz :-)
Allowing debugging! Mind blown!
Thank you for this
Glad it was helpful, David :-)
I am really pleased I found this channel - very well explained and presented. One of the best I have seen, well done
Thanks for your kind words and welcome aboard!
Neat! Great presentation, as always. Thank you so much, Mynda!
Thank you! Glad you enjoyed it :-)
Finally got to use LET at work. Thanks for helping me understand it!
Awesome to hear 🙏
Thanks a lot for your presentation and channel. You are one of my favorites "Excel teachers"
❤ Thanks so much!
LET's magic. Amazing video
Thank you!
Thank you Mynda! Another very clear and useful presentation!
Glad you enjoyed it, Oliver!
Hi Mynda,
That new formula is amazing!!!!
Thank you!!!!
Great to know you're looking forward to getting the LET function :-)
That is a great way to make really complex formulas easier to read for others (and for me when I go back to an old file !)
Glad you like it, Anais!
Great video, a thorough coverage of the LET function indeed! I've been using it for a month now and I think it's fantastic. I use the debugging approach you suggest too, it is a great way to step through your formula, especially as they get more and more complex. I've just made it a rule that I output the final step as the return of a single variable (as you did in your example) always, like the "in" step in M code.
It really does throw the world of excel formulas in a whole new direction. The complexity offered in a single formula is huge, and it'll take quite a balancing act between keeping things simple over having less formulas. For example, in your Top 3 example I would have used the choose formula in the final variable to return 1, the name, 2, the XLOOKUP value, thus returning the whole table in one formula (I'd have maybe used sequence instead of {1,2,3} with a user input for the number, but I realise you were just showing a single example!). I think I'm of the opinion that if you're having to copy a formula down, like you did with the xlookup, then I would append to the single Let formula rather than have to copy further down if you wanted top 4 or whatever. If it's more pivot-like, as in your FILTER formula with the dates/months across the top and you have an unknowable number of columns, I'd do as you did. It'd be interesting to see a video touch on these concepts and best practices, as I'm struggling to define the best approach with LET.
Thanks for your kind words, Rico! In preparing the examples for this video I did have a scenario where I declared many variables and used quite a few of the new dynamic array formulas like you suggested, but I decided this might be a bit daunting and I didn't want to scare people away! You mentioned the top 3 example and although I didn't copy XLOOKUP down, it's a dynamic array so it spilled, it prompted me to write it again to return all 6 results in one go (including your suggestion of SEQUENCE) :-)
=LET(
RankArray, SEQUENCE(3),
BrandRng, A5:B14,
SalesRng, B5:B14,
INDEX(BrandRng,MATCH(LARGE(SalesRng,RankArray),SalesRng,0),{1,2}))
And as per your tip, the 3 in SEQUENCE could be linked to a cell for the user to enter.
I agree, LET is a big change to how we write formulas and finding a balance between nesting many steps in one formula vs having helper columns that are easier to follow for some users etc. will depend on the audience/users of the workbooks, assuming one or the other doesn't have an adverse effect on performance.
Thank you for this marvelous and elaborate video on Let. Each and every example is amazing. I liked that Alt+Enter trick, not many people know this. I often use this trick while writing long IF statements. Cheers :)
Glad you enjoyed it, Sachin!
Simply Excel Brilliantly! I think your excel skills are the biggest contribution to Excel since its introduction Nov 1987. I wish you and your family a Merry Christmas and a very prosperous New Year
Wow, thanks for your kind words! Marry Christmas and best wishes for 2023 to you too 🎅
That's great. I've wanted a LET function for years. This will probably let me avoid making many of the VB UDFs I usually resort to. And you used some other functions I wasn't aware of. Lots to digest here. Thanks! I just subscribed. :-)
Awesome to know the LET function is going to be a big help to you, Dan! The other functions you mention are probably the new dynamic array formulas. More on them here: www.myonlinetraininghub.com/excel-dynamic-arrays
Hi Mynda, this is indeed an eye-opening lecture.
One very interesting feature of the LET function is that you can use a name within a name.
That is, a name may contain previously defined names in the very same LET function.
For example, in your last illustration:
The "Calc" name refers to ("contains") other predefined names ("Rng", "SelectedBrad", "SelectedCategory").
This feature is very useful, especially when your formula is quite complicated.
Thanks, Meni! Yes, being able to refer to a name previously declared is super useful.
Perfect function. perfectly explained. appreciated. Thank you so much, dear tutor.
Thanks so much!
Brilliant. Very easy to follow with your explanations. Thank you
You're very welcome!
Thanks for sharing, this is great feature. I wasn't aware of the new Excel features LET, CHOOSE, FILTER, XLOOUP, etc... Will definitely need to brush up my Excel knowledge as these new formulas can improve the existing ones
Glad it was helpful!
Thanks a million mynda
That was magical
It saves times and clarifies formulas . Thanks for your help and for your time
Appreciate 🤩
So pleased you liked the LET function :-)
That's a fantastic function. It reminds me the syntax of one more very useful function "Choose". Thanks for sharing.
Great to hear, Sachin :-)
@@MyOnlineTrainingHub subscribed !!!
Excelente vídeo. Great explanation with very illustrative examples. Thank you.
Glad it was helpful, Mario!
I am looking forward to using this function. It looks incredible and will make so many of my worksheets much easier to write, debug and come back to months or years after first being written.
Great to know you'll be able to make use of the new LET function, Nigel :-)
Wonderful ! You show me something useful. You write your formula in many lines by splitting with Alt+Enter. By that, a complexe formula become simple to read. Ex.
=IF(
A1
Great to know you found the Alt+Enter tip useful :-)
The formulas which you are telling are quite a high level one.
And I find quite useful and difficult too.
Thanks for sharing valuable information.☺️
The only limitation of LET is your imagination ;-)
This is such a magic function! indeed - this video deserves 1mio views!!!!!
Thanks so much! Please spread the word 😊
Another awesome lesson, thank you
Glad you liked it! 🙏
What a magisterial presentation. Thank you.
Glad you enjoyed it!
Amazing Video Myndy. I have the 365 Insiders so I have been enjoying and practicing the LET Function and this will add to my practice!!! Thanks very helpful. : ) : )
Glad it was helpful, John :-)
Seems to be a very useful function. Eager to try when it is released. Thank you for excellent explanation.
Glad you're looking forward to using the LET function :-)
Now let fn is available i ofc 365 n i have practiced everything shown in video. It took 2 hrs to practice everything as i was forgetting brackets while using filter fn n was giving errors to me. But have to say its great tool to avoid writing formulas again and again n again. Now i can give variable to name and calculation as many time as possible.
So pleased it will be useful to you, Deependra!
Apart from programming like VBA, LET also reminds me of algebra done at school. Great video, Mynda.
Yes! Thank you, Sandeep!
Wow this is amazing and yet at same time so advanced I will have to watch more than once to understand how you can do this
thanks for videos you and husband are amazing
Thanks so much, Shawn!
Great function (milestone!) and great lesson! Thank you.
Glad you liked it, Hubert!
Hi Mynda. Great examples! Well explained and useful. Thanks for sharing :)) Thumbs up!!
Thanks so much, Wayne!
nice one, i will start to use it , rather than retyping the formulas again and again or copy pasting
Great to hear 😊
Being able to declare variables is a very important step of the new calculation engine.
I see excel starts to look more like a traditional programming language.
LET sure is a great step forward for Excel :-)
I am subscriber of leila gharani channel so understood 80% whats going on. So definitely recommend this channel to my friends and subscribed this too.
Welcome aboard :-)
Thanks a lot.. 🌟 🌟 🌟 I could learn new skills through this tutorial..
Happy to help!
Mind Blowing.
Frist, that was a great, clear presentation!
In other news: we finally get a feature that's been available in BASIC since 1964. Now that's what I call progress. ;-/
:-) glad you enjoyed the video.
@Johnny Deep In other words, it's functionality that has been available in Excel for decades by using VBA. But VBA doesn't work in Excel Online/Android/iOS, so here at work all new spreadsheets must be VBA-free in order to function correctly across platforms.
I've been wishing Excel had an in-cell LET function like this since my company embraced Office 365. I really appreciate this video!
This kind of 'let' derives from LISP's let/let* and was introduced there in 1958.
In LISP the first example would read
(let* ((x 5) ; first name - value pair
(y 10)) ; second (and last) name - value -pair
(+ x y)) ; operations on variables and end of 'let'
Wow. Daz a killer video on LET Function. Well presented but requires more practice to master.
Thanks, Shadrack! I found it really easy to master. It's only limited by your imagination :-)
Thank you Mynda, as usual very very good explained. I don't have LET yet but it will come soon I hope. I liked the debugging part, comparable with the VBA window to see the values of the variables. At this moment I would prefer names in formulas in stead of LET, but I do not work with that large files with formula's. But it is good to know that that there is a LET that can maybe solve your performance issues. btw: nice notebook you have on your desk..;)
Thanks, Bart! The notebook was an MVP gift from Microsoft ;-)
An excellent presentation, I learnt a lot. Thanks.
Glad it was helpful, Lloyd!
Excellent Tutorial and very useful function. Thank you
Glad you liked it, Jason!
Hi Mynda, It's impressive to learn such formulas in excel. Thanks for sharing such a useful information.
Please do make video on VBA from beginning level.
Thanks, Akilesh!
Excellent video, learnt a lot in few minutes....
Great to hear, Abe!
Hey, I just came across your channel and really enjoyed this video and your clear presentation. I love Excel and I can't wait to go through more of your videos! If I can offer you one piece of advice in return for your generosity - you are speaking into the top of your mic when you should be speaking into the side (which is actually the front). There is normally a 'dot' on the side of the mic to identify which is the front side as opposed to the rear. You will get a much fuller and more natural sound to your voice if you do this. Best wishes D.
Cheers, Dave! I didn't use that mic in the shot to record the intro audio, but I am still working on getting my recording set up perfect, so appreciate the tip :-)
I don't really know why am I seeing this, but I am glad that Excel users can finally have local variables. Maybe you will even get loops someday, idk...
Well at least in later versions of Excel, you can do loops to some extent, or at least simulate them with the OFFSET and COUNTA combo. Either horizontally or vertically to get just as many rows or columns as there is data, and only those rows or columns, and have it be autopopulated to the right or down. I use it for charts, where data in the charts on the X-axis expands/contracts.. by simply making a OFFSET+COUNTA formula and then putting it in a named reference
Loops are obviously coming eventuality.
What? OK, just 2 words: SEQUENCE and LAMBDA. Pick your poison.
Hi Mynda, Very interesting video about this function. Simply amazing!!
Request you to please make VBA tutorial.. It will be very helpful. Thanks
Glad you like the LET function, Rakesh. Have fun with it.
Excel Macro Mastery has some good VBA tutorials. Good info on the website too. I would start there.
Great tutorial as always!!!!
Thanks so much, Daniel!
Great lesson and great new feature. Can't wait to see LET in the official release. Also thank you for reminding me about ISERROR. Esp with xlookup/vlookup (as you mention) I have so much redundant code! ISERROR helps a lot.
Great to know you're looking forward to using LET. Be sure to use IFERROR as opposed to ISERROR for handling errors.
@@MyOnlineTrainingHub Right! ISERROR is pretty straightforward by comparison ... Thank you.
Thanks a lot for it. Simply, great!
Glad you like it!
excellent session
Glad you think so!
Thanks Mynda. Really very informative and i am great fan of your videos. Just to add, alternative to LET function, we can also use VLOOKUP function with a return type as 1.
Glad you liked the new LET function, Mahesh!
Hello, thank you for the detailed walk-through. Could you briefly explain the benefit of using the LET() vs using name manager and naming ranges? I believe you can achieve a similar outcome with the right combination of ifs and dynamic ranges.
Yes, you can use defined names instead of LET to achieve the same results. The benefit of LET is that you can see all the workings in the formula, whereas with defined names you need to open the name manager or use the name box to see what is actually being referenced.
Love your videos!
Thank you, Jimmy :-)
Like the hair cut! Cute!
Thank you!!
I've always wanted something like this in Excel in order to reduce the number of times I have to repeat the same formula within a formula. I've always ended up having to use helper cells to make the formula readable. I hope they release to the public soon.
Great to know you'll be able to make use of it :-)
Doesn't this still use a helper cell?
Omg, LET with the development of XLookup are game changers.
Haha, over the past couple decades I've written some ridiculous and convoluted conditionals. LET is so much more efficient!!!
Great to know you'll be able to make use of the new LET function, Ikaika :-)
I created functions to find the numerator and denominator of a value with a fraction
C2 contains the value
C3 contains a string with a number of questionsmarks
Numerator:
=LET(
form;C3;
part;TRIM(TEXT(MOD(C2;1);form&"/"&form));
VALUE(LEFT(part;FIND("/";part)-1))
)
Denominator:
=LET(
form;C3;
part;TRIM(TEXT(MOD(C2;1);form&"/"&form));
VALUE(MID(part;FIND("/";part)+1;LEN(form)))
)
With these combined you cab create a different fraction format with more then 3 digits
Interesting, Wouter! I'll have to try it out. Thanks so much for sharing.
that is pretty useful. Anything too large I just do it in VBA. So much more powerful and flexible.
Glad you'll be able to make use of LET. Be careful crafting your own functions in VBA, they can be significantly slower than the built in functions ;-)
@@MyOnlineTrainingHub Yes they can be can be but they can also speed up your workflow for complex items and other things that the excel functions just aren't capable of doing. Once someone masters the excel functions the next step is VBA.
Thank you
You're welcome, Mohamed :-)
Excellent!
Glad you liked it, Jason!
Simply awesome
Thanks so much 😊
@@MyOnlineTrainingHub but my excel 365 is not takin Let formula, its not upgraded I guess...
very useful!
Great to hear, Steven :-)
👍👍👍...Thank you Mynda.
Cheers, Luciano :-) Have fun with LET!
Amazing!!
Thank you! Cheers!
And thanks very much for thus great video
My pleasure, Bili :-)
Just tired this and it worked, not sure when I got the update as not insider, all I know is when I first tried it, I didn't have it. Great intro, looked at some others and they went into using it in quite complex ways which is not great when you just need to understand the syntax, this goes for DAX, M or Excel.
Have you ever considered a video on Microsoft 'insider' as I've thought about it but always been wary.
Glad you found my video helpful, William! That's a good idea to do a video on being an Office Insider.
Thanks a ton
Most welcome!
This is great, especially for the readability. Although SUM() is used multiple times in the formula, Excel's formula execution may be smart enough to optimize and only perform SUM() once. I would hope it does this.
Indeed, James, with the LET function the SUM will only be evaluated once, whereas with the IF it would have been evaluated twice.
Mynda, you explained it so easy to remember. thanks you so much. i am glad i just found such a good teacher. keep it up!
Glad it was helpful, Dansel!
Wtfffff... finally some increible useful for better performance and power ups
Great to hear you're looking forward to using the new LET function :-)
👏🏻👏🏻👏🏻 this was great
Cheers, Graham!
Now i am not programmer and not familiar with advance array formulas but will definitely grab it after practicing it. 3 examples in one video made it lengthy. Suggest you to make just one or 2 examples of 10-12 mnt max. Otherwise great video.
So will wait for office 365 monthly roll out because i have that plan.
Great to know you'll be able to make use of the LET function, Deependra!
Amazing 😍
Thanks 🤗
Thanks you :)
You're welcome, Maher!
Thank you very much for the video you made. It is very educative and I appreciate it!
Question:
In what position should I use the "UNIQUE" function to make sure that the product does not repeatedly appear in the result?
Thank you!
Glad I can help! Assuming you're returning a single column, then UNIQUE should be the outer function e.g.
=UNIQUE(FILTER(Table1[Product Category],Table1[Brand]=C4))
@@MyOnlineTrainingHub
=LET(
productrank,{1,2,3,4,5,6,7,8,9,10},
productname,Table1[product_title],
totalnetsales,Table1[net_sales],
SORT(UNIQUE(TRANSPOSE(INDEX(productname,MATCH(LARGE(totalnetsales,productrank),totalnetsales,0))))))
Sorry, I am practicing the Top 10 by using LET function.
The result appears some repeated products.
Thank you for your help!
Super awesome
Thank you so much 😀
Absolutely excellent video! Great new formula plus I learned some useful stuff about other things, I've been manually converting table references to absolute for months. Never occurred to me copying and pasting as opposed to dragging was all I needed to do!!!
One query, in the relative references section, you used G3 in both the start and end of month variables. Would I be right in thinking you could have used your already declared start of month variable in your EOMONTH formula instead of G3?
Liked and subscribed by the way :-)
Glad you liked LET and discovered the Table tip :-) Yes, I could have written the LET with EOMONTH(MonthStart,0) :-)
Amazing , this is Coding without Vba , and its absolutely elegant
Glad you like it!
Excellent video!
This got me stoke, 'cause I come from the programmer level, and every time I try to get into excel I always get lost in these one-liners and repetitive commands...
therefore I now joined the insiders'. For anyone that's trying to get it, Microsoft has a page for MDT config xml but they had removed the Insiders channel from it and even if you add it in the config file won't work(at least from my experience)... instead need to create a key in the registry over what you already have installed, and then hit update one more time (as won't get any sign that you are an insider)... -that was fun!
cheers!
Welcome to Insiders, Ivan :-)
Thanks! Madam
My pleasure :-)
@@MyOnlineTrainingHub you are providing online class for PowerBI
Yes, you can see my Power BI course here: www.myonlinetraininghub.com/power-bi-course
@@MyOnlineTrainingHub Thanks! Madam
Great explanation, thank you. I'm getting to grips with the LET function and starting to really like it. However I created a unique list of some items and store in variable and then tried to use the variable in a MakeArray function that also uses Textsplit to have the text over 3 columns and it does not give the right result. If I replace the variable with the spilled dynamic array (#), it works as it should. Any ideas? Data,MAKEARRAY(ROWS(Flex_ul),3,LAMBDA(rw,col,INDEX(TEXTSPLIT(INDEX(Flex_ul,rw,0)," "),1,col))) - where Flex_ul is the unique list.
Great to hear you're having fun with LET. At a guess, you could try adding the # to the unique list name e.g.: Data,MAKEARRAY(ROWS(Flex_ul#),3,LAMBDA(rw,col,INDEX(TEXTSPLIT(INDEX(Flex_ul#,rw,0)," "),1,col)))
If that doesn't help, you can try posting your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thank you so much. It didn't work but will try the forum
Great video!!!
I have a question, when i divide two number on excel (ex. =5/10), excel immediately change the value of cell by 0.5. And if I need to know the data that I introduce (5 and 10), it doesn't appear....
How I can set up that excel remain the formula (=5/10) on that cell...?
Hi Oliver, if the cell is formatted to 'Number' and you type +5/10 it will convert it to 0.5. If you type =5/10 it will retain the underlying formula in the cell.
Thats really great! Thats incredible useful :D unfortunatelly we dont use m365 in the company :(
Hopefully when your company next updates their software you can convince them that Microsoft 365 is the way to go!
Thank you for a great video! I just subscribed to your channel! :) I noticed on the array input and output segment there were two vendors that tied for third but it chose the one one that was listed first alphabetically. Adventure Works had 16 but so did The Phone Company. Is that because of the way the table was sorted or is that because of how Microsoft programmed the function?
Thank you! The tie break is decided based on the sort order of the list. Well spotted ;-)
Thank you so much! I've had a similar issue with VLOOKUP.
You are always making amazing videos ✌️👌😁. But, I am currently struggling with Google sheet online in which I am trying to create a protection in the enter sheet only those cells and columns that has a specific word, so in a nutshell, I want to make automatic protection based on conditional. For instance, I have a data entry in which the team leaders fill it and I have a column to approve them, if I said yes then automatically lock these cells for them, and if it was no then leave it as it is. Thanks in advance. I have searched a lot but didn't find anything like this.
Thanks, Halkawt! I don't use Google Sheets, sorry.
Why are people downvoting this? Wow.
Thanks, Jeffrey! Appreciate your support.