LET Function Transforms Messy Formulas - Comprehensive Guide

[@citrocar1028]

There're so less number of women on the web doing intellectual and tech work. You're one of those few. Keep it up👏👏👏👏👏!

[@MyOnlineTrainingHub]

Thanks, I appreciate that!

[@suki9860]

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!

[@MyOnlineTrainingHub]

Glad you found the video helpful! Have fun withe LET.

[@fliptop157]

Last example on debugging rocks! Great way to manually evaluate your formula!

[@MyOnlineTrainingHub]

Glad you think so!

[@chonzadaniel]

Thanks Mynda, for the articulate demonstration of how the LET function works, providing various scenarios. It's a strong programming concept for Excel users!

[@MyOnlineTrainingHub]

Thanks for your kind words 🙏

[@dltaylormeng]

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!

[@MyOnlineTrainingHub]

So glad you found the video helpful, Dave!

[@josepepe741]

Thanks a lot for your presentation and channel. You are one of my favorites "Excel teachers"

[@MyOnlineTrainingHub]

❤ Thanks so much!

[@fokhrulislam2633]

That's the easiest examples to make it understand at once. Thank you.

[@MyOnlineTrainingHub]

Glad it was helpful! 🙏😊

[@msmith3090]

Finally got to use LET at work. Thanks for helping me understand it!

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@fransvanderwerf7485]

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

[@MyOnlineTrainingHub]

Wow, thanks for your kind words! Marry Christmas and best wishes for 2023 to you too 🎅

[@Deependra1991]

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.

[@MyOnlineTrainingHub]

Welcome aboard :-)

[@anaismartin7384]

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 !)

[@MyOnlineTrainingHub]

Glad you like it, Anais!

[@anv.4614]

Perfect function. perfectly explained. appreciated. Thank you so much, dear tutor.

[@MyOnlineTrainingHub]

Thanks so much!

[@pierreblanchard9752]

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

[@MyOnlineTrainingHub]

Great to know you found the Alt+Enter tip useful :-)

[@aiasaiascon3894]

This is such a magic function! indeed - this video deserves 1mio views!!!!!

[@MyOnlineTrainingHub]

Thanks so much! Please spread the word 😊

[@shawnward3494]

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

[@MyOnlineTrainingHub]

Thanks so much, Shawn!

[@zx-25r12]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@abe4214]

Excellent video, learnt a lot in few minutes....

[@MyOnlineTrainingHub]

Great to hear, Abe!

[@deependragupta7679]

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.

[@MyOnlineTrainingHub]

So pleased it will be useful to you, Deependra!

[@digitalkm]

Brilliant. Very easy to follow with your explanations. Thank you

[@MyOnlineTrainingHub]

You're very welcome!

[@davidgreen3723]

Allowing debugging! Mind blown! Thank you for this

[@MyOnlineTrainingHub]

Glad it was helpful, David :-)

[@suehallstrom3666]

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!

[@MyOnlineTrainingHub]

Thanks for your kind words, Sue :-) Glad it was helpful.

[@10ozGold]

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 :)

[@MyOnlineTrainingHub]

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.

[@10ozGold]

@@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 :)

[@udonnambaji5298]

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.

[@MyOnlineTrainingHub]

Awesome to hear :-)

[@-johnny-deep-]

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. ;-/

[@MyOnlineTrainingHub]

:-) glad you enjoyed the video.

[@jonburgett626]

@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!

[@Kris2510]

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'

[@meniporat3527]

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.

[@MyOnlineTrainingHub]

Thanks, Meni! Yes, being able to refer to a name previously declared is super useful.

[@johnborg5419]

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. : ) : )

[@MyOnlineTrainingHub]

Glad it was helpful, John :-)

[@roizengh]

Thanks for the video! This changes so many things I've done in previous spreadsheets. Started seeing your channel today as a recommendation from KZbin 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.

[@MyOnlineTrainingHub]

Wow, thank you! Awesome to hear :-)

[@wayneedmondson1065]

Hi Mynda. Great examples! Well explained and useful. Thanks for sharing :)) Thumbs up!!

[@MyOnlineTrainingHub]

Thanks so much, Wayne!

[@07XTX]

I am really pleased I found this channel - very well explained and presented. One of the best I have seen, well done

