Great explanation of this concept. Note that the name you give your parameters will appear in the function screen tips (the little pop-up that comes under your function when you are inside it). So if you name them "Text", "Before" and "After" instead of t, b and a, then it will be much easier for the user to figure out what to input in each parameter.
Wow, I have puzzled over this for years. Thank you for explaining this. The function is like repeating mirrors that reflect off of the most recent reflection, but the difference is that in Excel you can program the way out for that latest reflection/replacement. This was very helpful!
Great!! Wrote a recursive lambda that can handle the whole string column array RPLAB(ar,a,b,i)=LAMBDA(ar,a,b,i,LET(r,ROWS(a),IF(i=r+1,ar,RPLAB(SUBSTITUTE(ar,INDEX(a,i),INDEX(b,i)),a,b,i+1)))) --replace a with b for array ar and i=1 If you don't like extra variable i we write another one REPLACEAB(ar,a,b)=LAMBDA(ar,a,b,RPLAB(ar,a,b,1)) Anyhow "i" can be very handy in case you want to start the search from a different index in the replace list.
Used Python for most of my data work but at my new job was forced to use Excel. Your tutorials have been a blessing. Really appreciate the work you put on these!
Oh, Leila - this is an eye-opener! Thanks for sharing your knowledge. Actually I've got so many ideas to realize with LAMBDA, but recursive functions double the impact of this function. Can't wait to get the function at my insider-installation. Und ich hoffe auf 2021! Frohe Weihnachten und bleib gesund, Andreas.
Hey Leila, Big fan and have been watching you for years. I recently came across the magic of Map and Lambda in Google sheets / Excel and I would definitely would love to take it for a spin. I haven’t had the chance to use recursive lambda but one day, for sure.
The most powerful statement you made was right at the end. The next generation of excel users will be intuitively learning functional programing techniques. I suspect that this was perhaps the deeper reason behind Simon Peyton Jones decision to join Microsoft. Personally I am delighted as I know from experience that it takes time to become fluent with these techniques.
Absolutely agree. I was in the camp that VBA aged, and we should provide support for language such as Python/ R natively in Excel. But after seeing these recent paradigm shift in Excel programmability, I'll agree that I was short sighted. Any general-purpose programming language is a distraction for the population who uses Excel. I am a programmer, and still love Excel as it allows you to focus on the activity rather than how to do it. Lambda (and all the recent functions to support it) feel so tightly integrated. This is probably the best evolution in Excel, ever.
That’s interesting-SPJ has worked a lot on the same kinds of things-perhaps with more emphasis on formal methods intersecting with domain languages. He seemed to weigh in stuff like formal methods in secure operating systems, while still being hyper focused on thinking about language ecosystems like his comparisons about Java. Having just stopped running a company I am back in a job in finance, yet I still have a heavy addiction to programming. This is giving me exactly what I need to stay interested in what I’m doing. I’m seeing that there’s actually good structure here-and it is textbook ML style FP. However I still get bugged by the functions not being first class polymorphic functions where you can pass in labels with values dependent on higher order functions though. Example…I’m typing in a structured reference this morning like to a table called [APR] which is also a name of a table column like [@[APR]], and yet there are no operads for me to touch the concept of the string-it’s still objects. It’s still something that is not well defined in the sense of Grothendieck Universes, Agda, or even Haskell’s system F. Another example-I’m writing a Sumif with structured references and embedded conditional criteria-but it’s dax sometimes and it’s something else other times. I can’t know how the value will be represented in memory-will an embedded conditional be passed up to a Calling function, hit a type error I never see, or is it simply an undefined parameterization because the designers didn’t intend for users to make criteria into multi-function call chains? I’ve been in the lisp world a bit more than usual these past several months entertaining myself with nix and I can never imagine doing series fast paced corporate work with those-only building systems. And yet these don’t have the barriers that make it harder to express deep relationships about data and the many people oriented issues around that data. You’ve gotta write most stuff yourself, and outside of excel it’s not gonna be in some nice text editing space where the rules are standard. For example, no two emacs configs are alike. Just check out the many inconsistencies in packages between scala, OCaml, python, R, Rust, or Clojure, all kinda functional interfaces but not completely pure or well founded, and yet they still don’t really have great workspaces like an excel sheet or a power query editor. Kinda amazing.
With BYROW and BYCOL this example can be done a lot easier, with the difference, that you will have to select the whole range of replacement words and not just the first cell. Thanks for the F2 tip in name manager that was super anoying until now.
Lambda functions are really powerfull. We, the developers, don't even know how much it will impact sheets creations from now on. I can already see people creating libraries full of personalized lambda functions to expand what Excel can do. I didn't really had time to do it, but I imagine that we finally can have fully optmizable neural networks or genetic algorithms working in Excel without any VBA or usage of Solver, just with lambda functions.
Another Thursday 'Must-Watch' date with Leila … but today's OMG moments required a pot of black coffee to watch both Lamdda videos three times … and I still have much work to do. For me, many of these functions are like getting a drink of water with a firehose … but the ideas … ideas … ideas are numerous!!! I leave hard copy post-it notes and digital post-it notes all over my financial models to remind myself to review again and incorporate Leila's lessons. Overwhelming … but so good … so well explained. As always … your are a treasure … thank you … thank you … thank you …
Trust me, it took me a long time and a lot of post-its to get the hang of it. But when it finally clicks it offers so many amazing possibilities. As always, thanks for your support!
Wow, Leila! This example is exceptionally cool in that it would allow you to add to your list of errors(b) and corrections(a) that it can fix without any change to your formula.
I have NO IDEA how you managed to pack so much into a video that length. I had a column of data where I need to strip out multiple extra words before and after the value I wanted to end up with. I tried it with a series of SUBSTITUTE commands, which worked, but required me to create a new column for each pattern to replace. Then I tried it again using your MegaReplace, and it worked like magic!
You make it look so easy, yet I’m sitting here thinking you’re using some form of magic. 🧐 What? How? 🤪 I love watching an artist at work. The ability to start with nothing and end up with something beautiful.🤩
Wow. It's a Christimas gift before Christmas. I just activated the beta channel so I be able to use Lambda. It will save me a lot of time and make my workbook easier to understand.
Hi Leila, Based on this I tried multi filter.. scenario is there are 5 filter criteria based on selection of filter criteria we need to either filter 1 r 3 r 5. We can use filter with + or* as filter criteria is dynamic..
A caution about the blank, "", as an escape from the recursive lambda is worth mentioning. Having a "" in a cell isn't (necessarily) the same as an empty cell; so be careful. There are cases Excel may interpret empty cell values differently than you expect as if there are hidden characters lurking around.
It is better than using find and replace functions. Hopefully everyone can get to use it soon. Thanks for the long detail explaining of the use for this Lambda functions.
Wow, i am blown by the power of this function. Excell is slowly stepping into coding territory my head is still spinning but I will get it. thank you Leila :)
Hi Leila. Awesome! I developed recursive functions when programming in LISP years ago, with a twist in that the function was able to modify iteslf on the fly during recursions and do things that I couldn't figure out how to implement any other way. I have missed that power since moving on to other languages. Great to see this functionality in Excel!
This is going to help in many more ways which is hard to perceive at this moment as the function is new and limited in examples. Look forward to more examples in its application.
This opens up Pandora's box, Now we have a tool to work on - try and try. Relate to our previous work, test where you can use it and create future intelligence. Thanks a Lot Lela
I will be glad when this function is available on my Excel Insider version. Like you said a few months back, Microsoft has got better at delivering updates quicker, but there’s still room for improvement. Hopefully we won’t be waiting around for a year for it to be implemented in the main version.
Great for simple stuff to mid stuff. But cannot replace vba for more complex things. For this example, VBA can call RegEx, which is far superior in complex string-matching. Still, liked it very much and I am soooo using it already thanks to her videos =3
Another great video and wonderful explanations. I still don't have this LAMBDA function but, thanks to you, I'll be ready for it. At last, I hope so. ;-) Oh, and I love it when you say "…here is how things can get complicated…". With Excel and people like you, life is never boring, there's always something to learn. So, a big thank you.
=LAMBDA(T, B, A, IF(B = "", T, MegaReplace( SUBSTITUTE(T, B, A), OFFSET(B, 1, 0), OFFSET(A, 1, 0) ) ) ) It seems that, at each iteration, the function is performing the following assignments: T = SUBSTITUTE(T, B, A) B = OFFSET(B, 1, 0) A = OFFSET(A, 1, 0) Therefore, it seems that the arguments of the inside function with the name of the LAMBDA function are the new values to assign to the parameters of the LAMBDA (T, B, A) in each iteration. The OFFSET function takes a reference and returns another reference. Thus, the last two assignments move the reference of a cell. Because the first assignment runs before the second and third, the first substitution uses the content of the cells given as arguments, before any shift by the OFFSET function. Because, after the last substitution, B and A are updated, B ends up located after the last row; therefore, in the next iteration, the exit condition (B = "") is TRUE. Please comment and correct the above!
I’m going to be re-writing a lot of functions with this! I would be really helpful to have the pop up for the parameters when writing the function. Maybe, in addition to a comments section in name manager, there could be an option to add the parameters. Enjoying your videos!
Yep - it was definitely worth the wait since watching part 1! Thanks and liked the simple yet effective explanation on the term 'recursive', especially with the visual flow aid
Ufff!!!! Stunningly beautiful concept. YOU made my life easy. The same concept I tried with Find function. But giving NO solution. Can you shoot video on FIND function recursive lambda. By the way, I took course on Power Query, your explanations are very simple and easy to understand. I have become fan of yours.
WOW!!!! I have to play with this... working with large datasets with ‘incorrect’ nomenclature in 4 columns to standardize before pricing and importing - wow. I’m loving your vids, and will be signing up for your courses once my transition to corporate finalizes!
Recursion first came across it in the 1980s with the QL computer super basic. It also had functions and you could also create your own new super basic commands. There was also coercion. Very powerful when used correctly. Your previous video on protecting a spread sheet, can this be done automatically? Without having to resort to sheet protection or vba? A cell that contains a formulae will not be overwritten. Great work Regards John hanrahan
Great video, good points, Super mega extrafragilistic cool!! Lambda, rocks, now, next challenge, a (kind of single cell) formula, with 2 array variables, the whole column and 2nd, the 2D array of replacements.😉😉✌✌
lol - Imagine, we would have seen it on the last Excel conferences. Listen! Do you hear the noise and amazement in the audience. Hope to join you in line number 5 another time (right next to the propeller) ✈
Thanks Leila for providing such a detailed explanation. I am sure you will come up with even more amazing examples of Lambda’s use. Have a great Xmas 😃
History have been repeated again today! 😂 Yesterday i was watching this video, and today again i've helped to my colleague with this video. 🤗 Thanks a lot Leila!💃
@@LeilaGharani, this is true, and we have to acknowledge that there is hard work and, of course, deep knowledge behind these videos. And your courses are a good way to know Excel to work with; it is a good platform for learning, and your team is great.
Hello, how are you? This example seems perfect, in which you use the substitute function. I am trying to do the same but with the find function. In Table A I have texts that can contain information from the first column of Table B. When you find it, you must place the second column of Table B. If you can't find it, put "N / A". When I do I send you the example. Thanks for inspiring us.
Thank you first of all for your fantastic work as a popularizer. I followed your tutorials and those of many others on recursive lambda, it seems to me a fascinating function and I pulled down about fifty examples in which to use it trying to use traditional formulas to get the same answers. The impression I got is that in reality the scenarios that can only be solved with recursive lambda are really limited (two or three, no more). Am I just having a limited imagination or is that really the case?
This video is great, thank you for sharing! Can I ask, how do you come up with the logic to solve this problem? I seem to struggle when it comes to building up logic and applying to problems at work.
This is fantastic. I wonder if you could add functionality that dynamically adds new unfamiliar entries to the right-hand table, that some manager could periodically update with their correct formats.
This made me a bit sad..Back in the late 80s I was studying electronic engineering and I took a course in computer programming.. it was in PASCAL. (yes it was big then 🤣).. When we got to Recursive programing We got a home assignment to "solve" the solitaire "Towers of Hanoi" with ten rings.. The only clue we got was that "You solve the puzzle with ten rings the same way you do with nine..and then you add one ring" What makes me sad is that I could never get it and the thing that was lacking was in my own intelligence..We all have a point when we get of the train.. that was mine 🤣🤣🤣.. I always appreciate your videos and I'll use this to test my brains limitations 🤯
Hi Ma'am, It was a pleasure to learn Recursive LAMBDA from you. But the OFFSET Function is volatile. Though using it in LAMBDA will not affect your performance much, we can use the INDEX Function and remove the volatility. I have worked up an INDEX + LAMBDA combination. I hope you will like the same. = LAMBDA( source, before, after, LET( Formula, LAMBDA( ME, s, b, a, n, IF(n > ROWS(b), s, ME(ME, SUBSTITUTE(s, INDEX(b, n), INDEX(a, n)), b, a, n + 1))), Formula(Formula, source, before, after, 1))) Please provide me guidelines if I can improve it further.
Grab the file I used in the video from here 👉 pages.xelplus.com/lambda-file
You know you are lost to humanity when you get excited for a new function on Excel 😂😂😂
You rock Leila !
WOW! I don't know what to say. I'm a bit overwhelmed but fascinated.
Welcome in my world ;-)
Great explanation of this concept. Note that the name you give your parameters will appear in the function screen tips (the little pop-up that comes under your function when you are inside it). So if you name them "Text", "Before" and "After" instead of t, b and a, then it will be much easier for the user to figure out what to input in each parameter.
I come from a Python background and always wished there was a way to use loops in Excel without having to learn VBA. This video was my answer. Thanks!
This is just so mind-blowing that I don't mind feeling a little nerd watching an Excel video on a weekend! 😂🤯
ME TOO!!
Same here friday and saturday night for me
Wow, I have puzzled over this for years. Thank you for explaining this. The function is like repeating mirrors that reflect off of the most recent reflection, but the difference is that in Excel you can program the way out for that latest reflection/replacement. This was very helpful!
Finally the ability to add coding, looping logic without VBA. Very nice and well explained . As always.
This would be the most exciting video you've done! Crystal clear explanation of recursive use of the Lambda function. Brilliant, thank you.
Wow, thank you!
Just great. Perfect explanation, no useless info, to the point!
Great!! Wrote a recursive lambda that can handle the whole string column array
RPLAB(ar,a,b,i)=LAMBDA(ar,a,b,i,LET(r,ROWS(a),IF(i=r+1,ar,RPLAB(SUBSTITUTE(ar,INDEX(a,i),INDEX(b,i)),a,b,i+1))))
--replace a with b for array ar and i=1
If you don't like extra variable i we write another one REPLACEAB(ar,a,b)=LAMBDA(ar,a,b,RPLAB(ar,a,b,1))
Anyhow "i" can be very handy in case you want to start the search from a different index in the replace list.
Used Python for most of my data work but at my new job was forced to use Excel. Your tutorials have been a blessing. Really appreciate the work you put on these!
Our pleasure, Daniel!
Oh, Leila - this is an eye-opener! Thanks for sharing your knowledge. Actually I've got so many ideas to realize with LAMBDA, but recursive functions double the impact of this function. Can't wait to get the function at my insider-installation. Und ich hoffe auf 2021! Frohe Weihnachten und bleib gesund, Andreas.
It really is a great development. Auch dir Frohe Weihnachten und ein gutes Neues Jahr!
I love this! This is super interesting. It's like programming visual basic inside a cell without the actual vb language. Thanks for this video! :)
True 😁
My thoughts excactly.
Hey Leila, Big fan and have been watching you for years. I recently came across the magic of Map and Lambda in Google sheets / Excel and I would definitely would love to take it for a spin. I haven’t had the chance to use recursive lambda but one day, for sure.
Sounds great!
The most powerful statement you made was right at the end. The next generation of excel users will be intuitively learning functional programing techniques. I suspect that this was perhaps the deeper reason behind Simon Peyton Jones decision to join Microsoft. Personally I am delighted as I know from experience that it takes time to become fluent with these techniques.
Absolutely agree. I was in the camp that VBA aged, and we should provide support for language such as Python/ R natively in Excel. But after seeing these recent paradigm shift in Excel programmability, I'll agree that I was short sighted.
Any general-purpose programming language is a distraction for the population who uses Excel. I am a programmer, and still love Excel as it allows you to focus on the activity rather than how to do it. Lambda (and all the recent functions to support it) feel so tightly integrated. This is probably the best evolution in Excel, ever.
That’s interesting-SPJ has worked a lot on the same kinds of things-perhaps with more emphasis on formal methods intersecting with domain languages. He seemed to weigh in stuff like formal methods in secure operating systems, while still being hyper focused on thinking about language ecosystems like his comparisons about Java.
Having just stopped running a company I am back in a job in finance, yet I still have a heavy addiction to programming. This is giving me exactly what I need to stay interested in what I’m doing. I’m seeing that there’s actually good structure here-and it is textbook ML style FP.
However I still get bugged by the functions not being first class polymorphic functions where you can pass in labels with values dependent on higher order functions though.
Example…I’m typing in a structured reference this morning like to a table called [APR] which is also a name of a table column like [@[APR]], and yet there are no operads for me to touch the concept of the string-it’s still objects. It’s still something that is not well defined in the sense of Grothendieck Universes, Agda, or even Haskell’s system F.
Another example-I’m writing a Sumif with structured references and embedded conditional criteria-but it’s dax sometimes and it’s something else other times. I can’t know how the value will be represented in memory-will an embedded conditional be passed up to a Calling function, hit a type error I never see, or is it simply an undefined parameterization because the designers didn’t intend for users to make criteria into multi-function call chains?
I’ve been in the lisp world a bit more than usual these past several months entertaining myself with nix and I can never imagine doing series fast paced corporate work with those-only building systems. And yet these don’t have the barriers that make it harder to express deep relationships about data and the many people oriented issues around that data. You’ve gotta write most stuff yourself, and outside of excel it’s not gonna be in some nice text editing space where the rules are standard.
For example, no two emacs configs are alike. Just check out the many inconsistencies in packages between scala, OCaml, python, R, Rust, or Clojure, all kinda functional interfaces but not completely pure or well founded, and yet they still don’t really have great workspaces like an excel sheet or a power query editor. Kinda amazing.
amazing to see that it is possible now to create lambda functions in excel and that no vba is required 😀. Big game changer! Great explanation, thanks!
Glad it was helpful!
I think Excel is reaching its new heights in applications. Everyday it is providing something new.
True. Lots of great developments recently.
With BYROW and BYCOL this example can be done a lot easier, with the difference, that you will have to select the whole range of replacement words and not just the first cell.
Thanks for the F2 tip in name manager that was super anoying until now.
you are right! REDUCE might also help this to be leaner. (great video though :))
Lambda functions are really powerfull. We, the developers, don't even know how much it will impact sheets creations from now on.
I can already see people creating libraries full of personalized lambda functions to expand what Excel can do.
I didn't really had time to do it, but I imagine that we finally can have fully optmizable neural networks or genetic algorithms working in Excel without any VBA or usage of Solver, just with lambda functions.
I had already thought of this implementation, but thanks for the confirmation that you believe that it can be done.
Sky is the limit 😊
Hello Leila, you are very intelligent, a very good teacher and the best: very beautiful!
Another Thursday 'Must-Watch' date with Leila … but today's OMG moments required a pot of black coffee to watch both Lamdda videos three times … and I still have much work to do.
For me, many of these functions are like getting a drink of water with a firehose … but the ideas … ideas … ideas are numerous!!! I leave hard copy post-it notes and digital post-it notes all over my financial models to remind myself to review again and incorporate Leila's lessons. Overwhelming … but so good … so well explained.
As always … your are a treasure … thank you … thank you … thank you …
Trust me, it took me a long time and a lot of post-its to get the hang of it. But when it finally clicks it offers so many amazing possibilities. As always, thanks for your support!
Wow, Leila! This example is exceptionally cool in that it would allow you to add to your list of errors(b) and corrections(a) that it can fix without any change to your formula.
Thank you! Yes. It's great that it's dynamic and no need for refresh or updating formulas.
I have NO IDEA how you managed to pack so much into a video that length. I had a column of data where I need to strip out multiple extra words before and after the value I wanted to end up with. I tried it with a series of SUBSTITUTE commands, which worked, but required me to create a new column for each pattern to replace. Then I tried it again using your MegaReplace, and it worked like magic!
Glad it was helpful, Anthony!
What a fantastic feature, and what a fantastic video. Thank you Leila!
Many thanks, Adrian!
You make it look so easy, yet I’m sitting here thinking you’re using some form of magic. 🧐 What? How? 🤪
I love watching an artist at work. The ability to start with nothing and end up with something beautiful.🤩
😊 thank you. It's definitely not the easiest topic 😁
Wow. It's a Christimas gift before Christmas. I just activated the beta channel so I be able to use Lambda. It will save me a lot of time and make my workbook easier to understand.
Christmas came early for me too this year :)
Hi Leila,
Based on this I tried multi filter.. scenario is there are 5 filter criteria based on selection of filter criteria we need to either filter 1 r 3 r 5. We can use filter with + or* as filter criteria is dynamic..
It will take a while to get my head around this one but with your help I've started. You have become my goto for anything Excel...Thanks!!
Thank you, Robert!
A caution about the blank, "", as an escape from the recursive lambda is worth mentioning. Having a "" in a cell isn't (necessarily) the same as an empty cell; so be careful. There are cases Excel may interpret empty cell values differently than you expect as if there are hidden characters lurking around.
True, thanks for that tip!
Wow so now we are having not only a custom functions in Excel but also a custom recursive ones... Thanks a million Laila
You are welcome, Ibrahim 😊
This is definitely advanced. I thought I was an advanced user, but I did not know Excel could do this.
It is better than using find and replace functions. Hopefully everyone can get to use it soon. Thanks for the long detail explaining of the use for this Lambda functions.
You are welcome!
Wow, i am blown by the power of this function. Excell is slowly stepping into coding territory my head is still spinning but I will get it. thank you Leila :)
My pleasure :)
Best of your awesome videos so far that I've seen 👌
Wow, thanks!
Very well explained Leila. It's never easy to find a recursive example but this one rocks 👍
Thank you! 😃
Hi Leila. Awesome! I developed recursive functions when programming in LISP years ago, with a twist in that the function was able to modify iteslf on the fly during recursions and do things that I couldn't figure out how to implement any other way. I have missed that power since moving on to other languages. Great to see this functionality in Excel!
Now there's a programing language I haven't touched in a VERY long time!! LISP = Lost in Stupid Parenesis
This is going to help in many more ways which is hard to perceive at this moment as the function is new and limited in examples. Look forward to more examples in its application.
This opens up Pandora's box, Now we have a tool to work on - try and try. Relate to our previous work, test where you can use it and create future intelligence. Thanks a Lot Lela
The Father of all Functions is out here.. Thanks Leila for such wonderful explanation!! 😊
You are so welcome!
Very Powerful, and Lambda is hard to understand, but you have made it a whole lot easier. Stating the obvious, You're really good.
Thank you, Justin!
Colours of 2021... Grey and yellow...you are updated and so are we...thanks
Thanks Leila ...the way you explained Lambda function ... cleared alot of confusion for me
Happy to hear that, Hussein!
I will be glad when this function is available on my Excel Insider version. Like you said a few months back, Microsoft has got better at delivering updates quicker, but there’s still room for improvement. Hopefully we won’t be waiting around for a year for it to be implemented in the main version.
I hope so too. Let me know once you get it!
1 comment...always waiting for ur videos
She is one of the best, and her courses are pretty good; I'd say crucial if you want to know how to work with Excel in deep.
@@Galileo2pi yes.
Thank you so much 😀
Wow! This sounds like Sub() & Dim statements!
Also, iterative While statements.
Great for simple stuff to mid stuff. But cannot replace vba for more complex things. For this example, VBA can call RegEx, which is far superior in complex string-matching. Still, liked it very much and I am soooo using it already thanks to her videos =3
Another great video and wonderful explanations. I still don't have this LAMBDA function but, thanks to you, I'll be ready for it. At last, I hope so. ;-) Oh, and I love it when you say "…here is how things can get complicated…". With Excel and people like you, life is never boring, there's always something to learn. So, a big thank you.
Thank you, Claire!
Super. Thanks Leila ❤👍🌹
Very well explained Thank u Leila
Could you please add more videos on recursive lambda? This concept of excel is quite tricky
With LET and LAMBDA Excel becomes microprogrammed. This is amazing.
A new level of Ms Excel! Thanks Leila!
You're very welcome!
=LAMBDA(T, B, A, IF(B = "", T, MegaReplace( SUBSTITUTE(T, B, A), OFFSET(B, 1, 0), OFFSET(A, 1, 0) ) ) )
It seems that, at each iteration, the function is performing the following assignments:
T = SUBSTITUTE(T, B, A)
B = OFFSET(B, 1, 0)
A = OFFSET(A, 1, 0)
Therefore, it seems that the arguments of the inside function with the name of the LAMBDA function are the new values to assign to the parameters of the LAMBDA (T, B, A) in each iteration.
The OFFSET function takes a reference and returns another reference. Thus, the last two assignments move the reference of a cell.
Because the first assignment runs before the second and third, the first substitution uses the content of the cells given as arguments, before any shift by the OFFSET function. Because, after the last substitution, B and A are updated, B ends up located after the last row; therefore, in the next iteration, the exit condition (B = "") is TRUE.
Please comment and correct the above!
I’m going to be re-writing a lot of functions with this! I would be really helpful to have the pop up for the parameters when writing the function. Maybe, in addition to a comments section in name manager, there could be an option to add the parameters. Enjoying your videos!
Yep - it was definitely worth the wait since watching part 1! Thanks and liked the simple yet effective explanation on the term 'recursive', especially with the visual flow aid
Great to hear!
Thanks a lot for this very helpful presentation. It's clear that LAMBDA is an other great improvement for an advanced Excel user. 💪
Ufff!!!! Stunningly beautiful concept. YOU made my life easy. The same concept I tried with Find function. But giving NO solution. Can you shoot video on FIND function recursive lambda. By the way, I took course on Power Query, your explanations are very simple and easy to understand. I have become fan of yours.
9:35 wow I gave a like to the video for this tip alone. I've struggled so much trying to use arrow keys in that window!
WOW!!!! I have to play with this... working with large datasets with ‘incorrect’ nomenclature in 4 columns to standardize before pricing and importing - wow. I’m loving your vids, and will be signing up for your courses once my transition to corporate finalizes!
Glad it was helpful, Josh!
Recursion first came across it in the 1980s with the QL computer super basic. It also had functions and you could also create your own new super basic commands. There was also coercion. Very powerful when used correctly. Your previous video on protecting a spread sheet, can this be done automatically? Without having to resort to sheet protection or vba? A cell that contains a formulae will not be overwritten. Great work
Regards
John hanrahan
impressive Many thanks Leila
Great video, good points, Super mega extrafragilistic cool!! Lambda, rocks, now, next challenge, a (kind of single cell) formula, with 2 array variables, the whole column and 2nd, the 2D array of replacements.😉😉✌✌
This is taken directly from Microsoft's guide pages on recursive Lambdas, which I find particularly cheap.
Amazing.. videos
Super duper like 👍🏻
Super duper thanks Faraz 😊
lol - Imagine, we would have seen it on the last Excel conferences. Listen! Do you hear the noise and amazement in the audience. Hope to join you in line number 5 another time (right next to the propeller) ✈
Thanks Leila for providing such a detailed explanation. I am sure you will come up with even more amazing examples of Lambda’s use. Have a great Xmas 😃
My pleasure 😊 Merry Xmas to you too!
Thank you! I have wanted a way to do this on many occasions.
Wow. Great function and great explanation of new features
That's really a great development in Excel. Thanks for watching!
History have been repeated again today! 😂
Yesterday i was watching this video, and today again i've helped to my colleague with this video. 🤗
Thanks a lot Leila!💃
Amazing! Great job!
Excellent use case .. opens so many possibilities!!
Running out of superlatives to compliment your videos 🙏👍
Thank you! Cheers!
As always, great, you're one of the best on this matter, terrific. Thanks
Wow, thank you!
@@LeilaGharani, this is true, and we have to acknowledge that there is hard work and, of course, deep knowledge behind these videos. And your courses are a good way to know Excel to work with; it is a good platform for learning, and your team is great.
Amazing Explanation and very straight forward Leila, if you came up with this by yourself you're very smart :D
Hello, how are you?
This example seems perfect, in which you use the substitute function.
I am trying to do the same but with the find function.
In Table A I have texts that can contain information from the first column of Table B. When you find it, you must place the second column of Table B. If you can't find it, put "N / A".
When I do I send you the example.
Thanks for inspiring us.
Thank you first of all for your fantastic work as a popularizer. I followed your tutorials and those of many others on recursive lambda, it seems to me a fascinating function and I pulled down about fifty examples in which to use it trying to use traditional formulas to get the same answers. The impression I got is that in reality the scenarios that can only be solved with recursive lambda are really limited (two or three, no more). Am I just having a limited imagination or is that really the case?
OMG, that's great
I need to see it again and again, thank you Leila for teaching me a lot of things about Excel
You're very welcome!
Back ground music is nice
You are a brilliant tutor. Thanks
Great thinking from you, nice development.
I feel like going back to my programming classes right now 💐
Thanks, Leila
thanks so much. From Viet Nam with love
You are such a great teacher!!!
This video is great, thank you for sharing! Can I ask, how do you come up with the logic to solve this problem? I seem to struggle when it comes to building up logic and applying to problems at work.
Thanks for the great work Leila!👌 The music in background is a bit distracting. I hope you consider some meditation music instead! 😉
There was music? I was so focused on what she was saying I didn't even notice it 🤣
Awesome. Is there a way to like your videos more than once ?
Thanks for all your awesome content Leila! Promise to buy you more coffees are I go through more :)
Yay! Thank you!
@@LeilaGharani 😊😊 thanks again for all your content
This is extraordinary! Thanks for the detailed explanation. bravo!
Glad you enjoyed it!
Great videos, thank you
After watching your video, it really makes me wonder what took Microsoft so long to come up with Lambda.
Familiar concepts from Python loops (and other languages, of course). Very interesting.
This is fantastic. I wonder if you could add functionality that dynamically adds new unfamiliar entries to the right-hand table, that some manager could periodically update with their correct formats.
Incredible example!!!!
so well explained! thank you!
i like recursive lambda it's very interesting
Thanks for this video.
This made me a bit sad..Back in the late 80s I was studying electronic engineering and I took a course in computer programming.. it was in PASCAL. (yes it was big then 🤣).. When we got to Recursive programing We got a home assignment to "solve" the solitaire "Towers of Hanoi" with ten rings.. The only clue we got was that "You solve the puzzle with ten rings the same way you do with nine..and then you add one ring" What makes me sad is that I could never get it and the thing that was lacking was in my own intelligence..We all have a point when we get of the train.. that was mine 🤣🤣🤣.. I always appreciate your videos and I'll use this to test my brains limitations 🤯
Thank you very much for burning my brain in a good way all mighty Leila 🔥
You’re welcome 😊
Amazing tutorial. Well done
Hi Ma'am,
It was a pleasure to learn Recursive LAMBDA from you.
But the OFFSET Function is volatile. Though using it in LAMBDA will not affect your performance much, we can use the INDEX Function and remove the volatility. I have worked up an INDEX + LAMBDA combination. I hope you will like the same.
= LAMBDA(
source,
before,
after,
LET(
Formula,
LAMBDA(
ME,
s,
b,
a,
n,
IF(n > ROWS(b),
s,
ME(ME, SUBSTITUTE(s, INDEX(b, n), INDEX(a, n)), b, a, n + 1))),
Formula(Formula, source, before, after, 1)))
Please provide me guidelines if I can improve it further.
In the last-but-one line of code, you write ME as if it were the name of a function. Would it be too much to ask you for an explanation?
Thank you Leila. You are right, it looks complicated.
It takes a while to get the hang of it but it's really powerful.
Thanks for detailed explanation in a brilliant way. Name manager has limitations to use in the same workbook right ?