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!
Thanks Mynda, for the articulate demonstration of how the LET function works, providing various scenarios. It's a strong programming concept for Excel users!
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!
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.
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.
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
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
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.
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.
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. ;-/
@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'
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
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
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 :)
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
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!!!
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.
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.
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..;)
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.
Thanks for the video! This changes so many things I've done in previous spreadsheets. Started seeing your channel today as a recommendation from UA-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.
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.
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.
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
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 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.
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
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.
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 :-)
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!
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.
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.
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!
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
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 :-)
Hopefully this will help differentiate times in athletics where something under 90sec is presented as seconds 89.46 whereas over 90 is in minutes. 1:31.27
Nice function, but my Office 365 hasn’t updated with this function yet. Definitely will save typing time, and for cases where the formula may be lengthy, reduce errors.
Note: Excel could have implemented the formula parser so that subexpressions already calculated are kept as map entries, together with their result. Example: In evaluating (left to right) the formula used to illustrate the LET() function in the video =IF(SUM(C6:C15)>100,SUM(C6:C15)*1.05,SUM(C6:C15)*0.97), after the first occurrence of "SUM(C6:C15)" has been calculated, its 'literal name' (i.e. the actual string "SUM(C6:C15)") and corresponding result (102) are retained as a key-value pair in an automated map data structure. The next time the string "SUM(C6:C15)" will be parsed later down the line, it would be looked up in the map structure and replaced with its previously calculated result... In my opinion, a completely new function is hardly required (well, it STILL presents the advantage of writing less)... Just some smarts in implementing the expression parser. ;-)
it's useful as it reduces errors when changing those portions of the formula. I've had much longer and more repetitive formulas that this would work great on because it reduces potential problem areas when debugging.
Great video Mynda. I learned a lot. Thank-you! At the 15:17 mark, Adventure Works is ranked #3 with Sales of 16. However, The Phone Company also has Sales of 16...tied for 3rd place. When I changed the RankArray to {1,2,3,4}, the spill repeats Adventure Works in both 3rd and 4th place as INDEX will obtain only the first instance. 1) How would you modify the formula to solve for ties? 2) The LET formula spilled across for me. I had to use TRANSPOSE to wrap INDEX to spill down. How did you get the formula to spilled down without TRANSPOSE? Thank-you :)
Thanks! To answer your questions: 1) You'd be better off using this formula if you want the top 4 results: =INDEX(SORTBY(A5:B14,B5:B14,-1),{1;2;3;4},{1,2}) 2) You need to separate the rank array with semi-colons i.e. {1;2;3} if you want it to spill down. Commas {1,2,3} will spill across.
@@MyOnlineTrainingHub Thank-you Mynda. Ah yes, the semi-colon....I missed it. Changing to Top 4 is just okay for this scenario....3rd place tie now includes both Adventure Works and The Phone Company. However, as the title remains Top 3 then it would be nicer to SPILL only 3 results if there are no ties :)
Really nice Minda! Had a query, if you have used a LET Function in EXCEL 365 & you send this report by mail to someone with Microsoft 2013 or 10, will that person able to read values? read formula? or they will get ##N/A?
Thanks, Ravi! I believe Excel will show the results of the LET function, but if they edit the formula it will break it because their version of Excel can't calculate LET upon re-entry of the formula.
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!
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.
Last example on debugging rocks! Great way to manually evaluate your formula!
Glad you think so!
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 🙏
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 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!
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!
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 :-)
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 :-)
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.
Finally got to use LET at work. Thanks for helping me understand it!
Awesome to hear 🙏
That's the easiest examples to make it understand at once. Thank you.
Glad it was helpful! 🙏😊
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?
Thanks a lot for your presentation and channel. You are one of my favorites "Excel teachers"
❤ Thanks so much!
nice one, i will start to use it , rather than retyping the formulas again and again or copy pasting
Great to hear 😊
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.
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'
Allowing debugging! Mind blown!
Thank you for this
Glad it was helpful, David :-)
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!
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 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 🎅
Perfect function. perfectly explained. appreciated. Thank you so much, dear tutor.
Thanks so much!
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 :-)
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 :-)
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 :-)
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.
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 :-)
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 ;-)
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.
This is such a magic function! indeed - this video deserves 1mio views!!!!!
Thanks so much! Please spread the word 😊
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 !!!
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.
LET's magic. Amazing video
Thank you!
Brilliant. Very easy to follow with your explanations. Thank you
You're very welcome!
Apart from programming like VBA, LET also reminds me of algebra done at school. Great video, Mynda.
Yes! Thank you, Sandeep!
Another awesome lesson, thank you
Glad you liked it! 🙏
Thanks for the video! This changes so many things I've done in previous spreadsheets. Started seeing your channel today as a recommendation from UA-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 :-)
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!
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 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.
Such a great and informative channel. Thank you. So happy for youtube to recommend it.
Thanks so much, Bartosz :-)
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 :-)
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.
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 :-)
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.
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!
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!
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
excellent session
Glad you think so!
Thank you Mynda! Another very clear and useful presentation!
Glad you enjoyed it, Oliver!
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 :-)
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!
Excellent video, learnt a lot in few minutes....
Great to hear, Abe!
Amazing! I just suggested my team subscribe due to all of your content being relevant. Thank you again!
Awesome, thank you, George!
Hi Mynda. Great examples! Well explained and useful. Thanks for sharing :)) Thumbs up!!
Thanks so much, Wayne!
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 :-)
Excellent Tutorial and very useful function. Thank you
Glad you liked it, Jason!
Wtfffff... finally some increible useful for better performance and power ups
Great to hear you're looking forward to using the new LET function :-)
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 :-)
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!
Excelente vídeo. Great explanation with very illustrative examples. Thank you.
Glad it was helpful, Mario!
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.
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 :-)
Thanks a lot.. 🌟 🌟 🌟 I could learn new skills through this tutorial..
Happy to help!
What a magisterial presentation. Thank you.
Glad you enjoyed it!
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!
Like the hair cut! Cute!
Thank you!!
Hi Mynda,
That new formula is amazing!!!!
Thank you!!!!
Great to know you're looking forward to getting the LET function :-)
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!
Great function (milestone!) and great lesson! Thank you.
Glad you liked it, Hubert!
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.
Excellent!
Glad you liked it, Jason!
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
Amazing , this is Coding without Vba , and its absolutely elegant
Glad you like it!
Simply awesome
Thanks so much 😊
@@MyOnlineTrainingHub but my excel 365 is not takin Let formula, its not upgraded I guess...
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!
very useful!
Great to hear, Steven :-)
Neat! Great presentation, as always. Thank you so much, Mynda!
Thank you! Glad you enjoyed it :-)
nope don't have that function available..however, thanks for the training..
It’s always good to know what it’s capable of. Hopefully you’ll get a new version of Excel soon.
Thank you
You're welcome, Mohamed :-)
Mind Blowing.
Thanks a lot for it. Simply, great!
Glad you like it!
Amazing!!
Thank you! Cheers!
Super awesome
Thank you so much 😀
Why are people downvoting this? Wow.
Thanks, Jeffrey! Appreciate your support.
👍👍👍...Thank you Mynda.
Cheers, Luciano :-) Have fun with LET!
How can you know all those things in Excel? :-D You're truly a master and a guru, Mynda. Thank you very much.
Thanks for your kind words, Sam!
And thanks very much for thus great video
My pleasure, Bili :-)
As usual brilliant video.... Why dont you make a course in udemy
Thank you! I have my own site with my courses on: www.myonlinetraininghub.com/
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) :-)
Great tutorial as always!!!!
Thanks so much, Daniel!
Hopefully this will help differentiate times in athletics where something under 90sec is presented as seconds 89.46 whereas over 90 is in minutes. 1:31.27
Great to know you'll be able to make use of the LET function, Julian!
And when I went to excel to use it it wasn’t there despite being on the insider programme, already using unique and filter.
Nice function, but my Office 365 hasn’t updated with this function yet. Definitely will save typing time, and for cases where the formula may be lengthy, reduce errors.
Hopefully you'll get the LET function soon. It's currently only available to Office Insiders.
@@MyOnlineTrainingHub still not available on 16 december on my office 365
Thanks a ton
Most welcome!
Note: Excel could have implemented the formula parser so that subexpressions already calculated are kept as map entries, together with their result. Example: In evaluating (left to right) the formula used to illustrate the LET() function in the video =IF(SUM(C6:C15)>100,SUM(C6:C15)*1.05,SUM(C6:C15)*0.97), after the first occurrence of "SUM(C6:C15)" has been calculated, its 'literal name' (i.e. the actual string "SUM(C6:C15)") and corresponding result (102) are retained as a key-value pair in an automated map data structure. The next time the string "SUM(C6:C15)" will be parsed later down the line, it would be looked up in the map structure and replaced with its previously calculated result...
In my opinion, a completely new function is hardly required (well, it STILL presents the advantage of writing less)... Just some smarts in implementing the expression parser. ;-)
Nice idea. You can post it on Excel UserVoice: excel.uservoice.com/forums/304921-excel-for-windows-desktop-application
it's useful as it reduces errors when changing those portions of the formula. I've had much longer and more repetitive formulas that this would work great on because it reduces potential problem areas when debugging.
Great video Mynda. I learned a lot. Thank-you! At the 15:17 mark, Adventure Works is ranked #3 with Sales of 16. However, The Phone Company also has Sales of 16...tied for 3rd place. When I changed the RankArray to {1,2,3,4}, the spill repeats Adventure Works in both 3rd and 4th place as INDEX will obtain only the first instance. 1) How would you modify the formula to solve for ties? 2) The LET formula spilled across for me. I had to use TRANSPOSE to wrap INDEX to spill down. How did you get the formula to spilled down without TRANSPOSE? Thank-you :)
Thanks! To answer your questions:
1) You'd be better off using this formula if you want the top 4 results: =INDEX(SORTBY(A5:B14,B5:B14,-1),{1;2;3;4},{1,2})
2) You need to separate the rank array with semi-colons i.e. {1;2;3} if you want it to spill down. Commas {1,2,3} will spill across.
@@MyOnlineTrainingHub Thank-you Mynda. Ah yes, the semi-colon....I missed it. Changing to Top 4 is just okay for this scenario....3rd place tie now includes both Adventure Works and The Phone Company. However, as the title remains Top 3 then it would be nicer to SPILL only 3 results if there are no ties :)
Really nice Minda! Had a query, if you have used a LET Function in EXCEL 365 & you send this report by mail to someone with Microsoft 2013 or 10, will that person able to read values? read formula? or they will get ##N/A?
Thanks, Ravi! I believe Excel will show the results of the LET function, but if they edit the formula it will break it because their version of Excel can't calculate LET upon re-entry of the formula.
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