[@MyOnlineTrainingHub]

Thanks for your kind words and welcome aboard!

[@chahineatallah2636]

nice one, i will start to use it , rather than retyping the formulas again and again or copy pasting

[@MyOnlineTrainingHub]

Great to hear 😊

[@HM-wj4je]

LET's magic. Amazing video

[@MyOnlineTrainingHub]

Thank you!

[@NigelDomaingue]

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.

[@MyOnlineTrainingHub]

Great to know you'll be able to make use of the new LET function, Nigel :-)

[@sachinrv1]

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 :)

[@MyOnlineTrainingHub]

Glad you enjoyed it, Sachin!

[@sachinrv1]

That's a fantastic function. It reminds me the syntax of one more very useful function "Choose". Thanks for sharing.

[@MyOnlineTrainingHub]

Great to hear, Sachin :-)

[@sachinrv1]

@@MyOnlineTrainingHub subscribed !!!

[@_cmr211]

Such a great and informative channel. Thank you. So happy for youtube to recommend it.

[@MyOnlineTrainingHub]

Thanks so much, Bartosz :-)

[@georgetosounidis5545]

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.

[@MyOnlineTrainingHub]

LET sure is a great step forward for Excel :-)

[@ricos1497]

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.

[@MyOnlineTrainingHub]

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.

[@michaelmaharlika8569]

Another awesome lesson, thank you

[@MyOnlineTrainingHub]

Glad you liked it! 🙏

[@bramble-east]

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...

[@Luggruff]

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

[@IkaikaArnado]

Loops are obviously coming eventuality.

[@GeertDelmulle]

What? OK, just 2 words: SEQUENCE and LAMBDA. Pick your poison.

[@jashope100]

Excellent Tutorial and very useful function. Thank you

[@MyOnlineTrainingHub]

Glad you liked it, Jason!

[@olivierissaverdens6916]

Thank you Mynda! Another very clear and useful presentation!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Oliver!

[@williamarthur4801]

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.

[@MyOnlineTrainingHub]

Glad you found my video helpful, William! That's a good idea to do a video on being an Office Insider.

[@abdallah.kandiel]

Thanks a million mynda That was magical It saves times and clarifies formulas . Thanks for your help and for your time Appreciate 🤩

[@MyOnlineTrainingHub]

So pleased you liked the LET function :-)

[@shadrackawunyo7786]

Wow. Daz a killer video on LET Function. Well presented but requires more practice to master.

[@MyOnlineTrainingHub]

Thanks, Shadrack! I found it really easy to master. It's only limited by your imagination :-)

[@sandeepkothari5000]

Apart from programming like VBA, LET also reminds me of algebra done at school. Great video, Mynda.

[@MyOnlineTrainingHub]

Yes! Thank you, Sandeep!

[@DanBrill]

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. :-)

[@MyOnlineTrainingHub]

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

[@mariocamacho8176]

Excelente vídeo. Great explanation with very illustrative examples. Thank you.

[@MyOnlineTrainingHub]

Glad it was helpful, Mario!

[@stevereed5776]

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.

[@MyOnlineTrainingHub]

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

[@stevereed5776]

@@MyOnlineTrainingHub Thank you so much. It didn't work but will try the forum

[@IkaikaArnado]

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!!!

[@MyOnlineTrainingHub]

Great to know you'll be able to make use of the new LET function, Ikaika :-)

[@vanvinos]

What a magisterial presentation. Thank you.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@AshokKumar-sy2qt]

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.☺️

[@MyOnlineTrainingHub]

The only limitation of LET is your imagination ;-)

[@Heavieth]

Amazing! I just suggested my team subscribe due to all of your content being relevant. Thank you again!

[@MyOnlineTrainingHub]

Awesome, thank you, George!

[@barttitulaerexcelbart9400]

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..;)

[@MyOnlineTrainingHub]

Thanks, Bart! The notebook was an MVP gift from Microsoft ;-)

[@Goomba99-r9t]

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.

[@MyOnlineTrainingHub]

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.

[@charlesabraham7171]

excellent session

[@MyOnlineTrainingHub]

Glad you think so!

[@felipe4181]

Hi Mynda, That new formula is amazing!!!! Thank you!!!!

