Thank you so much for pointing out the LET (and IFS) functions. This has drastically simplified a very complex formula we have been using. Our existing formula was 1615 characters in length, and with just the LET function it reduced the formula length to 487 characters. With a combination of the LET and IFS functions, it reduced the length of the formula to 469 characters. Here is the end result using LET & IFS: =LET(x,INDEX(TableName,MATCH([@sale],[sale],0),MATCH(Adj1_Element,TableName[#Headers],0)),a,Adj1_a,b,Adj1_b,c,Adj1_c,d,Adj1_d,e,Adj1_e, IFS( Adj1_Graph_Type="Exponential",a*EXP(b*x), Adj1_Graph_Type="Linear",a*x+b, Adj1_Graph_Type="Logarithmic",a*LN(x)+b, Adj1_Graph_Type="Polynomial (2 order)",a*x^2+b*x+c, Adj1_Graph_Type="Polynomial (3 order)",a*x^3+b*x^2+c*x+d, Adj1_Graph_Type="Polynomial (4 order)",a*x^4+b*x^3+c*x^2+d*x+e, Adj1_Graph_Type="Power",a*x^b, TRUE,"")) While I have not measured any performance improvement, since I have only run a limited test, I am sure it is there.
[6:17 thaattttt (breaking up using alt + entere) is new to me and what i needed for so long. thanks for including that. and glad i clicked this vid. 7:53 conditional formatting
Hear hear! Never mind all the new functions Leila is showing us, even experienced Excel users; it’s these usability tips that are of equal if not more importance. ps, I only found Win+. for emojis this afternoon by accident, then an hour later Leila shows us!
This is SO useful. I had a formula that basically consisted of pre-modifiers, the main formula, and post-modifiers. But the post modifier are dependant on the outcome of the main formula. So for each post-modifier I had to add the entire main formula again. This made the code extremely long since the main formula was a string of 20 if checks. Now it's just 3 neat sections of close split with nice breaks.
First video of yours I've watched and I hit that bell at the 5:00 mark. Your depth of knowledge, ease and speed of use of Excel, and clarity of communication combines into an informative video that surpasses all other Excel videos I've ever watched by everyone, ever. Your quality and professionalism is the gartner quadrant that has been missing in the excel video community, until now.
A very thorough treatment of this little useful function. Speaking of performance, I remember how, about the time you were born, a 100 x 20 sheet was recalculated for several seconds (VisiCalc on Apple ][), and auto-recalc was usually turned off.
I am regular watching UA-cam channel, big thanks for you make lot of videos especially on excel, I think I am becoming expert on excel because of you, thanks 🙏 please continue making videos on excel and also on MS Word
Great video... as always :) When you have tables with long column names and these get repeated in formulas it can make them really hard to read, so using LET will make these much easier to read and maintain - all we have to do is look at the variable at the beginning and we'll see which columns this refers to throughout.
Hi@@LeilaGharani , I forgot to mention that using the LET function allowed me to add comments easier in the formula by adding a comment line at the start and then change line for the rest of the formula: = LET( COMMENT; N("......"); .....)
I think through some of the crazy 10-line formulas I've written over the years and my mind is blown at how much easier this would/will be!! Thanks for the great work and video Leila!
Hi, I have applied this to my report for payroll and the outcome is really very effective in terms of times and efficiently.. Really love the part whn you analysed and compared the time reduction between w/wo LET... Thank you again Leila... :)
Great to see speed tests on LET function!!! Think it's the first one out here. Many thanks for that!!. A great advantage of LET is it's kind of portability. We can write a complex formula in one workbook, copying it as a string, pasting it in other workbook , changing only the variables reference addresses , and good to go. No VBA😉
That second example is crazy, I'm guessing because it can grab the complex function from the variable established, instead of reading it each time it is faster; however, when doing small calculations the time to set the variable initially ever so slightly outways the benefit in cost of time. REALLY awesome stuff, can't wait for that official launch.
LET function is very useful . The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula
Thanks Leila! Your explanations and use cases are awesome, but by taking the time to measure it against larger datasets, you have encouraged me to use this function without fear of it slowing down my workbooks. These sorts of above-and-beyond insights are what make your channel so useful to me. Thanks again!
Love your tutorials and the clear way you present them. I keep watching them over and over till it sinks in. Also love the Excel downloads so I can play with them..
Excellent video; it had me using LET, with enthusiasm, in no time. I have one issue though. Not to do with LET but you have the VAT rate embedded in a formula; I consider this to be bad practice. VAT can, in theory, change at very short notice. Ideally it would be stored as a global somewhere but more realistically it should be in a cell where it's visible. The same applies to values like income tax rates and thresholds: any constant that isn't constant.
Wow, I just saw a way with your video how I can already improve lots of formulas. I have formulas that is repeated way too often. LET looks like the solution to those sheets of mines, thank you!!!
This is great.Many thanks for this tutorial quiet straightforward and impactful. I see let function working more like the variable function in powerbi.
Hey Leila, Thanks for making a video on LET. It is indeed a great function. Just a sugesstion - you could use IFS for multiple conditioning in a single formula instead of using IF statement several times. It will look neat and easy to read. 😊
Hi Leila.. great in-depth summary of the advantages of the LET function. The timing comparisons were particularly interesting. Thanks for sharing your insights. Thumbs up!!
I have found that most speed improvements are negligible unless dealing with large datasets. The kind of datasets not typically seen in small businesses. BUT, I have always been a fan of making formulas readable understandable at a glance by someone that did not wite the code. I am frustrated sometimes when analyzing someone else's code when they do not use common conventions or make use of named ranges. Most people can understand Assets=Capital - Liabilities easier to read than some combination of summed cells - other summed cells.
@@iiiiii-w8h because it is functional programming where there is no loops. Instead, you use an call to the previous value. And repeat It through the desirable range. There is your loop.
Being a computer science teacher and programming enthusiast, I really enjoy the idea of examining computational efficiency. In the early 80's, using 8 bit computers, it was a real art to keep programs small and efficient. This art has been de-emphasised in the modern era due to the speed of computers and availability of lots of memory. In your second analysis, the Let function sped up the calculation by almost double which is very impressive by computational standards. In the context of manipulating 700 000 items and dealing in milliseconds however, this illustrates why I have stopped looking to make my programs and files efficient. Aside from pride in efficiency, you will never see the difference that efficient code makes because computers are now too fast to appreciate it.
I've been so frustrated that Excel didn't have such functionality before, principally for readability and maintenance. Wasn't aware of LET it until I saw this. (Interesting point about performance on the simpler functions too.) Thanks you
Me finally getting around to learning Lambda last week thinking it was more like this. I knew there was a way I could define my own variables without all the extra rigmarole of naming lambda functions.
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/let-function-file
Thank you so much for pointing out the LET (and IFS) functions. This has drastically simplified a very complex formula we have been using. Our existing formula was 1615 characters in length, and with just the LET function it reduced the formula length to 487 characters. With a combination of the LET and IFS functions, it reduced the length of the formula to 469 characters.
Here is the end result using LET & IFS:
=LET(x,INDEX(TableName,MATCH([@sale],[sale],0),MATCH(Adj1_Element,TableName[#Headers],0)),a,Adj1_a,b,Adj1_b,c,Adj1_c,d,Adj1_d,e,Adj1_e,
IFS(
Adj1_Graph_Type="Exponential",a*EXP(b*x),
Adj1_Graph_Type="Linear",a*x+b,
Adj1_Graph_Type="Logarithmic",a*LN(x)+b,
Adj1_Graph_Type="Polynomial (2 order)",a*x^2+b*x+c,
Adj1_Graph_Type="Polynomial (3 order)",a*x^3+b*x^2+c*x+d,
Adj1_Graph_Type="Polynomial (4 order)",a*x^4+b*x^3+c*x^2+d*x+e,
Adj1_Graph_Type="Power",a*x^b,
TRUE,""))
While I have not measured any performance improvement, since I have only run a limited test, I am sure it is there.
[6:17 thaattttt (breaking up using alt + entere) is new to me and what i needed for so long. thanks for including that. and glad i clicked this vid.
7:53 conditional formatting
Hear hear!
Never mind all the new functions Leila is showing us, even experienced Excel users; it’s these usability tips that are of equal if not more importance.
ps, I only found Win+. for emojis this afternoon by accident, then an hour later Leila shows us!
This is SO useful. I had a formula that basically consisted of pre-modifiers, the main formula, and post-modifiers. But the post modifier are dependant on the outcome of the main formula. So for each post-modifier I had to add the entire main formula again. This made the code extremely long since the main formula was a string of 20 if checks. Now it's just 3 neat sections of close split with nice breaks.
Started watching your videos for the view and then realized that I learned lot of short cuts in excel. Thanks great job
Finally got to use LET at work. Thanks for helping me understand it!
¡Gracias!
Thank you Robert!
Leila, I'm a heavy excel user and I love your videos ! You are always clear and to the point. Greetings from Brazil !
Let us all have more fun efficiency now that LET has arrived! Thanks, Teammate!
You bet Mike ;)
Would love to see a collaboration between you two. That’ll be like the Avengers version of excel.
Yes please. Would be like 2 DJs mixing side by side.
Understanding from one time from Mrs. Leila GHarani Videos
Thanks
I have to admit i like the way you teach, very easy to follow. I am now going to start using LET!
Great to hear!
Amazing and most important is your way of explanation is so simple and easy to understand any complicated formulas... Thanks
You are making my job so easy..... Thank you Leila... You are a savior
You are a true professional... You deserve all the best in life
Thank you!
Ma’am you are a great teacher... i found you just 2 days back and i have become crazy about your videos. Thank you so much🙏 from India
Great demonstrations here, especially the application beginning at 07:00.
What I great way to show how we can use Let! Thanks!
First video of yours I've watched and I hit that bell at the 5:00 mark. Your depth of knowledge, ease and speed of use of Excel, and clarity of communication combines into an informative video that surpasses all other Excel videos I've ever watched by everyone, ever. Your quality and professionalism is the gartner quadrant that has been missing in the excel video community, until now.
Wow, thank you Morgan! Glad to have you onboard :)
Your way of teaching is very very good and easy to follow.
Thanks a lot, Felipe!
A very thorough treatment of this little useful function.
Speaking of performance, I remember how, about the time you were born, a 100 x 20 sheet was recalculated for several seconds (VisiCalc on Apple ][), and auto-recalc was usually turned off.
What an excellent teacher - clear explanation and meaningful yet still clear examples.
Glad you think so, Gary!
you are awesome, your explanation is out of this world, so easy to understand, nothing like this i have ever seen before. thank you Leila
Wow, thanks!
A function LET in programming mode, declaring variables. Thank you for this fantastic tutorial Leila!
Glad you enjoyed it, Iván!
Thanks Leila! a great example of productivity.
So brilliant. The LET function is so great. You're a an amazing teacher. Thanks for sharing
Thanks so much!
Im very late to the party in trying to learn this function but this is very well explained and delivered. Thank you... subscribed!
Happy to have you aboard!
I am regular watching UA-cam channel, big thanks for you make lot of videos especially on excel, I think I am becoming expert on excel because of you, thanks 🙏 please continue making videos on excel and also on MS Word
Great video... as always :) When you have tables with long column names and these get repeated in formulas it can make them really hard to read, so using LET will make these much easier to read and maintain - all we have to do is look at the variable at the beginning and we'll see which columns this refers to throughout.
Since I watched this video a few months ago and learned about the "LET" function, I'm using it every day since. Thank you. 🙂
Excellent! Glad the video was helpful.
Hi@@LeilaGharani , I forgot to mention that using the LET function allowed me to add comments easier in the formula by adding a comment line at the start and then change line for the rest of the formula: = LET( COMMENT; N("......"); .....)
A lot of the formulas i write for excel repeat sections of the formulas over and over so this will make them a lot shorter. thanks for the vid
I did not see the point at the begining but it's really useful! thanks Leila!
quality of your videos are getting better and better. Congrats
I appreciate that!
I don't have time to watch all your vids but whenever I did watch, I am glad I did.
Great to hear, Amy.
I think through some of the crazy 10-line formulas I've written over the years and my mind is blown at how much easier this would/will be!! Thanks for the great work and video Leila!
It really can make a difference for these complex formulas.
@@LeilaGharani How to import live data excel to spreadsheet? Please help me. My e-mail adress is melikov_xalid@yahoo.com
I've been grumbling that Excel should have this feature for years. Now i'm super excited!
That # for value lists is good to know.
Thanks for sharing Leila! Awesome tuto!! 👍🏼
Hi, I have applied this to my report for payroll and the outcome is really very effective in terms of times and efficiently.. Really love the part whn you analysed and compared the time reduction between w/wo LET... Thank you again Leila... :)
Great to hear!
Great to see speed tests on LET function!!! Think it's the first one out here. Many thanks for that!!. A great advantage of LET is it's kind of portability. We can write a complex formula in one workbook, copying it as a string, pasting it in other workbook , changing only the variables reference addresses , and good to go. No VBA😉
Thank you! That's right. It'll make managing workbooks easier.
That second example is crazy, I'm guessing because it can grab the complex function from the variable established, instead of reading it each time it is faster; however, when doing small calculations the time to set the variable initially ever so slightly outways the benefit in cost of time.
REALLY awesome stuff, can't wait for that official launch.
LET function is very useful . The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula
Thanks Leila for your suggestions, it worked, I just added more columns to the formula
Glad it helped, Jean!
Mam you're fabulous Excel tutor I had ever seen.👌👌
Thanks a lot 😊
I appreciate living in the age free education and great teachers. Thanks Leila.
I have learnt so much from this channel ❤❤❤ it has really helped me in my work.
That's awesome! Thank you.
Best channel to learn excel
Thanks Leila! Your explanations and use cases are awesome, but by taking the time to measure it against larger datasets, you have encouraged me to use this function without fear of it slowing down my workbooks. These sorts of above-and-beyond insights are what make your channel so useful to me. Thanks again!
Glad it's appreciated, Walter!
Love your tutorials and the clear way you present them. I keep watching them over and over till it sinks in. Also love the Excel downloads so I can play with them..
Glad you like them, Mireille!
Indeed! LET function would be a one of the most with FILTER function to make the excel more powerful. Thank you for sharing the great knowledge
You're very welcome!
Your video lectures are excellent
Glad you think so!
Hi Leila, this is very timely because I am working on a project that this function will prove useful.
Great! Hope it will come in handy for you.
It was amazingly enjoyed, thanks a lot 🌹
Coming from a programming background and only now learning Excel, this is a huge deal.
Leila, congrats on excellent videos. I like your pace, and your ability to explain this to us. All the best.
Thank you very much, Alain!
As always clear and direct. Thanks Leila
Learned a lot here. Not just the LET function. Awesome video! Subbed!
Thanks for the sub, Mark!
definitely need this LET in my big data files and long repeated formula elements
Nice use of LET and icons. 🔥
Thanks Oz 🔥
Excellent video; it had me using LET, with enthusiasm, in no time. I have one issue though. Not to do with LET but you have the VAT rate embedded in a formula; I consider this to be bad practice. VAT can, in theory, change at very short notice. Ideally it would be stored as a global somewhere but more realistically it should be in a cell where it's visible.
The same applies to values like income tax rates and thresholds: any constant that isn't constant.
Please teach about formula distribute the amount proportionally on different things. Thanks
Great and valuable vid. Thanks and stay safe. Greetings from Portugal
Thank you!
Wow, I just saw a way with your video how I can already improve lots of formulas. I have formulas that is repeated way too often. LET looks like the solution to those sheets of mines, thank you!!!
You're so welcome!
Thank you for sharing!! This is really useful!!
That's one of the most useful functions I have learned! Your videos are just terrific.
Glad you like them!
This is great.Many thanks for this tutorial quiet straightforward and impactful.
I see let function working more like the variable function in powerbi.
Hey Leila, Thanks for making a video on LET. It is indeed a great function. Just a sugesstion - you could use IFS for multiple conditioning in a single formula instead of using IF statement several times. It will look neat and easy to read. 😊
Yes ;) ua-cam.com/video/AOO1AoTNdZk/v-deo.html.
For some reason nested IFs are still my go-to. I'm sure I'll overcome this sometime in the future 😁
Great comparison of performance
Thanks for the video. Interesting.
Thanks
are you from India...if yes than its awesome, now we have someone who is compatible with international standards...good job.
Nice background setup.
It's very wonderful to watch your video
Glad you liked it :)
Hi Leila.. great in-depth summary of the advantages of the LET function. The timing comparisons were particularly interesting. Thanks for sharing your insights. Thumbs up!!
I have found that most speed improvements are negligible unless dealing with large datasets. The kind of datasets not typically seen in small businesses. BUT, I have always been a fan of making formulas readable understandable at a glance by someone that did not wite the code. I am frustrated sometimes when analyzing someone else's code when they do not use common conventions or make use of named ranges. Most people can understand Assets=Capital - Liabilities easier to read than some combination of summed cells - other summed cells.
Thanks Leila for this tutorial of LET function ! You are simply ahead of everyone! 💛💛💛🧡🧡🧡
I can use this A LOT. Thanks!
Great topic, awesome video and beyond perfect tutor. Thank you.
The addition of the LET function means that Excel formulas are basically becoming like a programming language where declaring variables is the norm.
EXACTLY
Still no loops tho
Python is making Excel increasingly archaic and I love it
@@iiiiii-w8h because it is functional programming where there is no loops. Instead, you use an call to the previous value. And repeat It through the desirable range. There is your loop.
you could use VB to code them pretty much the same. it's just inline instead of function now.
Congratulations from Rio. Your videos help a lot and you explain the concepts very well.
Awesome, thank you!
Yooo this could be a game changer. I tend to use IF as a decision tree abs this would make it clearer
Thanks for giving such a great opportunity to learn on skillshare..I have no words except you are great and nice as always ...Thanks
...and then God said, "LET" there be "Leila" and our Excel problems are solved! :-) Thank you for another great video!
That is true !!!😊
😀
💖 ... just playing with the Win-dot shortcut 😊
Being a computer science teacher and programming enthusiast, I really enjoy the idea of examining computational efficiency. In the early 80's, using 8 bit computers, it was a real art to keep programs small and efficient. This art has been de-emphasised in the modern era due to the speed of computers and availability of lots of memory. In your second analysis, the Let function sped up the calculation by almost double which is very impressive by computational standards. In the context of manipulating 700 000 items and dealing in milliseconds however, this illustrates why I have stopped looking to make my programs and files efficient. Aside from pride in efficiency, you will never see the difference that efficient code makes because computers are now too fast to appreciate it.
Thanks for sharing your experience!
Very good one. Thanks. This can help on mobile, as apps doesn't give access to Name manager menu.
I have to like the video befor watching it. I know it will come in useful one day.
Thanks for the trust :)
Love that function I have created many stacked if statements with similar formulas....I will try this function out 😎
I've been so frustrated that Excel didn't have such functionality before, principally for readability and maintenance. Wasn't aware of LET it until I saw this. (Interesting point about performance on the simpler functions too.) Thanks you
Thanks for increasing my interest immensely in learning more
Me finally getting around to learning Lambda last week thinking it was more like this. I knew there was a way I could define my own variables without all the extra rigmarole of naming lambda functions.
Beautiful White Chester sofá. Love it !
Me too!!
thank you leila like let function its simple,
You should VLOOKUP(K4:L6)" for the range, out of range IF statement instead of typing IF B4
👏🏻👏🏻👏🏻👏🏻 this is going to be very useful for complex financial models. Thanks Leila
You're so welcome!
Fantastic function I learned from you today! Thank you!
You're very welcome!
Thank you for this. This is such a game-changer for me.
Very interesting video!!
I love your videos, you are very smart
Thank you. But I'm not smart. Just like to learn and share.
@@LeilaGharani sure you are, you know a lot and you are a great teacher. Glad I found your channel ❤
Thank you Leila
Thanks! You have great tutorials. I'm fairly advanced, but I always find your videos helpful!
Great to hear, David!
Why you make my live easier
Ms Leila you rock😊
Thanks Leila for the knowledge.