Thanks Mynda, for the articulate demonstration of how the LET function works, providing various scenarios. It's a strong programming concept for Excel users!
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!
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!
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
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'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.
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
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'
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
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
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.
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.
Thanks for the video! This changes so many things I've done in previous spreadsheets. Started seeing your channel today as a recommendation from RU-vid 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.
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
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!!!
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.
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.
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.
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..;)
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. : ) : )
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 :-)
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, 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.
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!
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.
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'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.
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!
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 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
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.
Nice, excel is adding so many things regularly! One thing in the video though, can't we just use the Month() function instead of getting start+end of month?
@MyOnlineTrainingHub but doesn't the Month(*date*) function give you the month number of whatever date you put in it. And then you can compare it with the month of the header in your output range the formula can be like this: =LET( Month, MONTH(G$3), Brand, $F4, BrandRng, FILTER(Table1[Sales], (Table1[Brand]=Brand) * (MONTH(Table1[Date]) = Month)), SUM(BrandRng))
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 :)
You can still create VSTO add-ins, but the new Office Add-ins model is now recommended because they have a smaller footprint and you can build them using almost any web programming language: docs.microsoft.com/en-us/office/dev/add-ins/
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
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 :-)
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.
Hi. I don't know if I understood correctly. The LET function is more efficient than SUMIFS. If so please shown (with some simple example) when the LET is more efficient than the SUMIFS?
In the relative references example, I would use the SUMPRODUCT formula. Any benefit of using FILTER in this example? Or is it just down to personal preference? "=SUMPRODUCT((MONTH(Table1[Date])=MONTH(H$26))*(Table1[Brand]=$G27)*(Table1[Sales]))"
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?
I would suggest instead using: =LET(SalesCY,SUM(C6:C15),IF(SalesCY>100,1.05,0.97)*SalesCY), but with the speed and memory of today's computers the difference is inconsequential.
Thanks for sharing your alternate formula, Bill. You're right, it won't make any difference because SalesCY is only evaluated once, i.e. when it's defined in the first step. So whether you reference it 2 or 3 times later, it won't result in any efficiency gain. That said, in this formula only one of the outcomes, TRUE/FALSE, is evaluated anyway. I like your formula though because it's shorter :-)
Wow it’s really a good function and video too!!! BTW I’m already an insider of 365, but I still can’t see this function yet? Do u have any idea? Thanks!
