Excel LAMBDA, LAMBDA Helper Functions & Eta-LAMBDAs: the Complete Story - Updated 2024: 365 MECS 10
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel Start file: excelisfun.net/files/10-M365E...
Download pdf notes: excelisfun.net/files/10-M365E...
Download Excel Finished file: excelisfun.net/files/10-M365E...
This is a 1:45 hour video about everything that the Excel LAMBDA function, LAMBDA Helper Functions & Eta-LAMBDAs can do. This video is also a complete lesson in Defined Names, the LET function and Spilled Single Cell Formula Reports.
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (01:18) Overview
3. (03:09) Defined Names
4. (13:00) First look at LAMBDA to create a re-usable function
5. (19:25) Advanced Formula Environment
6. (24:08) Summary of LAMBDA
7. (25:50) Rate Of Change Re-usable LAMBDA function
8. (28:33) COGS Re-usable LAMBDA function created in Advanced Formula Environment
9. (30:55) Save LAMBDA to default file
10. (32:44) Show Formula Re-usable LAMBDA function
11. (36:35) LAMBDA Helper Functions
12. (37:24) BYROW and BYCOL functions
13. (39:41) Eta-LAMBDAs
14. (42:16) MAP function
15. (50:46) SCAN function
16. (54:01) REDUCE function, initial look
17. (55:13) Recursion with LAMBDA
18. (01:04:24) REDUCE function, introduction and three examples
19. (01:14:48) MAKEARRAY function
20. (01:16:34) LET function
21. (01:17:46) GROUPBY and PIVOTBY functions
22. (01:22:29) Add calculation label to Single Cell Formula Report: 2 methods
23. (01:24:00) Single Cell Formula Report with two conditions in row area
24. (01:25:53) Conditional Formatting For Dynamic Reports
25. (01:28:25) Add two different calculations to a Single Cell Report
26. (01:31:10) PIVOTBY function
27. (01:33:05) PIVOTBY function to analyze increases and decreases in sales
28. (01:35:02) Goal Seek with the PIVOTBY function
29. (01:36:08) Fully Dynamic PIVOTBY report using formula inputs from cells using the functions: XLOOKUP, CHOOSE and XMATCH.
30. (01:40:15) CHOOSE and XMATCH functions to lookup a function
31. (01:41:53) Add dynamic calculation label to report using LET, ROWS, COLUMNS and SEQUENCE functions
32. (01:45:13) Finance and Statistics LAMBDA re-usable function examples
33. (01:45:52) Homework
34. (01:45:55) Summary and Conclusions
35. (01:46:41) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/... . Artist: audionautix.com/
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #groupby #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda #excellambda #lambdahelper #etalambda
To create a spilled report with 2 or more calculations, use this:
=GROUPBY(C3:C31,F3:F31,HSTACK(SUM,PERCENTOF),0)
not the one I showed in video at 01:30:00 minute park.
This trick comes from SoftwareTrain at RUclips : )
Go Team!!!!!
Yep! This was the first thing I rushed to check 2 days ago when MS activated this beauty for me, almost immediately after Mike's post :) :)
Excel's fun must go on!
VSTACK works as well and for some tasks is preferable since provides "relational" (record per row) form of data.
btw, "Goal Seek" is here: Data/What-if... Alt, A, W, G
@@viktorasgolubevas2386 Thanks for the Goal Seek keyboy : ) The more keyboards, the more fun we have, and the more smoothly we flow thorough the raddness at hand : )
He's back!!! Excel never stsnds still MECS: Mike's Evolving Complete Story. In the MECS context "complete" is Aspirational, Relative and extremely Time Sensitive!! Excel refuses to stand still!
So true!!!! It is hard to be an Excel teacher because as soon as I post a video, MS changes something lol
You have a real talent, Mike, for making tough things seem easier. I love your recursive LAMBDA that removed extraneous characters in words in a dynamic array. It worked great, but instead of replacing the characters with a space and removing the spaces with TRIM(Text), I wanted to change it so I could replace the bad characters with a good one. For example, replace vowels in a list of words with an asterisk. I figured this would be a great exercise, and give me some practice debugging recursive formulas. What I came up with was
ChangeChar(Text, Remove, Replace)=LAMBDA(Text,Remove,Replace, IF(Remove="", Text, ChangeChar( SUBSTITUTE(Text,LEFT(Remove),Replace), RIGHT(Remove,LEN(Remove)-1)))),
and I added ChangeChar to my Name list. When I use the parts step-by-step outside the Lambda (in a table), it works fine. But when I run the Lambda I get an error that says I need three parameters. I already have three parameters! How can I debug this? How to debug a Recursive LAMBDA that is failing? Maybe a neat topic for a future tutorial!
Since 2008, I have not learned from anyone more than you.
I wish I could have liked each of your videos 100 times.
Thanks
Thank for the 100 likes : ) : ) : ) : ) : ) * 20!!
@excelisfun that is not a lie
I have been watching your videos since the beginning
Tell 100 people
Tell them to like them
Glad to see you're back :) :) Thanks for this epic video!!!
Glad to boomerang back and provide some fun epic, Formula Guy John : ) : ) : )
Welcome back Mike, the Team missed you. I'll be watching this one later, thanks!
Thanks, Chris! It is great to boomerang back : ) : )
Welcome back Mike 🎉
Thank you, Bani : ) : )
The best, most comprehensive, most in-depth video about Lambda function.👍❤ This is really a gem tutorial after you have been missed for the past weeks. Thank you Mike 🌹
Glad you like it : ) : )
So happy to see you back! Hope all is well and that you had a good Holiday and Happy New Year!
Happy New Year, Jerry!!!!
This class I have seen it till 1:20:00 is going into the depths of code computing
Excel is becoming more user input based application which is dynamic and mostly user applied definition
Day will come when excel will be classified as an RDBMS software with unique features of data analytics
And it is because of you we are lucky to learn ahead of time
Nicely said : ) : )
I am greatfull that even in this hard time, you are able to this videos.
Thank you for your unwavaring will to do videos!
Your videos are absolute best Knowladge in excel!
There is no one better!
Thank you :)
Thanks for your kind words, My Rad Friend!!!!
Amazing video, Mike! It's great to have you back; you were greatly missed. To contribute to your content, I suggest that at 1:30:00, you could also use the following formula:
=GROUPBY(C3:C31, F3:F31, HSTACK(SUM, PERCENTOF)) .
Wow!!!!!!!!!!!!!!!!!!!! : )
I love it : ) : ) : )
Thanks SoftwareTrain. I added it to the download workbook and pinned a comment to the top and I will make a video soon.
Go Team!!!!
It'll be useful when groupby is widely available. I'll circle back to this course in the next few month. Hopefully Microsoft will make this available soon.
Great epic video! As always, lots of details and explaining all the behind-the-scenes logic of each solution. Thanks for such quality resources. Keep going Mike, you are the best!
Glad it is epic for you, gvitullib!!!!
Welcome back Mike, great to hear from you🌷
Thank you, Anwar!!!
Welcome back Mike. You've been missed by all! Here we are many months later, and I still do not have the GroupBy function.
Sorry about the GROUPBY. Come on, MS - bring it on to all M 365 Excelers!!!!!
I love all your video's I have learned so much over the last couple of years in excel and 80-90% comes from your content. I watch other excel content creators and they do good work but I don't know. Maybe its how you deliver the content or the enthusiasm or just the genuine love for excel but you are my go to for anything excel related. Slaying excel dragons has a permanent place on my desk next to the computer. Thank you for all the work you put into these videos and sharing your love of such an amazing, powerful and often underestimated tool.
Thank you for your kind words!! I am happy that my content can help : )
Nice coverage of LAMBDA's and the support functions. Is there a place I could go to enjoy more Excel fun and discussions.
I will say that there was one point I disagree with. There is a way to test a recursive Lambda function without adding it to the Name Manager. Here is a simple example:
=LET(Loop,LAMBDA(ME,x, IF(x < 2, 1, x + ME(ME, x - 1))), Loop(Loop, $C$8))
The actual LAMBDA is but it illustrates the concept. By assigning the LAMBDA function to the Loop variable, the function at the end is able to call the Loop lambda.
Wow!!! I did not know that. Thank you for that hot tip : ) : ) I do not have a discussion board, but the best one I know of for last 20 years is: mrexcel.com/board
So happy you're back
We all learned from you so much
I hope to you the best
Thanks, Egyptian Accountant!!!
@@excelisfun "Even in this hard time you are able to make videos...". What happened Mike is he not feeling well?
@@MariaSaleem-gi4uj For last year, I had to take care of my mom, then she passed away, and now I am settling her estate. Part of my duty in life : )
@@excelisfun I am sorry to hear that. You are such a good son of your parents.
Wow I am only half way but sure this is an epic video! So well explained. Thanks Mike for explaining how to put this amazing LAMBDA (FORMULATEXT) formula into an XLT file. I can not create it in the default directory (authorisation..😉), but I can store it anywhere else. If I need it I just open this file and move one sheet to my current file (all names are copied, this is annoying but in this case very useful. Looking forward to see the rest of the video...
Glad you like this! I, like you, use this ShowFormulas in almost every workbook : ) : ) : )
Thank you amazing Mike for this EXCELlent video.
You are welcome, Amazing Fellow Teacher!!!
Happy new year Mike:)
So glad you are back.
I am glad to boomerang back. Happy New Year!!!
@@excelisfun ❤️
Happy New Year Mike!
I would love to spend a day with you. This is some of your finest work. Thank you so much
That is how long it will take to study and have fun with this video - so perfect ; )
Danke!
Thank you for your kind donation, Baenzu!!!
Happy New Year and Welcome Back! :-)
Hope everything is sorted with the family.
…and again an epic video in the MECS series: simply amazing and downright awesome!
We’re in for a treat. :-)
PS: those new shiny functions GroupBy, etc. What’s taking so long in the Beta Channel (the other 50%)?
Any ideas?
MS is really bad. They are so inconsistent in when then release. Shame on them. Great to boomerang back and Happy New Year, Geert!!!! : )
@@excelisfun I totally agree !
I am also waiting now for two months for the update in the beta channel ... and I let it go ... hopefully the new year ...will bring them to their senses
In any case ... excellent video Mike!
Highly informative!( mMall point: Sequential is misspelled "Sequencial" in the recursion discussion.)
He's back!
Just like a boomerang!!!!
Happy New Year Sir Thanks for Epic this Epic Video
You are welcome for the Epic!!!!!
Mike, be continue excel magic trick....
I am slow with Excel Magic Tricks because of my Mom's passing. It has been 4 months of hard family work and about 2 more months to go. There will be many more Excel Magic Tricks soon!!!! : ) : )
@@excelisfun lam sorry to hear that..
Welcome back ❤
Thanks, Amr!!!!
I adjust spreadsheets which have text data like "Mon 20-Mar-2023 07:45pm" and stakeholders want to work with the date and time.
Instead of creating a new formula for each spreadsheet, I insert a Lambda function I wrote turning the text into the date & the time.
Perfect use!!!!!!
Happy new year
Thank you : ) Happy New Year to you too!!!
Can you do amortisation table with variable rates?
Hopefully the pivotby and groupby comes to my workplace's excel soon.
It's early 2024 and the enterprise team only just allowed the image function update to roll in. :(
Happy New Year 🎉
Thank you and happy new year!!
But many folks don't have eta-LAMBDA, PIVOT, or GROUPBY functions yet. I am signed up for new releases, but have Excel 64, not 32. I hope they release these soon!
Welcome back, sir. How is your mom,I hope she is doing well ❤.
Happy new year Habibi.
My mom passed away. She is in a better place. I posted a comment in the Community area of my excelisfun RUclips home page. Happy new year to you!!!
@@excelisfun
I am so sorry to hear about the loss of your mother. I know that she meant a lot to you, and I can only imagine how much you are hurting right now.
I want to let you know that I am thinking of you and your family during this difficult time. I know that I am just a stranger on the internet, but your videos have had a profound impact on my life. You have taught me so much about data analysis, and you have helped me to achieve my goals. I am truly grateful for your work.
I know that nothing I can say will take away your pain, but I want you to know that you are not alone.
@@unionafrican6094 Thank You : )
14:28, Mike I just verified something, and if you would put this formula into name manager, it would only work for the range U13:U23, Was that the goal?
I allowed myself to retype this formula into:
=LAMBDA(Date,"Q"&LOOKUP(MONTH(Date),FiscalQLookupTable))(U13:U23)
and now any range can work with this lambda as long as it is a date
Yes, I guess having the range U13:U23 in the Defined name was a mistake. Date is a variable as you have it is much better.
Hi, so basically we should ignore the next episode of the 365 playlist talking about lambda because it’s outdated compared to this one right?
Yes : )
Hi can suggest shortcut to copy paste merged cell from one sheet to another
I do not. Sorry, but I do not use Merged Cells.
I saved .xltx in C:/Dingo. But custom functions don't show up in the new workbook as I opened with the Ctrl+N shortcut. Any help please?
In windows 11 you use a different method. You build the workbook normally and create the name manager with the added functions. Then you use FILE-EXPORT and choose the export as a template. Then you open a blank workbook and press Ctrl-N. The new workbook has your new functions.
Dear Mike,
The video is without subtitles. 🤗
You can turn them on below video : )
Hi, I am looking for help with my excel report i have separate monthly visitors sheet and i want to collect them in one annual sheet with diagram can you help me with that?
Here are three methods:
ruclips.net/video/dHAMSTr1xqc/видео.html (Power Query)
ruclips.net/video/dha1T7mrFbY/видео.html (FILTER function)
ruclips.net/video/RL8dXcbiTUc/видео.html (Older versions of Excel)
sir, kindly give us an Excel formula I have an Excel 365 version, so kindly base my Excel 365 below mention -
We own some assets that have been acquired in different financial years. There is a specific condition to be applied while calculating the depreciation of these assets. If an asset was procured before September, a depreciation rate of 15% will be applied. However, if it was acquired after September, a rate of 7.5% will be applied. For the initial purchase of an asset, we will apply the specified conditions accordingly. After the first year, a consistent depreciation rate of 15% will be applied for every annual procurement.
I think you can use the IF function to apply different rates based on dates and the MONTH function. Or a lookup function with a lookup table based on dates like XLOOKUP. If you post an example of the initial price of asset, and the first few depreciation amounts that are expected, I can figure out a formula.
@@excelisfun When I purchase a machine we take depreciation before Sep 15% after Sep 7.5% suppose I purchase in 2010 the first year applies the above conditions but for the remaining year we apply a 15% depreciation like this we purchase every machine with every machine. for example 1Lac Machine purchase after sep 7.5% 7500k 92500
second year we apply on 92500 @15% dep third year 78625 @15% after one year condition 15%fixed depreciation. it is WDV method for depreciation.
Good evening friends...i have 365 in italian and i've tried the following G9: =PERRIGA(B9:E13;SOMMA) , and returns #NOME?. ...G9: =PERRIGA(B9:E13;LAMBDA(r,somma(r))) works perfectly!....could you help me?
I don't know Italian, but a suggestion is to change your separator from semicolon to comma (I had this problem). Cell G9: seems inconsistent with both semicolons and comma.