[@MyOnlineTrainingHub]

Great to know you're looking forward to getting the LET function :-)

[@Dansel_B]

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!

[@MyOnlineTrainingHub]

Glad it was helpful, Dansel!

[@gyozakeynsianism]

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.

[@MyOnlineTrainingHub]

Great to know you're looking forward to using LET. Be sure to use IFERROR as opposed to ISERROR for handling errors.

[@gyozakeynsianism]

@@MyOnlineTrainingHub Right! ISERROR is pretty straightforward by comparison ... Thank you.

[@kennethteh2498]

Thanks so much for this well explained and succinct introduction for this new function. Really liking what you're doing, keep it up Mynda!

[@MyOnlineTrainingHub]

You're very welcome, Kenneth!

[@maheshn9252]

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.

[@MyOnlineTrainingHub]

Glad you liked the new LET function, Mahesh!

[@seregv]

Seems to be a very useful function. Eager to try when it is released. Thank you for excellent explanation.

[@MyOnlineTrainingHub]

Glad you're looking forward to using the LET function :-)

[@jonsnow108]

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.

[@MyOnlineTrainingHub]

Great to know you'll be able to make use of it :-)

[@HarshColby]

Doesn't this still use a helper cell?

[@davematthews414]

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.

[@MyOnlineTrainingHub]

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 :-)

[@htrzewik]

Great function (milestone!) and great lesson! Thank you.

[@MyOnlineTrainingHub]

Glad you liked it, Hubert!

[@Deependra1991]

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.

[@MyOnlineTrainingHub]

Great to know you'll be able to make use of the LET function, Deependra!

[@IvanRosaT]

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!

[@MyOnlineTrainingHub]

Welcome to Insiders, Ivan :-)

[@Axewayboy]

Wtfffff... finally some increible useful for better performance and power ups

[@MyOnlineTrainingHub]

Great to hear you're looking forward to using the new LET function :-)

[@lauphyon]

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!

[@MyOnlineTrainingHub]

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))

[@lauphyon]

@@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!

[@sasavienne]

Thanks a lot.. 🌟 🌟 🌟 I could learn new skills through this tutorial..

[@MyOnlineTrainingHub]

Happy to help!

[@kama297]

Neat! Great presentation, as always. Thank you so much, Mynda!

[@MyOnlineTrainingHub]

Thank you! Glad you enjoyed it :-)

[@1kdthompson]

Like the hair cut! Cute!

[@MyOnlineTrainingHub]

Thank you!!

[@akileshsharma2523]

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.

[@MyOnlineTrainingHub]

Thanks, Akilesh!

[@TALKmd]

Amazing , this is Coding without Vba , and its absolutely elegant

[@MyOnlineTrainingHub]

Glad you like it!

[@mahfuzulhaquenayeem8561]

Thanks a lot for it. Simply, great!

[@MyOnlineTrainingHub]

Glad you like it!

[@vii5ri]

What is different between (1- Average) and (101-Average) 8:00 ?

[@MyOnlineTrainingHub]

The SUBTOTAL function allows you to choose whether hidden rows are included or excluded from the total, 1 includes them, 101 excludes them. More on SUBTOTAL here: www.myonlinetraininghub.com/excel-subtotal-formula-explained

[@Diallo268]

that is pretty useful. Anything too large I just do it in VBA. So much more powerful and flexible.

[@MyOnlineTrainingHub]

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 ;-)

[@Diallo268]

@@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.

[@julianstarkey9301]

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

[@MyOnlineTrainingHub]

Great to know you'll be able to make use of the LET function, Julian!

[@julianstarkey9301]

And when I went to excel to use it it wasn’t there despite being on the insider programme, already using unique and filter.

[@bravucod]

Great tutorial as always!!!!

[@MyOnlineTrainingHub]

Thanks so much, Daniel!

[@jamesrawlings8493]

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.

[@MyOnlineTrainingHub]

Indeed, James, with the LET function the SUM will only be evaluated once, whereas with the IF it would have been evaluated twice.

[@hamesha4u]

Hi Mynda, Very interesting video about this function. Simply amazing!! Request you to please make VBA tutorial.. It will be very helpful. Thanks

