Amazing Thoughts from Excel Teammate ExcelLambda: My humble opinion on the matter for anybody interested in lambdas in general: There are some stages of the learning process we have to cover to become better lambda "programmers" in the context of the new lambda helper functions: - 1st stage, Test them extensively, imagine the simplest tasks posible playing with all arguments. - 2nd stage, Use them to solve more complicated tasks, tasks we were familiar with, or we solved before with old techniques using the real estate of the spreadsheet, drag and drops, helper columns, expandable ranges, or any range formulas techniques. -3rd stage, When we became familiar with them, using them everywhere and for everything. - 4th stage, Avoid overuse of them and Algorithm Awareness. They are all iterative functions. Iterations tend to take time for large arrays. Like exact match vs approximate match. We have to use them Only if there is no other way to solve the same task "in bulk" dynamic arrays. Always be aware of the Big O (time/space complexity of an algorithm) . Check any refine final solution based on simulated larger data samples. If there is no "in bulk" dynamic solution and we have to use iterations, always use the solution with smallest nr. of iterations. Examples: 1. Removing all digits in an array of strings. (task from the previos video) 1st solution - for each cell, textsplit for digits and concat the result, using MAP. Total iterations = rows*clms initial array. 2nd solution - using substitute each digit for entire array, with REDUCE. Total iterations no matter of the array size = 10 iterations. Obvious the best solution. 2. When 2 solutions are possible, iterating by rows or by columns, choose the shorter dimension. 3. Nested lambda helper functions , exponentially slow. Only when no other way. 4. A long classic many variable formulae can be super fast compared with a super elegant short lambda helper alternative. - 5th stage, Vision vs lack of Vision. Modular design versatility. Any lambda should be designed as a probable future subrutine used by other function, therefore a golden rule for lambda "programmers": always avoid any construction using any functions that have range or reference as arguments, like ...xxIFS , OFFSET, ROW etc.. - 6th stage. Sky is the limit. Build our own custom-made lambda helper functions environment. I promise you that if we get here, with only a couple of lambdas we can solve an overly complex spectrum of tasks. This is my personal opinion based on my experience so far and I wanted to share it. Many of us are, or will be, or have been, to stage 3. It is a very important stage in the process, that's why I want to salute any solution that uses lambda helper functions, no matter if they will be ever use in real life or not, if are the best one or not, short or long, eficient or slow. What is important for all of us , is that these solutions can hold clever design tricks or concepts techniques that can be useful in other designes where they will be the Only alternative. My 2 cents.
I thought I was the only person who thought this way. Many interesting Formulations. For some reason lambda is only available in office 365 right now, not in my app, Even though we're on the semi-annual corporate subscription. I gather our corporate IT is behind...
I just added homework problems to Start file on Dec 31 at 2:37 PM. I just posted the pdf notes 8:19 AM Pacific time, Dec 30. They were delayed by 8 hours. Note #1: Recursion IS possible in the worksheet without Defined Name, and this comes from ExcelLambda (example is on Recursion sheet in download workbook): =LET(text,B25,rem,$I$4,f,LAMBDA(y,t,x, IF(x="", TRIM(t), y(y,SUBSTITUTE(t,LEFT(x),""), RIGHT(x,LEN(x)-1)))),f(f,text,rem)) Note #2: The Finished Workbook has all the solutions and some formulas and notes from comment area.
Most probably, they never teach advanced level Excel on any university in the world. From my point of view, you would definitely be a Senior Distinguished Professor on such faculty. Another masterclass, simply magnificent ! Congratulations for your amazing work, and extremely high effort and passion you always invest for preparation, shooting and editing !
You are welcome for all the class materials, Romu!!!! You are right, but it is worse: there is almost no (statistically speaking) colleges and universities in the world that even teach basics or intermediate Excel in a way that teaches efficient and smart model building. There are a few of us, like me and Bart Titulaer and Syed Muzammil Mahasan Shahi and a few others. But almost all academic Excel books and teachings abjectly fail in their duty to prepare workers for the working world.
@@excelisfun I taught an Excel class at a VoTech school. You are right. The courses usually teach only how to use the software, not how to solve problems.
@@excelisfun you are an amazing teacher you have changed tons of people's lives with this videos, I'm sure that thanks to you there's tons of people that save time, that kept their job, that got promoted, tons of people that were fortunate to find your videos and get their life changed due to you.. I include myself 🙂 thank u so much for all of this courses I love them
@@excelisfun and for sure you have accomplished it... Thank u so much 💖 for taking.the time to help us and teach us... Been a teacher is not easy it takes time and love for others. Thanks so much for all of this trainings
Wow, Mike, that’s another epic video in an epic series! Thanks for referring to me: I’ll take some 1.5% of the credit, ExceLambda should take a much more substantial chunk of the credit for all of his hard work, and the rest is for you for compiling all this information into yet another one of these epic videos. Thank you so much for all the hard work and the beautiful result! For spreading the Good Word on Excel, you are THE GOAT - bar NONE! We all owe you an immense debt of gratitude. We salute you.
Thanks for your kind words, Geert!!!!! No doubt: Excel Lambda (C) provides us all with such inventive, clever and uniquely efficient formulas!!! We are lucky for you and C and the whole Team!!!! Go Team!!!!
Mike, Here are some remarks: • The following files are missing from the downloads: the PDF-notes and that special MECS-tracker file you mentioned. • @44:10 in the "MAP2" sheet you talk about the Total Commission Calculation and you mention my solution, but the solution you demonstrated still needs a XLOOKUP function, whereas my "Simply SUMIFS"-solution does not (it does require a subsequent array multiplication and a SUM BYROW). You may want to revisit my solution and have another look at it. :-)
Here's my "Simply SUMIFS" solution: =BYROW(SUMIFS(D12:D67,B12:B67,F12#,C12:C67,C5:F5)*C6:F9,LAMBDA(r,SUM(r))) (no tables nor defined names) But there's another little problem in your file: do you see it? If not: compare the (order of the) names in F12# to those in B6:B9... ;-)
@@GeertDelmulle Thanks, Editor : ) : ) : ) I added pdf notes to links : ) You are right about the MAP2. I used one of the earlier formulas. I should have used your even more like DAX formula without the XLOOKUP : ( : (
@@excelisfun Don't worry about it: there are so many things to take into account making these epic videos, so it's easy to lose track of the "latest everything". Your "even more like DAX formula" statement reminds me of my other formula for EMT1804: the MAP²-solution. Here's the orig formula with all the names in it: =MAP(SalesReps,LAMBDA(name,SUMPRODUCT(FILTER(Rate[CommisionRate],Rate[SalesRep]=name),MAP(UNIQUE(Rate[Product]),LAMBDA(p,SUMIFS(Sales[Sales],Sales[SalesRep],name,Sales[Product],p)))))) This reminds me the most of DAX because I wanted to simulate how the RELATED-function works (and still no xlookup ;-).
I had to pause the video to slowly enjoy the beauty of ShowFormula LAMBDA solution ! It is like an extremely delicious cake you eat really slowly, to enjoy each and every second :--))). For sure, this ShowFormula feature should become one of many best practices you generously offer to all your subscribers and visitors of this channel. You are a living legend ! Microsoft should create this title for you: Living legend Excel MVP ! It sounds really good !
Thanks for your kind words! I am not aware of any other Excel MVP that provides as much for free: so maybe Microsoft could acknowledge with a title like: MVP free Guy lol I am so happy that you like the show formula formula. I have added it to my default workbook so it is in all workbooks. 30:22 shows how to add lambdas to default workbook. Are you going to do this?
Holy moly Mike! This is an excellent video! I now completely understand lambda. I have been dodging for a while, because I didn't under stand the function argument being place in the lambda function twice. But it all makes sense! Thanks again Mike!
You are welcome! This is why I make such huge and comprehensive videos: for people like you who want to know the hows and whys and who are not afraid to get down and study to become a master!
Mike another tip that I just tried: if you receive a file from someone else, and you want to use your own LAMBDA functions, you just move an empty sheet from your own file to the file you received, all the functions in the name manager are copied ! Make sure your names are defined in the workbook and not in the worksheet. That is really awsome, other way around works the same, I have your formula's now in my own empty sheet. Just copied one sheet of your file to my own file and all the formulas are there! awsome!!
That is a great point. I have this in the pdf notes becasue sometimes this feature is a nightmare when you copy a sheet and did not want all the names to come along. In your scenario, you are using it as an atvantage. : )
Dear Mike, Happy new 2023, all the best to you and to your family. I need to share something really personal with you. I was browsing some good, old Excel books, one of them is Ctrl+Shift+Enter: Mastering Excel Array Formulas, the amazing book you published back in 2013. On page VII you thanked to many people who answered your posts at the MrExcel Message Board. This late night I discovered that one of the names ... is mine, RomulusMilea. You cannot imagine how positively shocked I was, I still cannot believe my eyes. What an honor to see my name in one of your books ! Thank you, Sir ! This definitely made my first day of 2023 ! I cannot wait to see again the question(s) I answered :-))).
Yes!!!!!! I am so happy to be on the Team with you for sooooo long : ) Please, do share the links to some of the posts that you helped me with so many years ago : ) Happy 2023 to you too, Mr Excel Message Board Pal RomulusMilea : )
I truly love these videos, just like I love your book The Only App That Matters. I considered myself upper level before I walked into it, however what I liked most was VBA. But I learned so much new tricks and "you can also use it for"'s from the book that I'm now back to that place where formulas feels like "the real way" to do 'as much as possible and then twice as much on top of it'. :)
I love to hear that: becasue it is TRUE = Do much of what VBA used to do can be done with Power Query, Dynamic Arrays, LAMBDA and so many other new M 365 Excel features!! It is a good new Excel world to be in!!!!
@@excelisfun What I love most about LET and LAMBDA (in comb with dynamic arrays) is the possibillity to now do SO EXTREMELY MUCH with SO EXTREMELY LITTLE. I make Excel solutions for my organization, that very often involves tools for big table reports (which just not suit 'manual'). But very often I end up also sitting using those tools, because people still(!) feel uncomfortable with using macros (even if the only thing they do are opening those more comfortable looking userform tools)... and the IT-admin still feels uncomfortable with allowing any add-ins, which could hide them in "any other ribbon button". So I'm sitting working for all departments, often without actually knowing what I'm doing for them. But LET-functions and dynamic arrays can now do very much of the things I used to write macros for doing... and with LAMBDA it doesn't matter if those formulas are 20 characters or 1000 characters. They will still look tiny in the end-user's eyes, be just as easy (or easier) to use as the functions they are already used to, also do the fat one-klick-reports those macros would do... and finally they can feel comfortable doing it themselves. It's TRULY a wonderful XL-world to be in!!! Last, my response on "What do you use Advanced Formula Enviroment for": One thing I see it as a good tool for is interpreting. (Easy to see to me, being a Swede). It makes it very easy to use and learn what you are reading about in books etc, since you can use it in English. Just enter what you are reading about in the AFE and in the sheet you will then see how YOU should use these new functions/formulas you are reading about but can't find in your Excel version. That interpreting used to be a task for the VBA Immediate Window before AFE :)
Another Epic Video added to the ample collection of Epic Videos. ✌😉 PS: محمد حلمي had an interesting question on the comments, how to write a formula that can do recursive without a defined name. (Recursion spreadsheet) Here it is: =LET(text,B25:B28,rem,$I$4,f,LAMBDA(y,t,x, IF(x="", TRIM(t), y(y,SUBSTITUTE(t,LEFT(x),""), RIGHT(x,LEN(x)-1)))),f(f,text,rem))
Awe inspiring video, Mike thank you! I can scarcely believe the End of Month summary then the cross tab tabulate came next. I was kind of glad the video ended :)
This is absolutely AMAZING from an Excel Wizard! this video covers all the parts of Excel I actually needed. You are always knowing what the world wanted and that is another reason I call you a WIZARD. I love you Sir, MR. MIKE ❤️❤️🫶🫶
Yes, it is a lot. I tried to put all of what LAMBDA can do in one video (even though all means what I know and is in no way reflective of the true infinity of LAMBDA). Glad you like it!!!!
Mike...I can only say: WOAH! Thank you for ending the year this way with this video! - A-MA-ZING 😁👌- One hour and 45 minutes of pure learning magic on this new frontier!
This is a shocking about what Excel can do and what has become!!! and now we can loop too !!!, I could not understand what Microsoft is doing, but the message that I understood is that Excel will not end and will not die. you are the smarter excel teacher and the most complete, hate off Mike. :):)
Mike, The name "lambda helper functions" are a huge misnomer. This video does a huge service in highlighting their potential as lambda "embedders" (versus the normal process of creating and registering functions in Name Manager.) The biggest takeaway from this video is the use of lambda helper functions in creating array versions of functions that are otherwise limited to scalar results. I played around with the lambda function when it first came out, but much of the focus was on recursion, which I found challenging to troubleshoot. Custom functions in Name Manager turned out to often be more work than simply creating a working version and "copying it down", even when I knew there would be multiple instances in the workbook. In contrast, the process of building a working formula in a cell and wrapping it in a helper function really expands the functionality of Excel. This video installment has done a lot toward having me understand the Excel lambda function. From my perspective this is probably one of the most helpful videos of yours I've ever watched. Thank you!!
You are welcome, Gregory! Yes, I think that the biggest help of the "LAMBDA Helper Functions" (Microsoft's Corporate Name, not mine AND I have almost universally rejected Microsoft's nouns they use, but this time I did not... : ( I like your "Embedders better ), but I think that the biggest help is that they can help spill many formulas that you could not spill before they were invented : ) I am so glad they this video has helped a great deal!! As a Team we will keep learning all the power and fun of Excel!
Yes, LAMBDA is one of a number of things that Microsoft has given us in the last few years that revolutionizes who we work with Excel and dramatically increases our love for the amazing tool: Microsoft 365 Excel!!!! I am glad you you will learn some good things, Rean!!!!
Yes, single cell formulas have come a long way since you (formula Guy), me and the rest of our Team started making them back in Excel Beta November 2018...
Dear Mike, Templates, to always open with Excel and also using the CTRL+N command, can be copied in the C:\Program Files\Microsoft Office\Root\Office16\XLSTART\ folder, with administrator permissions. 🤗 Happy New Year. 🍾🤗
I got my template to work, by unchecking the box Show the start screen when this application starts from Options, then from Advanced I entered the path to the folder where I saved the template, now everytime I open Excel I start with a blank workbook with all the lambdas in the Name Manager and if I hit CTRL + N, the new workbook also has them
What a beautiful ending of 2022 with such great video about LAMDA made by the big boss Mike Girvin. I wish for u and for all teammate a happy new year 2023.. I am following you for ten years so far for me when we talk about excel direct it come to mind Mike Girvin :-) Honestly I am very very very happy to know as a great teacher as u mike.
Thank you! This video is the ultimate reference guide for Lambda functions and recursion. I will recommend it to all my students in my statistics courses. FYI, Advanced Formula Environment has been updated, and now it allows the user to edit the active cell formula in the new 'grid' view
You are welcome!!! I am so glad to help you and your students. What!?!?!? Updates? It must be in just the last week or so... I have not seen that. I will go and try it now : )
@@excelisfun I will do my best just with written text: (1) Activate the cell in which you want to create/edit a formula (i.e.: K10 in Sheet1); (2) In the 'Grid' tab of the Advanced Formula Editor, just below the tab, you will see a check mark, followed by the sheet name (Sheet1) and the cell reference (K10); (3) Below the cell reference, you will see the edition section which allows you to enter / edit formulas: if the cell is currently empty, you'll see a blank line with line number 1; if there is already a formula, you will see it there, properly highlighted and line numbered. (4) Enter/edit the formula; and (5) Click on the check mark to save changes on cell.
@@JorgeObando Thank you, Jorge! You did great with your written description. I followed it perfectly. Maybe you should be a teacher : ) I see that this allows you to type a formula, but I still do not see how you can directly reference the sheet, or how to test a LAMBDA. Where you saying that we can edit formula? I have seen and done that. Or are you saying that we can directly reference the worksheet with references by clicking in worksheet? I have not see this and I can not get this to work.
@@excelisfun (1) Reference a sheet & cell --> only writting the reference, but no 'mouse - reference' allowed, once you click on another cell, Advanced Formula Editor aborts the operation on previous cell; (2) Test a Lambda --> It seems you can: I entered this lambda: I entered this 'junk' lambda function =LAMBDA(a,b, a + b)(10, 2) in the Advanced Formula Editor and, once I clicked the check mark, the cell result was 12. Later I saved the lambda function as the new Junk function and type in the Advanced Formula Editor Junk(3, 5), and it worked. Don't know exactly if that is the functionality you want to try.
Actually the first time I notice this difference when using SEQUENCE(3) But notice if we use cells as a reference with these values 1 2 3 It will give us the same result of MAP =BYROW(R91#,LAMBDA(a,CONCAT(SEQUENCE(a)))) R91# = SEQUENCE(3)
20:41 The advance formula enviroment is great when dealing with ranges i.e A1:A10, however it had big issue when dealing with Table nomenclature when used inside lambda function. I need to test if that changed or not but it would be super great if there was something like personal.xlsb for all the labdas you created so each 1 can be accessed any time in any workbook
Epic video Mike, thanks, I am going to watch this again. I think this LAMBDA and LET is difficult to learn for at least one reason: These new formulas are relevant if they are used dynamic and/or repettitive. In the simple examples it is much easier to do this the old school. If you really need those LET and LAMBDA functions the examples will be more complex and even harder to understand. But with this video you have a good start to experiment with those new formula's. Another issue: if people create new own functions which are helpful, I can imagine that people are going to collect and user these own "UDF, User Defined Functions". This could be a formula jungle in a few years... so I would be a good idea that Microsoft will have a cenral place where you can install centrally placed functions or that they add them to the already huge list of functions...
Great video!!! Not your grandma's excel!! I find AFE a preferred way to create LAMBDAs. My primary method is to either use an existing formula and wrap it in a LAMBDA within AFE or less often to develop a new calculation, test it, then paste in AFE to convert itvtova LAMDA. I never deveop the calculation in AFE anyway.
In the classics :) the "mechanics" of recursion are well revealed when trying to write Euclid's algorithm to find the greatest common divisor =GCD(). e.g. define name Euclid like this: =LAMBDA(a, b, IF(b=0, a, Euclid(b, MOD(a,b)))) or optimized by one iteration =LAMBDA(a, b, IF(MOD(a,b)=0, b, Euclid(b, MOD(a,b)))) where LET could be used for readability =LAMBDA(a, b, LET(r, MOD(a,b), IF(r=0, b, Euclid(b,r))))
Glad you liked it, Victor!!!! I am just a good story teller. ExcelLambda is the one that is way out front on the tip of this frontier showing us clever and amazing formulas. Also, teammates like you and Geert and Excel Wizard and @user-wp5og7gw3p and many others are also so clever and helpful in your original solutions. It is am amazing Team!!!!
It's an amazing video. Thank you for the time and effort you put into making tons of epic videos. I wish you and your family a full year of joy and good health. Happy New Year Mike 🙂🎆🎉🎉
Phew !!! It took me three days to watch the entire video to understand at least 20% of the contents. I need to practice to get hold of it. Simply amazing. I wish and pray to God to give you a healthy, peaceful and long life :) There's one question that's troubling me for a long time (not related to the video though): If I mark the status as "Completed" - I need the date to populate. Example: If I put the status of a task as "Completed" - the date completed column should show the date when the status had changed to "completed". I tried with IF withTODAY(), NOW() and wrapped with TEXT - however, when my system date changes, automatically the date in this formula also changes. How can we put the Completion date and make it static (meaning, it shouldn't change when the system date changes) without going for VBA
@@excelisfun It is an honour to have you as my subscriber. My Super Thanks is just an infinitesimal token of appreciation and admiration for your invaluable and selfless contribution to the world.
The new module feature for the advanced formula editor will come in handy when making functions of a particular type like text, value modification, date & type, etc
Mike ! this is a great video. so many comments that I can go over all of them, but one simple condition. We all know that conditional formatting can slow the spreadsheet so to display the dates at 1:29:58 why you did not choose to wrap the variable eomu into a text function like this eomu;TEXT(SORT(UNIQUE(EOMONTH(--d;0)));"mmm, yyyy") Looking forward for your reply
The conditional formatting was for boarders and total row. The date formatting is just cell formatting. But if you don't mind the dates aligned left as text, that is a great way to avoid the cell number formatting : )
@@excelisfun I honestly pray to GOD that.... If I get a chance to visit US... My top priority would be to meet you personally and also treat you for noble service you have done....
This is extremely exciting stuff. I have a question. Is there a way to take an existing "calculator" workbook and encapsulate that existing calculator functionality into a LAMBDA function? By "calculator" I mean I have a sheet (or series of sheets) with X number of input cells, and a set of output cells (ie, a report table). The calculator is complicated but in the end it's just a black box function that accepts X arguments and gives an output report. Now I understand that using LAMBDA, one could rewrite this entire calculator as a LAMBDA function (and also using LET) that produces the output report. But I would like to avoid having to completely rewrite the calculator that I have already written. I would like to find a way to write a LAMBDA that "enters" the input variables into the existing calculator, and produces the output report, using the EXISTING version of all the logic. My expectation is there isn't a way to do this. But boy would I like to find a way to do it if there is.
Anonymous recursion occurs when using double iterations or when using helper functions and recursion at the same time. When extracting a specific item with a random number, even a random number can be duplicated if there is a lot of data, so it is mainly used to regenerate a random number recursively in case of duplication. When creating 10 words or 1000 rows in one row, duplicate words may appear even if extracted with random numbers, so it is a case of looping until there are no duplicates through recursion. This problem cannot be solved with a helper function, but only through recursion . Below is an anonymous recursive expression similar to REDUCE. =LAMBDA(text, LET( Loop, LAMBDA(ME,arr,n, LET( str, SUBSTITUTE(arr, n, ), IF(n = 9, str, ME(ME, str, n + 1)) ) ), TRIM(Loop(Loop, text, 0)) ) )(B25:B28)
ok! first try of SCAN for real situation in my work, it did great job for running total. but for my situation i did it for tow array columns when the first one represent invoices and the second one the payments so without creation of extra column i did array subtraction and running total ... it's funny 🤣🤣🤣
RE: Recursion at 55:25, another great way to remove numbers to the right is *=LEFT(G5,MIN(IFERROR(SEARCH(SEQUENCE(10,,0),G5),255))-1)* . This searches through the cell and returns an array listing of the position of all numbers (Any errors return 255.), and then performs LEFT() to the position left of the first number. It's a short and fairly simply formula. I know the purpose was to show recursion in action, but this is a viable option for that specific situation. I use it to return voucher types (accounting).
Not so good, your formula extracts only the text before first digit, does not remove all the digits. Applied to "ab23cd56" returns only "ab" instead of "abcd" to "12ab" returns "" (empty string) instead of "ab" A recursion alternative that works could be: =LET(m,MID(G5,SEQUENCE(LEN(G5)),1),CONCAT(IF(ISNUMBER(--m),"",m)))
@@MaydayAggro My bad. Did not realize that "remove numbers" in English means "remove number and text" 😆 What your formula does is keeping text to the left of first digit. ✌
Der Mike, In the «MAP2» worksheet, the BYROW and MAP functions work equally, if the array is a spilled range: =--BYROW(F25#,LAMBDA(a,CONCAT(SEQUENCE(a)))) =--MAP(F25#,LAMBDA(a,CONCAT(SEQUENCE(a)))) or = --BYROW( SEQUENCE( 3 ),LAMBDA(a, CONCAT( SEQUENCE( SUM( a ) ) ) ) ) 🤗
Yes, that is true. I meant to show that. When we spill an array into worksheet and refer to it with Cell#, it is considered a range, this is why Microsoft named the #, a Spilled Range Operator. It is sort of how INDEX can take an array and convert it to a range so that SUMIFS can read it. But I was careful with my words in the video and said that MAP and BYROW have a discrepancy with arrays. It is so funny (or maybe un-funny), I try so hard to get all details into videos like this, but even though many, many ideas on a topic are in my head, and I plan to have them all in the video, some inevitably fall out before I get them into story... lol I will go add your note to the downloads workbook : ) Go Team!!!!
@@excelisfun The fact that MAP and BYROW act the same for ranges (1D) is absolutely obvious and common sense. Anything works with ranges. When it comes to arrays, the difference is Profound. We always have to take the nose out of examples that live only the real estate of a spreadsheet . If a function does a preliminary calculation of a pattern that I need to expand, makes no sense to stop the action to print a range to be able to use BYROW and continue after that, or use MAP for expanding vectors on the first place. Vision vs lack of Vision 😆😆You know what I mean !✌😉
@@JoseAntonioMorato Did not want to post this when I first saw your lambdas, but Sorry, none of your formulas works properly. - the above one for letters, does nothing to "AxxBxxCxx" (the 2 in substitute not helping the way you think) - the one for digits returns for "abc24 def46" this : "abcdef" instead of "abc def" . The eventual existing initial space structure should remain intact in both cases. Minimum versatility a lambda should have. So far, TRIM is doing ok.✌😀
@@JoseAntonioMorato No problem! Somehow your formula I was referring to on my previous post, the one with ...SUBSTITUTE(a,b," "b,2)...that was supposed to replace TRIM, disappeared. You deleted it yourself, or YT has done something weird ? If you do not have it to post it back, I can do it if you want. You can post it back. Let me know.✌😉
Hi. Thank you for the very informative video. Let's say i have a 2 column table with Dates in column A and numbers in column B. The task is to generate a running total in column C for each change in month (assume data is only for 1 year). Assuming dates in range A2:A5 are for the month of January 2023 and A6:A9 are for the month of February 2023, this formula in cell C2 will only spill till cell C5 (not till cell C9). Why is this so? Please help. Here is the formula i am entering in cell C2 =SCAN(0,FILTER($B$2:$B$14,MONTH($A$2:$A$14)=MONTH(A2)),LAMBDA(I,a,I+a))
single cell formula at C2: =LET(d,A2:A14,n,B2:B14,s,SEQUENCE(ROWS(d)), SCAN(0,s,LAMBDA(v,i,(MONTH(INDEX(d,i))=MONTH(INDEX(d,MAX(i-1,1))))*v+INDEX(n,i))))
Hi. In range A2:E5 are some text entries. When i write this formula (=TOCOL(A2:E5)) in cell H4, i get all entries in single column (H4:H23). In cell K4, when i write this formula, I get the count of each entry in separate cells =COUNTIF(H4#,UNIQUE(H4#)) All good so far. However, when i write this formula in cell K4, then i get an error measage =COUNTIF(TOCOL(A2:E5),UNIQUE(TOCOL(A2:E5))) Why am i getting this error and how can i get the answer without the formula in cell H4? Thank you for your help.
All COUNTIFS, SUMIFS and the like, can not handle array calculations in the range arguments. This has been true for decades. It is the unfortunate way Microsoft programmed these functions. I have video blogged and written in my books extensively about this topic.
Hello Mike thank u so much for all of this videos they have helped a lot..I wanted to ask you a question about power query merge..I merged 2 tables and one row was duplicated.. I did it tons of times the same error .. what am I Doing wrong :'( please help
@@excelisfun sure.. this can happen if you have one single duplicate on Any of the data you are bringing over.. I checked and I had one on the multiple data i loaded so.. once I removed the duplicated row from the original data... The issue was fixed :D
Can you explain what the "F9" key is doing when you make the array constant? at 11:24? What's the difference between that and doing array constant with ctrl+shift+enter?
I guess you have not seen many of my videos because I use it in almost every video. F9 is the evaluation key. It can evaluate a formula element, a range of cells or a complete formula. Since the formula was a rectangular range when you hit F9 it evaluates and shows you values in array syntax: { } house array, , means columns and ; means row. Ctrl + Shift + Enter has nothing to do with F9. Ctrl Shift Enter is the old way to enter array formulas before Microsoft 365 Excel Formula Calculations Engine was invented.
@@excelisfun Awesome. Thanks for the detailed explanation and video. I had googled array constant, and every example explained it as doing ctrl+shift+enter
@@GodParticleZero Yes, that is because you can't believe much about Excel when you search the internet or Google or RUclips. What you found is so wrong, and it has never been correct in any version. Yes, it is one thing that you can do with an array constant, but there are 100s of other things too... Also, most academic books and trainers do more hard than good when teaching Excel. There are few good academic resources that actually teach proper efficient Excel techniques. Which is sad. But I got your back : )
I have One Drive at work and the template solution absolutely works. As Mike shows in the video if you start Excel with the start screen it will not, but I unchecked that from Excel Options (so no Start Screen for me thank you very much), then in ADVANCED I entered the path to the template on the option AT startup always open this folder and bam!, my blank workbook contains all the lambdas in the template and so does every workbook that I open using the shortcut CTRL + N. This is a life saver because AFE is blocked at work
Hello Mike, first of all thanks ! Now that Excel is turing complete, how does it compete with modern programming language paradigm such as Python's pandas, or R's dplyr ?
question. at 50:02 what if the excel have 2 number columns and 2 balance value (maybe multiple subsidiaries data set) and if I want to use scan for an area. Is it possible to use byrow() and scan() together?
While I don't like the use of so many literal cell refs when defining the data arrays (versus dynamic definitions), this was otherwise a very useful review.
Hi re the bycol v map I found that if you use the sequence within BYCOL directly you do get 1 1 1, but if you use it outside and then reference it so; A = SEQUENCE(,3) BYCOL(A#,LAMBDA(s,CONCAT(SEQUENCE(,s)) )) , you'll get 1 , 12, 123 as with the ; MAP( SEQUENCE(,3),LAMBDA(s, CONCAT(SEQUENCE(,s)) ))
Hi. Let's say i have 123 in cell D2, 346 in cell D3 and 781 in cell D4. In a single cell formula (say cell F2), i would like to write a formula which will spill 1,2,3,3,4,6,7,8,1 in range F2:F10. Coud you kindly help me? Thank you.
Any clue why MS didn't design BYROW() and BYCOL() (and some of the other helper formulas) to operate directly on other functions, rather than requiring LAMBDA()? For example, why not just =BYROW(sum(B2#))?
Is absolutely possible but only with defined lambdas and not with the bult-in ones, like this(similar syntax) : define any lambda of 1 variable only , let's say TEST(x)=LAMBDA(x,SUM(x)/3), then you can call: =BYROW(B2#,TEST)
Mike, I wonder if you can add to this video the basics of lambdas in VBA, I got the template solution to work but I use a macro to create copies of workbooks that I need to share and I do not want the lambda functions to go with them, I have tried some basic code to delete the lambdas in the new workbooks but although other names are deleted the code always fail when it is the lambdas turn, do you know how to refer to lambdas in VBA? I would think that it would be the same as with defined ranges but apparently not😢😢
Dear Mike, I only realized that your «ShowFormulas» lambda is incorrect, when I noticed that cell P24, of the «CrossTab» worksheet, was blank. The IFERROR function in LAMBDA is redundant, as the TOCOL function already has parameter 2, for error elimination. Redundant: =LAMBDA(reference,IFERROR(TOCOL(ADDRESS(ROW(reference), COLUMN(reference), 4) & ": " & FORMULATEXT(reference), 2), "")) No redundancy: =LAMBDA(reference,TOCOL(ADDRESS(ROW(reference),COLUMN(reference),4)&": "&FORMULATEXT(reference),2)) 🤗
@@JoseAntonioMorato Good, IFERROR(.....,"") , has to be outside TOCOL because acts like "not found" argument, meanwhile TOCOL, with whatever arguments you use, when no formulas are to be found, returns #CALC error. So, instead of "" in IFERROR we can replace it with "not found" or "no formulas". => IFERROR adds versatility ✌😉
@@JoseAntonioMorato Was Mike's decision on how he designed the function to behave. On any circumstances, whatever TOCOL does or does not, IFERROR versatility has to stay. Is like taking out from FILTER no found argument.
My brain is just good for story telling : ) Your brain is awesome just the way it is!!! And I got your back anytime you need to learn something about Excel or Power BI.
Let's say i have some text entries in range A2:A9 (with repetitions in this column). In range B2:B9 are some numbers. In cell D2, I enter the formula =unique(A2:A9). In cell E2, I enter the formula =WRAPROWS(FILTER($B$2:$B$9,$A$2:$A$9=D2),MAX(COUNTIF($A$2:$A$9,D2))) This formula spills the results in multiple columns showing all numeric entries for the entry in cell D2. I want this formula to spill over to the cells below as well. When i copy the formula down, it works fine but as i mentioned, it is not spilling over automatically. I tried this formula but it returns an error =BYROW(D2#,LAMBDA(r,WRAPROWS(FILTER($B$2:$B$9,$A$2:$A$9=r),MAX(COUNTIF($A$2:$A$9,r))))) Could you help me with explaining why my second formula is not working and how i can resolve this issue. Thank you.
single cell formula for entire array (including unique vector) =LET(t,A2:A9,n,B2:B9,u,UNIQUE(t), r,REDUCE(0,u,LAMBDA(v,i,VSTACK(v,TOROW(FILTER(n,t=i))))), HSTACK(u,IFNA(DROP(r,1),"")))
Thank you very much for the knowledge that you provide , 1:00:00 RemoveChar , Please How make this Recursion in Formula without Define Name 1:33:00 this is try to solve =LET( z,B8:G22, x,B7:G7, f,INDEX(z,,XMATCH(J2,x)), i,TOROW(SORT(UNIQUE(f))), r,INDEX(z,,XMATCH(J1,x)), s,SORT(UNIQUE(r)), n,SUMIFS(INDEX(z,,XMATCH(J3,x)),f,i,r,s), IFNA( VSTACK( HSTACK("Total " &J3), HSTACK(J1&"/"&J2,i,"Total"), HSTACK(s,n,BYROW(n,LAMBDA(y,SUM(y)))), HSTACK("Total",BYCOL(n,LAMBDA(y,SUM(y))),SUM(n))),""))
@@Excelambda But your first formula: =TRIM(REDUCE(B25:B28,SEQUENCE(10,,0),LAMBDA(i,a,SUBSTITUTE(i,a,"")))) does not directly us recursion, and is much more beautiful : )
Amazing Thoughts from Excel Teammate ExcelLambda:
My humble opinion on the matter for anybody interested in lambdas in general:
There are some stages of the learning process we have to cover to become better lambda "programmers" in the context of the new lambda helper functions:
- 1st stage, Test them extensively, imagine the simplest tasks posible playing with all arguments.
- 2nd stage, Use them to solve more complicated tasks, tasks we were familiar with, or we solved before with old techniques using the real estate of the spreadsheet, drag and drops, helper columns, expandable ranges, or any range formulas techniques.
-3rd stage, When we became familiar with them, using them everywhere and for everything.
- 4th stage, Avoid overuse of them and Algorithm Awareness. They are all iterative functions. Iterations tend to take time for large arrays. Like exact match vs approximate match. We have to use them Only if there is no other way to solve the same task "in bulk" dynamic arrays. Always be aware of the Big O (time/space complexity of an algorithm) . Check any refine final solution based on simulated larger data samples.
If there is no "in bulk" dynamic solution and we have to use iterations, always use the solution with smallest nr. of iterations.
Examples:
1. Removing all digits in an array of strings. (task from the previos video)
1st solution - for each cell, textsplit for digits and concat the result, using MAP. Total iterations = rows*clms initial array.
2nd solution - using substitute each digit for entire array, with REDUCE. Total iterations no matter of the array size = 10 iterations. Obvious the best solution.
2. When 2 solutions are possible, iterating by rows or by columns, choose the shorter dimension.
3. Nested lambda helper functions , exponentially slow. Only when no other way.
4. A long classic many variable formulae can be super fast compared with a super elegant short lambda helper alternative.
- 5th stage, Vision vs lack of Vision. Modular design versatility. Any lambda should be designed as a probable future subrutine used by other function, therefore a golden rule for lambda "programmers": always avoid any construction using any functions that have range or reference as arguments, like ...xxIFS , OFFSET, ROW etc..
- 6th stage. Sky is the limit. Build our own custom-made lambda helper functions environment. I promise you that if we get here, with only a couple of lambdas we can solve an overly complex spectrum of tasks.
This is my personal opinion based on my experience so far and I wanted to share it.
Many of us are, or will be, or have been, to stage 3. It is a very important stage in the process, that's why I want to salute any solution that uses lambda helper functions, no matter if they will be ever use in real life or not, if are the best one or not, short or long, eficient or slow. What is important for all of us , is that these solutions can hold clever design tricks or concepts techniques that can be useful in other designes where they will be the Only alternative. My 2 cents.
I thought I was the only person who thought this way. Many interesting Formulations. For some reason lambda is only available in office 365 right now, not in my app, Even though we're on the semi-annual corporate subscription. I gather our corporate IT is behind...
I just added homework problems to Start file on Dec 31 at 2:37 PM.
I just posted the pdf notes 8:19 AM Pacific time, Dec 30. They were delayed by 8 hours.
Note #1: Recursion IS possible in the worksheet without Defined Name, and this comes from ExcelLambda (example is on Recursion sheet in download workbook):
=LET(text,B25,rem,$I$4,f,LAMBDA(y,t,x, IF(x="", TRIM(t), y(y,SUBSTITUTE(t,LEFT(x),""), RIGHT(x,LEN(x)-1)))),f(f,text,rem))
Note #2: The Finished Workbook has all the solutions and some formulas and notes from comment area.
Most probably, they never teach advanced level Excel on any university in the world. From my point of view, you would definitely be a Senior Distinguished Professor on such faculty. Another masterclass, simply magnificent ! Congratulations for your amazing work, and extremely high effort and passion you always invest for preparation, shooting and editing !
You are welcome for all the class materials, Romu!!!! You are right, but it is worse: there is almost no (statistically speaking) colleges and universities in the world that even teach basics or intermediate Excel in a way that teaches efficient and smart model building. There are a few of us, like me and Bart Titulaer and Syed Muzammil Mahasan Shahi and a few others. But almost all academic Excel books and teachings abjectly fail in their duty to prepare workers for the working world.
@@excelisfun I taught an Excel class at a VoTech school. You are right. The courses usually teach only how to use the software, not how to solve problems.
@@excelisfun you are an amazing teacher you have changed tons of people's lives with this videos, I'm sure that thanks to you there's tons of people that save time, that kept their job, that got promoted, tons of people that were fortunate to find your videos and get their life changed due to you.. I include myself 🙂 thank u so much for all of this courses I love them
@@ingridquan6036 You are welcome so much! This has been my goal for 14 years at RUclips : ) : )
@@excelisfun and for sure you have accomplished it... Thank u so much 💖 for taking.the time to help us and teach us... Been a teacher is not easy it takes time and love for others. Thanks so much for all of this trainings
Wow, Mike, that’s another epic video in an epic series!
Thanks for referring to me: I’ll take some 1.5% of the credit, ExceLambda should take a much more substantial chunk of the credit for all of his hard work, and the rest is for you for compiling all this information into yet another one of these epic videos.
Thank you so much for all the hard work and the beautiful result!
For spreading the Good Word on Excel, you are THE GOAT - bar NONE!
We all owe you an immense debt of gratitude. We salute you.
Thanks for your kind words, Geert!!!!! No doubt: Excel Lambda (C) provides us all with such inventive, clever and uniquely efficient formulas!!! We are lucky for you and C and the whole Team!!!! Go Team!!!!
Mike,
Here are some remarks:
• The following files are missing from the downloads: the PDF-notes and that special MECS-tracker file you mentioned.
• @44:10 in the "MAP2" sheet you talk about the Total Commission Calculation and you mention my solution, but the solution you demonstrated still needs a XLOOKUP function, whereas my "Simply SUMIFS"-solution does not (it does require a subsequent array multiplication and a SUM BYROW). You may want to revisit my solution and have another look at it. :-)
Here's my "Simply SUMIFS" solution: =BYROW(SUMIFS(D12:D67,B12:B67,F12#,C12:C67,C5:F5)*C6:F9,LAMBDA(r,SUM(r)))
(no tables nor defined names)
But there's another little problem in your file: do you see it?
If not: compare the (order of the) names in F12# to those in B6:B9... ;-)
@@GeertDelmulle Thanks, Editor : ) : ) : ) I added pdf notes to links : ) You are right about the MAP2. I used one of the earlier formulas. I should have used your even more like DAX formula without the XLOOKUP : ( : (
@@excelisfun Don't worry about it: there are so many things to take into account making these epic videos, so it's easy to lose track of the "latest everything". Your "even more like DAX formula" statement reminds me of my other formula for EMT1804: the MAP²-solution. Here's the orig formula with all the names in it:
=MAP(SalesReps,LAMBDA(name,SUMPRODUCT(FILTER(Rate[CommisionRate],Rate[SalesRep]=name),MAP(UNIQUE(Rate[Product]),LAMBDA(p,SUMIFS(Sales[Sales],Sales[SalesRep],name,Sales[Product],p))))))
This reminds me the most of DAX because I wanted to simulate how the RELATED-function works (and still no xlookup ;-).
I had to pause the video to slowly enjoy the beauty of ShowFormula LAMBDA solution ! It is like an extremely delicious cake you eat really slowly, to enjoy each and every second :--))). For sure, this ShowFormula feature should become one of many best practices you generously offer to all your subscribers and visitors of this channel. You are a living legend ! Microsoft should create this title for you: Living legend Excel MVP ! It sounds really good !
Thanks for your kind words! I am not aware of any other Excel MVP that provides as much for free: so maybe Microsoft could acknowledge with a title like: MVP free Guy lol
I am so happy that you like the show formula formula. I have added it to my default workbook so it is in all workbooks. 30:22 shows how to add lambdas to default workbook. Are you going to do this?
Wow. Wow. Wow. So much here and so well done. I may have to watch ten times just to get everything.
Microsoft added a bunch of new related functions, so I had to totally re-do this almost 2 hour video, here: ruclips.net/video/OxV-F0vXj8I/видео.html
Holy moly Mike! This is an excellent video! I now completely understand lambda. I have been dodging for a while, because I didn't under stand the function argument being place in the lambda function twice. But it all makes sense! Thanks again Mike!
Glad this helps!!! Nothing like a complete story on a topic to straighten things out : )
Very efficient and useful tutoril and I followed you in every step. I have learnt LAMBDA much more easliy thank to you 👍👍
You are welcome! This is why I make such huge and comprehensive videos: for people like you who want to know the hows and whys and who are not afraid to get down and study to become a master!
Mike another tip that I just tried: if you receive a file from someone else, and you want to use your own LAMBDA functions, you just move an empty sheet from your own file to the file you received, all the functions in the name manager are copied ! Make sure your names are defined in the workbook and not in the worksheet. That is really awsome, other way around works the same, I have your formula's now in my own empty sheet. Just copied one sheet of your file to my own file and all the formulas are there! awsome!!
That is a great point. I have this in the pdf notes becasue sometimes this feature is a nightmare when you copy a sheet and did not want all the names to come along. In your scenario, you are using it as an atvantage. : )
Perhaps the best Excel video I have ever watched. Thanks. #mind-blown
You are welcome for the fun with mind blowing!!!!
Dear Mike,
Happy new 2023, all the best to you and to your family. I need to share something really personal with you. I was browsing some good, old Excel books, one of them is Ctrl+Shift+Enter: Mastering Excel Array Formulas, the amazing book you published back in 2013. On page VII you thanked to many people who answered your posts at the MrExcel Message Board. This late night I discovered that one of the names ... is mine, RomulusMilea. You cannot imagine how positively shocked I was, I still cannot believe my eyes. What an honor to see my name in one of your books ! Thank you, Sir ! This definitely made my first day of 2023 ! I cannot wait to see again the question(s) I answered :-))).
Yes!!!!!! I am so happy to be on the Team with you for sooooo long : ) Please, do share the links to some of the posts that you helped me with so many years ago : ) Happy 2023 to you too, Mr Excel Message Board Pal RomulusMilea : )
BEST LAMBDA TUTORIAL EVER! 💯 With professional explanation and materials! It was a pleasure to follow! 😎 Thank you very much!
You are welcome very much!!!!
I truly love these videos, just like I love your book The Only App That Matters. I considered myself upper level before I walked into it, however what I liked most was VBA. But I learned so much new tricks and "you can also use it for"'s from the book that I'm now back to that place where formulas feels like "the real way" to do 'as much as possible and then twice as much on top of it'. :)
I love to hear that: becasue it is TRUE = Do much of what VBA used to do can be done with Power Query, Dynamic Arrays, LAMBDA and so many other new M 365 Excel features!! It is a good new Excel world to be in!!!!
@@excelisfun What I love most about LET and LAMBDA (in comb with dynamic arrays) is the possibillity to now do SO EXTREMELY MUCH with SO EXTREMELY LITTLE.
I make Excel solutions for my organization, that very often involves tools for big table reports (which just not suit 'manual'). But very often I end up also sitting using those tools, because people still(!) feel uncomfortable with using macros (even if the only thing they do are opening those more comfortable looking userform tools)... and the IT-admin still feels uncomfortable with allowing any add-ins, which could hide them in "any other ribbon button". So I'm sitting working for all departments, often without actually knowing what I'm doing for them. But LET-functions and dynamic arrays can now do very much of the things I used to write macros for doing... and with LAMBDA it doesn't matter if those formulas are 20 characters or 1000 characters. They will still look tiny in the end-user's eyes, be just as easy (or easier) to use as the functions they are already used to, also do the fat one-klick-reports those macros would do... and finally they can feel comfortable doing it themselves. It's TRULY a wonderful XL-world to be in!!!
Last, my response on "What do you use Advanced Formula Enviroment for":
One thing I see it as a good tool for is interpreting. (Easy to see to me, being a Swede). It makes it very easy to use and learn what you are reading about in books etc, since you can use it in English. Just enter what you are reading about in the AFE and in the sheet you will then see how YOU should use these new functions/formulas you are reading about but can't find in your Excel version. That interpreting used to be a task for the VBA Immediate Window before AFE :)
Thanks for this, Mike. This really gives me a better understanding of Lambda and some practical uses. And it is time to start following Excellambda!!
Yes, ExcelLambda can see with great vision out to the Outer Edges Of The Near Infinity of Microsoft 365 Excel!!!! Go Team!!!
Another Epic Video added to the ample collection of Epic Videos. ✌😉
PS: محمد حلمي had an interesting question on the comments, how to write a formula that can do recursive without a defined name. (Recursion spreadsheet) Here it is:
=LET(text,B25:B28,rem,$I$4,f,LAMBDA(y,t,x, IF(x="", TRIM(t), y(y,SUBSTITUTE(t,LEFT(x),""), RIGHT(x,LEN(x)-1)))),f(f,text,rem))
Exceλambda
y(y,SUBSTITUTE(t,LEFT(x),"")
This is the part that I really want to understand
We are so lucky to have ExcelLambda on the Team : ) : ) : ) : ) : ) : ) : ) ": ) : ) : ) : ) : ) : )
Wow! Bravo!
But then I suggest "embed" recursive lambda within regular noname lambda
=LAMBDA(text,rem, LET(f, LAMBDA(y,t,x, IF(x = "", TRIM(t), y(y, SUBSTITUTE(t, LEFT(x), ""), RIGHT(x, LEN(x) - 1)))), f(f, text, rem)))(B25:B28,$I$4)
We can even use the same name (y or f) because scopes don't conflict
btw, Advanced Formula Environment formatted it like this :)
=LAMBDA(text, rem,
LET(
y, LAMBDA(y, t, x,
IF(
x = "",
TRIM(t),
y(
y,
SUBSTITUTE(t, LEFT(x), ""),
RIGHT(x, LEN(x) - 1)
)
)
),
y(y, text, rem)
)
)(B25:B28, $I$4)
great to be here..We are excellers....
Yes!!!!! Love this: We are Excellers!!!!! : )
Awe inspiring video, Mike thank you! I can scarcely believe the End of Month summary then the cross tab tabulate came next. I was kind of glad the video ended :)
Glad you like it, John!!!
So amazing, Teacher. 😍😍
This episode gonna be the legend video for all Exceller.
Thanks, Great Teammate Excel Wizard!!!!!! : ) : )
This is absolutely AMAZING from an Excel Wizard! this video covers all the parts of Excel I actually needed. You are always knowing what the world wanted and that is another reason I call you a WIZARD. I love you Sir, MR. MIKE ❤️❤️🫶🫶
Thanks for the love, omotossso!!!!!!
Another great video mike, Thanks a lot.
Wowwww Mike ... I'm very astonished ...this is an EPIC video!
Yes, it is a lot. I tried to put all of what LAMBDA can do in one video (even though all means what I know and is in no way reflective of the true infinity of LAMBDA). Glad you like it!!!!
Mike...I can only say: WOAH! Thank you for ending the year this way with this video! - A-MA-ZING 😁👌- One hour and 45 minutes of pure learning magic on this new frontier!
It is a new frontier. I make movies that tell stories, but ExcelLambda, he is way in front us in the Frontier showing us the way : ) : ) : )
@@excelisfun thank you for making this. I hope your Mom is better 🙏! All the best myfriend.
This is a shocking about what Excel can do and what has become!!!
and now we can loop too !!!, I could not understand what Microsoft is doing, but the message that I understood is that Excel will not end and will not die.
you are the smarter excel teacher and the most complete, hate off Mike. :):)
I agree: it is amazing what Excel has become!!!! I am always happy to help, Digital Cooking!!!!!
Thank you Mike. That should help me with LAMBDA! 😄
You are welcome!
Great video! Thanx Mike! We are so lucky to have you!!
Dear Mike,
In the «REDUCE» worksheet, other formulas to remove numbers and/or characters from a text string:
Characters:
=LAMBDA(Text,REDUCE(Text,{0,1,2,3,4,5,6,7,8,9," "},LAMBDA(a,b,SUBSTITUTE(a,b,""))))(B6:B15) - without TRIM
= LAMBDA(Text, BYROW( Text, LAMBDA(r, TEXTJOIN( "", , TEXTSPLIT( r,{0,1,2,3,4,5,6,7,8,9," "}, , 1 ) ) ) ) )(B6:B15)
Numbers:
= LAMBDA(Text, BYROW( Text, LAMBDA(r, TEXTJOIN( "", , TEXTSPLIT( r, TEXTSPLIT( r, {0,1,2,3,4,5,6,7,8,9}, , 1 ), , 1 ) ) ) ))(B6:B15) 🤗
Mike,
The name "lambda helper functions" are a huge misnomer. This video does a huge service in highlighting their potential as lambda "embedders" (versus the normal process of creating and registering functions in Name Manager.) The biggest takeaway from this video is the use of lambda helper functions in creating array versions of functions that are otherwise limited to scalar results.
I played around with the lambda function when it first came out, but much of the focus was on recursion, which I found challenging to troubleshoot. Custom functions in Name Manager turned out to often be more work than simply creating a working version and "copying it down", even when I knew there would be multiple instances in the workbook. In contrast, the process of building a working formula in a cell and wrapping it in a helper function really expands the functionality of Excel.
This video installment has done a lot toward having me understand the Excel lambda function. From my perspective this is probably one of the most helpful videos of yours I've ever watched. Thank you!!
You are welcome, Gregory! Yes, I think that the biggest help of the "LAMBDA Helper Functions" (Microsoft's Corporate Name, not mine AND I have almost universally rejected Microsoft's nouns they use, but this time I did not... : ( I like your "Embedders better ), but I think that the biggest help is that they can help spill many formulas that you could not spill before they were invented : ) I am so glad they this video has helped a great deal!! As a Team we will keep learning all the power and fun of Excel!
There is no doubt I'm about to learn something revolutionary.
Thank you legend
Yes, LAMBDA is one of a number of things that Microsoft has given us in the last few years that revolutionizes who we work with Excel and dramatically increases our love for the amazing tool: Microsoft 365 Excel!!!! I am glad you you will learn some good things, Rean!!!!
Thanks Mike. Single Cell Formulas were the best EVER!!!!
Yes, single cell formulas have come a long way since you (formula Guy), me and the rest of our Team started making them back in Excel Beta November 2018...
Excellent tutorial, Thanks Mike 👍👍
You are welcome!!!!!
Dear Mike,
Templates, to always open with Excel and also using the CTRL+N command, can be copied in the C:\Program Files\Microsoft Office\Root\Office16\XLSTART\ folder, with administrator permissions. 🤗
Happy New Year. 🍾🤗
Aaahhhh. the old Administer permission : ) Thanks for that hot tip. I think I will add it to the pdf notes so the rest of the Team can benefit.
I just added it to the posted pdf notes : ) Thanks, Jose!!!! Go Team : )
I got my template to work, by unchecking the box Show the start screen when this application starts from Options, then from Advanced I entered the path to the folder where I saved the template, now everytime I open Excel I start with a blank workbook with all the lambdas in the Name Manager and if I hit CTRL + N, the new workbook also has them
@@lourdesdelcampo8722 Yes!!!!
This was amazing Mike! I definitely want to improve my LAMBDA skills in 2023!
I like you, and still learning and it is all amazing!!! Glad you liked it, Chris M!!!!
What a beautiful ending of 2022 with such great video about LAMDA made by the big boss Mike Girvin. I wish for u and for all teammate a happy new year 2023.. I am following you for ten years so far for me when we talk about excel direct it come to mind Mike Girvin :-) Honestly I am very very very happy to know as a great teacher as u mike.
I am happy to be hanging out on the Team with you for 10 years, Mohamed!!!!!
Thank you! This video is the ultimate reference guide for Lambda functions and recursion. I will recommend it to all my students in my statistics courses. FYI, Advanced Formula Environment has been updated, and now it allows the user to edit the active cell formula in the new 'grid' view
You are welcome!!! I am so glad to help you and your students. What!?!?!? Updates? It must be in just the last week or so... I have not seen that. I will go and try it now : )
I just tried it and could not get a cell reference to work in Advanced Formula Environment. How did you get it to work? What did you do?
@@excelisfun I will do my best just with written text: (1) Activate the cell in which you want to create/edit a formula (i.e.: K10 in Sheet1); (2) In the 'Grid' tab of the Advanced Formula Editor, just below the tab, you will see a check mark, followed by the sheet name (Sheet1) and the cell reference (K10); (3) Below the cell reference, you will see the edition section which allows you to enter / edit formulas: if the cell is currently empty, you'll see a blank line with line number 1; if there is already a formula, you will see it there, properly highlighted and line numbered. (4) Enter/edit the formula; and (5) Click on the check mark to save changes on cell.
@@JorgeObando Thank you, Jorge! You did great with your written description. I followed it perfectly. Maybe you should be a teacher : )
I see that this allows you to type a formula, but I still do not see how you can directly reference the sheet, or how to test a LAMBDA. Where you saying that we can edit formula? I have seen and done that. Or are you saying that we can directly reference the worksheet with references by clicking in worksheet? I have not see this and I can not get this to work.
@@excelisfun (1) Reference a sheet & cell --> only writting the reference, but no 'mouse - reference' allowed, once you click on another cell, Advanced Formula Editor aborts the operation on previous cell; (2) Test a Lambda --> It seems you can: I entered this lambda: I entered this 'junk' lambda function =LAMBDA(a,b, a + b)(10, 2) in the Advanced Formula Editor and, once I clicked the check mark, the cell result was 12. Later I saved the lambda function as the new Junk function and type in the Advanced Formula Editor Junk(3, 5), and it worked. Don't know exactly if that is the functionality you want to try.
It was amazing. For me the best part was comparing MAP and BYROW.
Thanks.
Actually the first time I notice this difference when using SEQUENCE(3)
But notice if we use cells as a reference with these values 1 2 3
It will give us the same result of MAP
=BYROW(R91#,LAMBDA(a,CONCAT(SEQUENCE(a))))
R91# = SEQUENCE(3)
Thanks a million for posting such an amazing video about Lambda.
You are welcome a million : ) : ) : ) ... : ) (millionth smile) lol
20:41
The advance formula enviroment is great when dealing with ranges i.e A1:A10, however it had big issue when dealing with Table nomenclature when used inside lambda function.
I need to test if that changed or not but it would be super great if there was something like personal.xlsb for all the labdas you created so each 1 can be accessed any time in any workbook
I thought I showed how in this video: you have to save in start up folder.
Page 12 in pdf notes has something about this. It was in video too, but I did not minute mark it : ( : ( : ( : (
This video is amazing, thank you very much for posting. I liked it so much so just bought your book and i am looking forward to reading it.
thanks for the great detailed explanation, soo much to digest 😅
The video is always here for you to boomerang back to : )
Epic and massively awesome! Lots to study.. lots to learn. Thanks Mike!!
You are welcome for the tons to study, Wayne!!!! : ) : )
Thank you sooo much, Mike aka the Excel MVP!!! :)
You are welcome so much!!!
Epic video Mike, thanks, I am going to watch this again. I think this LAMBDA and LET is difficult to learn for at least one reason: These new formulas are relevant if they are used dynamic and/or repettitive. In the simple examples it is much easier to do this the old school. If you really need those LET and LAMBDA functions the examples will be more complex and even harder to understand. But with this video you have a good start to experiment with those new formula's.
Another issue: if people create new own functions which are helpful, I can imagine that people are going to collect and user these own "UDF, User Defined Functions". This could be a formula jungle in a few years... so I would be a good idea that Microsoft will have a cenral place where you can install centrally placed functions or that they add them to the already huge list of functions...
Great points, Bart!!!
Great video!!! Not your grandma's excel!! I find AFE a preferred way to create LAMBDAs. My primary method is to either use an existing formula and wrap it in a LAMBDA within AFE or less often to develop a new calculation, test it, then paste in AFE to convert itvtova LAMDA. I never deveop the calculation in AFE anyway.
That makes sense, Richard. Develop in worksheet then paste into AFE. One way or another, gotta start in worksheet.
Thank you so much Mike for this EXCELlent video.
You are welcome so much, Fellow Teacher!!!!!
Thanks Mike for this video. Great topic, delivered at just the right pace for me so thank you once again. Happy New Year!
The pace is good for me too, with repeat on important topics : ) So glad you like it!!!
Thanks Mike, another epic video i really enjoyed !. Amazing function Lambda..
Yes!!!!!! Glad it is epic for you : )
What a tremendous job done, Mike. Incredible!
You are the Alpha and Omega, α and ω of Excel :)
In the classics :) the "mechanics" of recursion are well revealed when trying to write Euclid's algorithm to find the greatest common divisor =GCD().
e.g. define name Euclid like this:
=LAMBDA(a, b, IF(b=0, a, Euclid(b, MOD(a,b))))
or optimized by one iteration
=LAMBDA(a, b, IF(MOD(a,b)=0, b, Euclid(b, MOD(a,b))))
where LET could be used for readability
=LAMBDA(a, b, LET(r, MOD(a,b), IF(r=0, b, Euclid(b,r))))
Glad you liked it, Victor!!!! I am just a good story teller. ExcelLambda is the one that is way out front on the tip of this frontier showing us clever and amazing formulas. Also, teammates like you and Geert and Excel Wizard and @user-wp5og7gw3p and many others are also so clever and helpful in your original solutions. It is am amazing Team!!!!
@@viktorasgolubevas2386 Thanks for the Euclid. I added it to a sheet in the download file named "EuclidByVictor"
Amazing tutorial!
I am glad that you like it!!!!!
It's an amazing video. Thank you for the time and effort you put into making tons of epic videos. I wish you and your family a full year of joy and good health. Happy New Year Mike 🙂🎆🎉🎉
Thanks Ghost Excel Teammate!!!! Happy New year to you and your family!!!!
Phew !!! It took me three days to watch the entire video to understand at least 20% of the contents. I need to practice to get hold of it. Simply amazing. I wish and pray to God to give you a healthy, peaceful and long life :)
There's one question that's troubling me for a long time (not related to the video though): If I mark the status as "Completed" - I need the date to populate. Example: If I put the status of a task as "Completed" - the date completed column should show the date when the status had changed to "completed".
I tried with IF withTODAY(), NOW() and wrapped with TEXT - however, when my system date changes, automatically the date in this formula also changes. How can we put the Completion date and make it static (meaning, it shouldn't change when the system date changes) without going for VBA
I am not sure how to do that without VBA. Since I am not so gooc with VBA, try posting your question to this great site: mrexcel.com/board
@@excelisfun sure and thanks a lot for replying
My Excel guru 🙏!
Glad you fund this video and that it helps : ) : )
Thankyou, thankyou, thankyou for this video.
You are welcome, welcome, welcome!!!!!!
Strong!
Thank you, Mike
Thanks!
Thank you very much for your kind donation, effciency365!!!! Love your user name : ) : ) : ) : )
You have a great RUclips channel, Dr Nitin!!! I just subbed : )
@@excelisfun It is an honour to have you as my subscriber.
My Super Thanks is just an infinitesimal token of appreciation and admiration for your invaluable and selfless contribution to the world.
@@efficiency365 I am happy to be on the Team with you!!!!!!
This an Awesome video sir. Thank you very. Hope had very good New year & Christmas
Glad you like it and happy new year to you too : ) : ) : ) : )
Amazing video 📹 Mike. Genius!
Happy new year Mike to you and your family 👪 🥳🎉
Happy New Year to you, Nader : ) : ) : ) And your family!!!!!!!!!
Mike - First - your videos are outstanding ! Please check your links for this video - the "PDF" link actually links to an excel file
That has been fixed about 3.5 hours ago. Hit F5 to refresh your cache. I hope that works : )
The new module feature for the advanced formula editor will come in handy when making functions of a particular type like text, value modification, date & type, etc
That is a keen insight. Thanks, Patrick!!!!
Happy New Year Mike 🎊
Thanks for the Gift
Happy New Year to you, Anan!!!!!
Thanks Mike.....still Watching. :) :)
It Is Epic, Formula Guy : ) : ) : ) Enjoy!!!!!
Thanks Mike to start the year with such a great video. So much to learn, was a lot at once ;)
I like to put it all in one place. Then we all know where to go. But there are at least 100 short videos tips in this lol
Thats a great video
Glad you like it!!!
Mike ! this is a great video. so many comments that I can go over all of them, but one simple condition. We all know that conditional formatting can slow the spreadsheet so to display the dates at 1:29:58 why you did not choose to wrap the variable eomu into a text function like this eomu;TEXT(SORT(UNIQUE(EOMONTH(--d;0)));"mmm, yyyy")
Looking forward for your reply
The conditional formatting was for boarders and total row. The date formatting is just cell formatting. But if you don't mind the dates aligned left as text, that is a great way to avoid the cell number formatting : )
Thank you Mike.. ❤️❤️❤️
You are welcome, mano!!!!!
Woow , around 2 hours!
First comment.
It is epic, to say the least. Lots of good LAMBDA stuff : ) You get the first place trophy, Softwaretrain!!!!
happy new year to you mike!!!
Thanks : ) Happy New Year to you too!!!!!!
Outstanding!
Glad you like it, Paul!!!!
Non Recursive to remove Number
=MAP(B25:B28,LAMBDA(a,TRIM(CONCAT(TEXTSPLIT(a,SEQUENCE(10,,0))))))
Cool formula! I added it to the download : ) : ) Go Team!!!!
Great video ❤👌
Glad you like it!!!
Hii mike... Advance Happy new year...
I am yet to use the lambda function practically
It is worth the effort to learn. It opens up so much : ) : ) Happy New Year to you long time Teammate, RRR!!!!!!
@@excelisfun I honestly pray to GOD that.... If I get a chance to visit US... My top priority would be to meet you personally and also treat you for noble service you have done....
@@rrrprogram8667 It would be amazing to meet you someday : ) : ) : ) : )
This is extremely exciting stuff. I have a question. Is there a way to take an existing "calculator" workbook and encapsulate that existing calculator functionality into a LAMBDA function? By "calculator" I mean I have a sheet (or series of sheets) with X number of input cells, and a set of output cells (ie, a report table). The calculator is complicated but in the end it's just a black box function that accepts X arguments and gives an output report. Now I understand that using LAMBDA, one could rewrite this entire calculator as a LAMBDA function (and also using LET) that produces the output report. But I would like to avoid having to completely rewrite the calculator that I have already written. I would like to find a way to write a LAMBDA that "enters" the input variables into the existing calculator, and produces the output report, using the EXISTING version of all the logic. My expectation is there isn't a way to do this. But boy would I like to find a way to do it if there is.
Anonymous recursion occurs when using double iterations or when using helper functions and recursion at the same time.
When extracting a specific item with a random number, even a random number can be duplicated if there is a lot of data, so it is mainly used to regenerate a random number recursively in case of duplication.
When creating 10 words or 1000 rows in one row, duplicate words may appear even if extracted with random numbers, so it is a case of looping until there are no duplicates through recursion. This problem cannot be solved with a helper function, but only through recursion .
Below is an anonymous recursive expression similar to REDUCE.
=LAMBDA(text,
LET(
Loop, LAMBDA(ME,arr,n,
LET(
str, SUBSTITUTE(arr, n, ),
IF(n = 9, str, ME(ME, str, n + 1))
)
),
TRIM(Loop(Loop, text, 0))
)
)(B25:B28)
Thanks for your informative comment!!! I added your formula to the download workbook on the Recursion worksheet so the rest of the Team can see too!
Amazing video!!!! thank you 🙏🏼 Where can we find the 'excel lambda' content creator that you mention?
Awesome!
Can you take a look at the link for the pdf? I get the xlsx when I click on it. The link for the xlsx gives the xlsx :D
OMG... : ( : ( I forgot to post it, and it is like the best part. But, I just fixed the link!!!! Thanks for alerting me, sledge!!!!
ok! first try of SCAN for real situation in my work, it did great job for running total.
but for my situation i did it for tow array columns when the first one represent invoices and the second one the payments so without creation of extra column i did array subtraction and running total ... it's funny 🤣🤣🤣
Yes!!!!!!!! SCAN is Fun!!!!!
41:20 how do I work with the helper functions so that the formula can be in name manager like "only" lambda functions?
Helper fucntions ONLY work with LAMBDA. This is why they are called helper functions.
RE: Recursion at 55:25, another great way to remove numbers to the right is *=LEFT(G5,MIN(IFERROR(SEARCH(SEQUENCE(10,,0),G5),255))-1)* . This searches through the cell and returns an array listing of the position of all numbers (Any errors return 255.), and then performs LEFT() to the position left of the first number. It's a short and fairly simply formula. I know the purpose was to show recursion in action, but this is a viable option for that specific situation. I use it to return voucher types (accounting).
Not so good, your formula extracts only the text before first digit, does not remove all the digits.
Applied to "ab23cd56" returns only "ab" instead of "abcd"
to "12ab" returns "" (empty string) instead of "ab"
A recursion alternative that works could be:
=LET(m,MID(G5,SEQUENCE(LEN(G5)),1),CONCAT(IF(ISNUMBER(--m),"",m)))
@@Excelambda as i indicated in the comment, it was meant to remove numbers to the right, not to remove all numbers wherever they may be.
That is a clever formula to remove numbers to the right, Mayday!!!
@@Excelambda That is a beauty that works for all the situations!!!! BAM!
@@MaydayAggro My bad.
Did not realize that "remove numbers" in English means "remove number and text" 😆
What your formula does is keeping text to the left of first digit. ✌
Der Mike,
In the «MAP2» worksheet, the BYROW and MAP functions work equally, if the array is a spilled range:
=--BYROW(F25#,LAMBDA(a,CONCAT(SEQUENCE(a))))
=--MAP(F25#,LAMBDA(a,CONCAT(SEQUENCE(a))))
or
= --BYROW( SEQUENCE( 3 ),LAMBDA(a, CONCAT( SEQUENCE( SUM( a ) ) ) ) ) 🤗
Yes, that is true. I meant to show that. When we spill an array into worksheet and refer to it with Cell#, it is considered a range, this is why Microsoft named the #, a Spilled Range Operator. It is sort of how INDEX can take an array and convert it to a range so that SUMIFS can read it. But I was careful with my words in the video and said that MAP and BYROW have a discrepancy with arrays. It is so funny (or maybe un-funny), I try so hard to get all details into videos like this, but even though many, many ideas on a topic are in my head, and I plan to have them all in the video, some inevitably fall out before I get them into story... lol I will go add your note to the downloads workbook : ) Go Team!!!!
I just added a note to the MAP2 worksheet : )
@@excelisfun The fact that MAP and BYROW act the same for ranges (1D) is absolutely obvious and common sense. Anything works with ranges. When it comes to arrays, the difference is Profound. We always have to take the nose out of examples that live only the real estate of a spreadsheet . If a function does a preliminary calculation of a pattern that I need to expand, makes no sense to stop the action to print a range to be able to use BYROW and continue after that, or use MAP for expanding vectors on the first place. Vision vs lack of Vision 😆😆You know what I mean !✌😉
@@JoseAntonioMorato Did not want to post this when I first saw your lambdas, but Sorry, none of your formulas works properly.
- the above one for letters, does nothing to "AxxBxxCxx" (the 2 in substitute not helping the way you think)
- the one for digits returns for "abc24 def46" this : "abcdef" instead of "abc def" . The eventual existing initial space structure should remain intact in both cases. Minimum versatility a lambda should have.
So far, TRIM is doing ok.✌😀
@@JoseAntonioMorato No problem! Somehow your formula I was referring to on my previous post, the one with ...SUBSTITUTE(a,b," "b,2)...that was supposed to replace TRIM, disappeared. You deleted it yourself, or YT has done something weird ?
If you do not have it to post it back, I can do it if you want. You can post it back. Let me know.✌😉
Thank you 💗
You are welcome : ) : )
Hi. Thank you for the very informative video. Let's say i have a 2 column table with Dates in column A and numbers in column B. The task is to generate a running total in column C for each change in month (assume data is only for 1 year). Assuming dates in range A2:A5 are for the month of January 2023 and A6:A9 are for the month of February 2023, this formula in cell C2 will only spill till cell C5 (not till cell C9). Why is this so? Please help. Here is the formula i am entering in cell C2
=SCAN(0,FILTER($B$2:$B$14,MONTH($A$2:$A$14)=MONTH(A2)),LAMBDA(I,a,I+a))
single cell formula at C2:
=LET(d,A2:A14,n,B2:B14,s,SEQUENCE(ROWS(d)),
SCAN(0,s,LAMBDA(v,i,(MONTH(INDEX(d,i))=MONTH(INDEX(d,MAX(i-1,1))))*v+INDEX(n,i))))
@@Excelambda Thank you for your help. I am shortly posting another question here.
@@Excelambda Thanks for helping @excelenthusiast!!!! Great Formula : )
Hi. In range A2:E5 are some text entries. When i write this formula (=TOCOL(A2:E5)) in cell H4, i get all entries in single column (H4:H23). In cell K4, when i write this formula, I get the count of each entry in separate cells
=COUNTIF(H4#,UNIQUE(H4#))
All good so far. However, when i write this formula in cell K4, then i get an error measage
=COUNTIF(TOCOL(A2:E5),UNIQUE(TOCOL(A2:E5)))
Why am i getting this error and how can i get the answer without the formula in cell H4?
Thank you for your help.
Any update on this please?
All COUNTIFS, SUMIFS and the like, can not handle array calculations in the range arguments. This has been true for decades. It is the unfortunate way Microsoft programmed these functions. I have video blogged and written in my books extensively about this topic.
@@excelisfun Thank you.
@@excelenthusiasts You are welcome. This has tripped every single Exceller at some point in their life lol or : (
Hello Mike thank u so much for all of this videos they have helped a lot..I wanted to ask you a question about power query merge..I merged 2 tables and one row was duplicated.. I did it tons of times the same error .. what am I Doing wrong :'( please help
I am not sure. You can try posting to this great Excel question site: mrexcel.com/board
@@excelisfun thank you so much Mike for answering I posted the question and figure out the reason.. thank u so much
@@ingridquan6036 Post back if you want and let me know what cool solution you got : )
@@excelisfun sure.. this can happen if you have one single duplicate on Any of the data you are bringing over.. I checked and I had one on the multiple data i loaded so.. once I removed the duplicated row from the original data... The issue was fixed :D
@@ingridquan6036 : ) : ) : ) : )
Can you explain what the "F9" key is doing when you make the array constant? at 11:24? What's the difference between that and doing array constant with ctrl+shift+enter?
I guess you have not seen many of my videos because I use it in almost every video. F9 is the evaluation key. It can evaluate a formula element, a range of cells or a complete formula. Since the formula was a rectangular range when you hit F9 it evaluates and shows you values in array syntax: { } house array, , means columns and ; means row. Ctrl + Shift + Enter has nothing to do with F9. Ctrl Shift Enter is the old way to enter array formulas before Microsoft 365 Excel Formula Calculations Engine was invented.
@@excelisfun Awesome. Thanks for the detailed explanation and video. I had googled array constant, and every example explained it as doing ctrl+shift+enter
@@GodParticleZero Yes, that is because you can't believe much about Excel when you search the internet or Google or RUclips. What you found is so wrong, and it has never been correct in any version. Yes, it is one thing that you can do with an array constant, but there are 100s of other things too... Also, most academic books and trainers do more hard than good when teaching Excel. There are few good academic resources that actually teach proper efficient Excel techniques. Which is sad. But I got your back : )
Saving the template doesn't open new workbooks with the defined names. Could One Drive have something to do with that?
I have no idea.... But as long as One Drive has been around it has been trouble... If you discover what the issue is, please post back.
I have One Drive at work and the template solution absolutely works. As Mike shows in the video if you start Excel with the start screen it will not, but I unchecked that from Excel Options (so no Start Screen for me thank you very much), then in ADVANCED I entered the path to the template on the option AT startup always open this folder and bam!, my blank workbook contains all the lambdas in the template and so does every workbook that I open using the shortcut CTRL + N. This is a life saver because AFE is blocked at work
Hello Mike, first of all thanks ! Now that Excel is turing complete, how does it compete with modern programming language paradigm such as Python's pandas, or R's dplyr ?
I do not know, really, becasue I do not program in those languages... : (
Anyone else have an idea? (Go Team!!!!)
question. at 50:02 what if the excel have 2 number columns and 2 balance value (maybe multiple subsidiaries data set) and if I want to use scan for an area. Is it possible to use byrow() and scan() together?
You use MAP, which can iterate over multiple ranges.
@@excelisfun thank you. :D
While I don't like the use of so many literal cell refs when defining the data arrays (versus dynamic definitions), this was otherwise a very useful review.
Hi re the bycol v map I found that if you use the sequence within BYCOL directly you do get
1 1 1, but if you use it outside and then reference it so;
A = SEQUENCE(,3)
BYCOL(A#,LAMBDA(s,CONCAT(SEQUENCE(,s)) )) , you'll get 1 , 12, 123
as with the ; MAP( SEQUENCE(,3),LAMBDA(s, CONCAT(SEQUENCE(,s)) ))
Hi. Let's say i have 123 in cell D2, 346 in cell D3 and 781 in cell D4. In a single cell formula (say cell F2), i would like to write a formula which will spill 1,2,3,3,4,6,7,8,1 in range F2:F10. Coud you kindly help me? Thank you.
Maybe:
=MID(D2&D3&D4,SEQUENCE(SUM(LEN(D2:D4))),1)
or
=MID(CONCAT(D2:D4),SEQUENCE(SUM(LEN(D2:D4))),1)
or
=ROW(INDIRECT("1:"&SUM(LEN(D2:D4))))
@@excelisfun Thank you for your help. The last one does not give the correct result but the first 2 do.
Any clue why MS didn't design BYROW() and BYCOL() (and some of the other helper formulas) to operate directly on other functions, rather than requiring LAMBDA()? For example, why not just =BYROW(sum(B2#))?
Is absolutely possible but only with defined lambdas and not with the bult-in ones, like this(similar syntax) :
define any lambda of 1 variable only , let's say TEST(x)=LAMBDA(x,SUM(x)/3), then you can call:
=BYROW(B2#,TEST)
Mike, I wonder if you can add to this video the basics of lambdas in VBA, I got the template solution to work but I use a macro to create copies of workbooks that I need to share and I do not want the lambda functions to go with them, I have tried some basic code to delete the lambdas in the new workbooks but although other names are deleted the code always fail when it is the lambdas turn, do you know how to refer to lambdas in VBA? I would think that it would be the same as with defined ranges but apparently not😢😢
Sorry. No VBA for me. I am not so good with it, so I do not teach it : (
@@excelisfun Thanks, I eventually found a way, imperfect as it may be, but it works and now the macro deletes the lambdas from the end user files
pooooooooop
I miss these sounds🤣🤣🤣
Glad you like the fun, Rean!!!!!
👏🙏💚
: ) : )
Dear Mike,
I only realized that your «ShowFormulas» lambda is incorrect, when I noticed that cell P24, of the «CrossTab» worksheet, was blank.
The IFERROR function in LAMBDA is redundant, as the TOCOL function already has parameter 2, for error elimination.
Redundant:
=LAMBDA(reference,IFERROR(TOCOL(ADDRESS(ROW(reference), COLUMN(reference), 4) & ": " & FORMULATEXT(reference), 2), ""))
No redundancy:
=LAMBDA(reference,TOCOL(ADDRESS(ROW(reference),COLUMN(reference),4)&": "&FORMULATEXT(reference),2)) 🤗
If I will tell you that you are wrong on this one, again, will you delete the message, again? 😀
@@Excelambda My comment about Mike's LAMBDA, IT'S NOT WRONG AND IF YOU SAY IT IS, YOU'RE WRONG. ✌🤗
@@JoseAntonioMorato Good, IFERROR(.....,"") , has to be outside TOCOL because acts like "not found" argument, meanwhile TOCOL, with whatever arguments you use, when no formulas are to be found, returns #CALC error. So, instead of "" in IFERROR we can replace it with "not found" or "no formulas".
=> IFERROR adds versatility ✌😉
@@Excelambda The versatility is to leave the cell blank 👎(see cell P24 of the CrossTab worksheet).
@@JoseAntonioMorato Was Mike's decision on how he designed the function to behave. On any circumstances, whatever TOCOL does or does not, IFERROR versatility has to stay. Is like taking out from FILTER no found argument.
😆 👌👍
Glad you like it!!!
I wish I had your brain.
My brain is just good for story telling : ) Your brain is awesome just the way it is!!! And I got your back anytime you need to learn something about Excel or Power BI.
P r o m o s m
Nothing for Mathematicians here.
What does that mean?
Let's say i have some text entries in range A2:A9 (with repetitions in this column). In range B2:B9 are some numbers. In cell D2, I enter the formula =unique(A2:A9). In cell E2, I enter the formula
=WRAPROWS(FILTER($B$2:$B$9,$A$2:$A$9=D2),MAX(COUNTIF($A$2:$A$9,D2)))
This formula spills the results in multiple columns showing all numeric entries for the entry in cell D2. I want this formula to spill over to the cells below as well. When i copy the formula down, it works fine but as i mentioned, it is not spilling over automatically.
I tried this formula but it returns an error
=BYROW(D2#,LAMBDA(r,WRAPROWS(FILTER($B$2:$B$9,$A$2:$A$9=r),MAX(COUNTIF($A$2:$A$9,r)))))
Could you help me with explaining why my second formula is not working and how i can resolve this issue.
Thank you.
single cell formula for entire array (including unique vector)
=LET(t,A2:A9,n,B2:B9,u,UNIQUE(t),
r,REDUCE(0,u,LAMBDA(v,i,VSTACK(v,TOROW(FILTER(n,t=i))))),
HSTACK(u,IFNA(DROP(r,1),"")))
Thanks,@@Excelambda , for the formula solution!!!! : ) : ) : )
@@Excelambda Thank you for your help.
@@excelenthusiasts You're welcome!!✌
Thank you very much for the knowledge that you provide ,
1:00:00
RemoveChar , Please How make this Recursion in Formula without Define Name
1:33:00
this is try to solve
=LET(
z,B8:G22,
x,B7:G7,
f,INDEX(z,,XMATCH(J2,x)),
i,TOROW(SORT(UNIQUE(f))),
r,INDEX(z,,XMATCH(J1,x)),
s,SORT(UNIQUE(r)),
n,SUMIFS(INDEX(z,,XMATCH(J3,x)),f,i,r,s),
IFNA(
VSTACK(
HSTACK("Total " &J3),
HSTACK(J1&"/"&J2,i,"Total"),
HSTACK(s,n,BYROW(n,LAMBDA(y,SUM(y)))),
HSTACK("Total",BYCOL(n,LAMBDA(y,SUM(y))),SUM(n))),""))
recursive without define name, therefore a formula:
=LET(text,B25,rem,$I$4,f,LAMBDA(y,t,x, IF(x="", TRIM(t), y(y,SUBSTITUTE(t,LEFT(x),""), RIGHT(x,LEN(x)-1)))),f(f,text,rem))
@@Excelambda
Thank You for answer ,
y(y,
If possible, explain more about when to repeat and where to repeat in the formula
@@Excelambda But your first formula:
=TRIM(REDUCE(B25:B28,SEQUENCE(10,,0),LAMBDA(i,a,SUBSTITUTE(i,a,""))))
does not directly us recursion, and is much more beautiful : )
@@Excelambda I added this formula to Recursion sheet : ) Go Team!!!!!!
Very nice,
@user-wp5og7gw3p!!!! I have added your formula to the Cross Tab sheet : ) Go Team!!!!