[@MyOnlineTrainingHub]

Glad you like the LET function, Rakesh. Have fun with it.

[@Diallo268]

Excel Macro Mastery has some good VBA tutorials. Good info on the website too. I would start there.

[@IkMag]

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

[@MyOnlineTrainingHub]

Interesting, Wouter! I'll have to try it out. Thanks so much for sharing.

[@yiubili1]

And thanks very much for thus great video

[@MyOnlineTrainingHub]

My pleasure, Bili :-)

[@joesmith4254]

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]))"

[@MyOnlineTrainingHub]

Hi Joe, the difference being that FILTER will spill the results, whereas SUMPRODUCT must be copied and pasted to create the summary table.

[@casst346]

nope don't have that function available..however, thanks for the training..

[@MyOnlineTrainingHub]

It’s always good to know what it’s capable of. Hopefully you’ll get a new version of Excel soon.

[@kitemg]

Thats really great! Thats incredible useful :D unfortunatelly we dont use m365 in the company :(

[@MyOnlineTrainingHub]

Hopefully when your company next updates their software you can convince them that Microsoft 365 is the way to go!

[@abdulhaseeb8027]

Mind Blowing.

[@Ryvarya]

Simply awesome

[@MyOnlineTrainingHub]

Thanks so much 😊

[@Ryvarya]

@@MyOnlineTrainingHub but my excel 365 is not takin Let formula, its not upgraded I guess...

[@ravishete2602]

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?

[@MyOnlineTrainingHub]

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.

[@ajit555db]

In the example IF formula, if cell address C16 is used instead of SUM(C6:C15), still sum would be calculated three times?

[@MyOnlineTrainingHub]

I'm not sure where you mean to replace SUM with C16, if it's the logical test, then no, it would only be calculated once.

[@francois-xavieraubry4670]

Trying to find a way to write a formula in a cell without the equal sign and run it in another one with indirect. Does this function can be part of the game ? Thx

[@MyOnlineTrainingHub]

Hi Francois-Xavier, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@rwfrench66GenX]

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?

[@MyOnlineTrainingHub]

Thank you! The tie break is decided based on the sort order of the list. Well spotted ;-)

[@rwfrench66GenX]

Thank you so much! I've had a similar issue with VLOOKUP.

[@oliverrojasconde9144]

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...?

[@MyOnlineTrainingHub]

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.

[@ach301176]

When will the GET function be available for the public? I have Microsoft 365 and don't have this function yet.

[@MyOnlineTrainingHub]

We don't have an ETA on general availability of the LET function yet, sorry. If you sign up for the Office Insiders channel you can have it right away: insider.office.com/en-us/

[@soy34mb]

Love your videos!

[@MyOnlineTrainingHub]

Thank you, Jimmy :-)

[@Luciano_mp]

👍👍👍...Thank you Mynda.

[@MyOnlineTrainingHub]

Cheers, Luciano :-) Have fun with LET!

[@rrrprogram8667]

As usual brilliant video.... Why dont you make a course in udemy

[@MyOnlineTrainingHub]

Thank you! I have my own site with my courses on: www.myonlinetraininghub.com/

[@Creek5Romeo]

Can Xlookup replace the Index/Match in the second to last sheet?

[@MyOnlineTrainingHub]

Yes, but it'd have to be a nested XLOOKUP and personally I prefer INDEX & MATCH in these instances as I think it's easier to understand.

[@李泓範]

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!

[@MyOnlineTrainingHub]

Thank you! If your install of Excel is on Insiders you should have this function. I'm on version 2005 build 12810.20002. Maybe you need to update?

[@kurtkessler3664]

Are the named variable private to the LET()? Or can you use them elsewhere in the workbook like a named range?

[@MyOnlineTrainingHub]

Hi Kurt, the LET named variables are only available inside of that LET formula. You're better off using defined names if you want them globally available.

[@siysf]

Great video, i hope i can use this function asap. Btw, i can use xlookup function but can't use # in my references.I don't know what's going on 😔

[@MyOnlineTrainingHub]

Glad you're excited about using LET. I suspect there is something solvable with your # reference issue. Please share a sample Excel file on our forum where we can reproduce the issue and we can help you further: www.myonlinetraininghub.com/excel-forum