Excel LAMBDA Recursion + a Trick for Evaluating in a Cell

[@guranslifes]

you are the best 'teacher', Mynda, out of all MVPs

[@MyOnlineTrainingHub]

Aw, you're too kind! Thank you so much 🥰

[@rudiklein]

🤯 I shouldn't be watching these kind of videos before going to sleep.

[@MyOnlineTrainingHub]

:-) at least it didn't put you to sleep!

[@IvanCortinas_ES]

Thank you for this tutorial Mynda. I am already waiting this function in my Excel 365. That's a very powerful function!

[@MyOnlineTrainingHub]

You're welcome, Ivan!

[@patrickschardt7724]

Keeping track of the exit strategy and testing recursion are very hard. Great stuff

[@MyOnlineTrainingHub]

Indeed, Patrick!

[@sunnybaggu785]

Your explanations, presentation and instructions are very good and interesting. Thank you for sharing excel tips this way. IAM sure many people like get to learn from your videos. Thank you..

[@MyOnlineTrainingHub]

So nice of you to say so, Sunny! Thank you.

[@sunnybaggu785]

@@MyOnlineTrainingHub thank you madam, apart from youtube, we try to follow and get updates from your email subscription and LinkedIn too.

[@kltzy]

Wasn't familiar with the ME functionality. Thanks Mynda, a good push in the right direction.

[@MyOnlineTrainingHub]

Happy to help!

[@darrylmorgan]

Hi Mynda!Great Tutorial.Very Interesting Plus Nice And Easy To Understand...Thank You :)

[@MyOnlineTrainingHub]

Glad you liked it, Darryl!

[@samaruti9446]

Mynda, you have a great way of taking something complex and making it simple to understand, Can you please do the same with the ME parameter?

[@MyOnlineTrainingHub]

Thanks for your kind words! I know what you mean about the ME parameter. I'll add it to the list 😁

[@gheorghebidiac2975]

As you said: "mind bending"! :D

[@MyOnlineTrainingHub]

:-D

[@DougHExcel]

Great example! LAMBA could replace some VBA scripting so it's good to know

[@MyOnlineTrainingHub]

Absolutely!

[@chrism9037]

Wow. Time for an Advil, Mynda!

[@MyOnlineTrainingHub]

:-) I know!

[@mohammedahmed8295]

Appreciate your efforts

[@MyOnlineTrainingHub]

It's my pleasure, Mohammed!

[@ItzAsgharHussain]

Hi, in my excel workbook I have 200+ sheets & sheet names defined. Every sheet contains data like student name father name address and mob no.s if I enter sheet name I should get all the data in that particular sheet

[@MyOnlineTrainingHub]

Unfortunately, the structure of the file goes against best practice, so any way you approach this will be a workaround. I recommend you use Power Query (kzbin.info/www/bejne/gmWlpoiwmMh_ptE) to consolidate the data into the correct tabular format: www.myonlinetraininghub.com/excel-tabular-data-format

[@ItzAsgharHussain]

​@@MyOnlineTrainingHub Thanks for reply, I've seen the video and your website, I have my data in one workbook and in that I have 200+ sheets (tabs). The data is well defined and in good format. All I want is, in the index sheet (tab), if I enter the sheet(tab) name I should get the data present in that particular sheet(tab). Just like vlookup. I watched all your vlookup videos and liked it, In vlookup the lookup value is a cell, I get one cell data from different sheet, here the lookup value should be sheet name & I want all cells data or multiple cell data from different sheet by entering sheet name.

[@MyOnlineTrainingHub]

I understand what you want, but formulas are not suitable. You should fix the data layout and then you can use Slicers to filter the data to show what you want.

[@ItzAsgharHussain]

@@MyOnlineTrainingHub Ok mam understood, thanks a lot

[@abdelkrimmesaiahmed662]

Thank you for this amazing video

[@MyOnlineTrainingHub]

Thank you!

[@ricos1497]

Nice, I like the use of ME, I might try that. The bit I struggle with is how to bail on complex recursive functions, maybe where you've added a counter or whatever. Even in your example, it's not completely obvious how returning 1 in the IF() statement actually causes the function to return 120 (and not 1). That's the bit I struggle with when writing out recursion with more than one parameter, and when let gets involved and so on. I've actually written a couple where I added in another cntr variable, and a text variable that passed back the "state" of variables in the LET function and the counter, so I could effectively see which loop the thing broke on!

[@Excelambda]

Haha, you are everywhere. Is very simple to understand how recursive works, (why is 120 and not 1), because only after finds the base case, the 1, has all the terms to conclude the calculation . for example fact(4) computer > 4 * fact(3) > 4*3*fact(2) > 4*3*2*fact(1) >condition met fact(1) =1 > 4*3*2*1 bam result 24 That's the idea of recursive, never leaves the calculation , continues because it calls itself but finishes only when it has all the terms known.

[@ricos1497]

@@Excelambda Yes, that's a great way of explaining it! When I said it wasn't completely obvious, I meant to the layperson. It's a concept that doesn't exist elsewhere in Excel, and doesn't quite explain itself, so if you're looking at a recursive function, it's difficult to get by just looking at it. I'll steal your explanation for when I have to explain it to others!

[@antonyroussel]

Hi! I've been trying to find ways NOT to use VBA, but I haven't been able to find a way to trigger an action (whatever that might be - just say calculate a formula in a cell), based on user interaction without it. Is there a way to perform an action if a user clicks on some kind of button or link etc, without using VBA?

[@MyOnlineTrainingHub]

You can try using Office Scripts which is just another programming language, but scrips are executable on the web.

[@realpulsecoin]

Great content!

[@MyOnlineTrainingHub]

Thanks, Rasmus!

[@sktneer]

Another way to do this is as below... Assuming the number is in A2, then to find the factorial of the number in A2, we may use the following formula directly in the cell. In B2 =REDUCE(1,SEQUENCE(A2),LAMBDA(n,r,n*r))

[@MyOnlineTrainingHub]

Thanks for sharing!

[@sktneer]

@@MyOnlineTrainingHub You're welcome, Mynda!

[@johannes3020]

Hello, I have a problem: I'm trying to make a stock price chart show the points at which I've purchased the stock. Would you know how to do this? My attempts fail since the dynamic arrays conflict with each other. Thanks if you'll devote some time to helping.

[@MyOnlineTrainingHub]

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

[@abdelkrimmesaiahmed662]

Mynda there is no link to download the excel file

[@MyOnlineTrainingHub]

Sorry! It's there now.

[@abdelkrimmesaiahmed662]

Thanks a lot

[@TheEXCELGURU]

Hi there, i don't see the download link

[@MyOnlineTrainingHub]

So sorry! It's there now!

[@drsteele4749]

Does the fact you're using LAMBDA cause your cursor to jump down one cell after every entry, forcing you to move it up every time?

[@MyOnlineTrainingHub]

No, the lambda doesn't affect the cursor.

[@ZaneRinke]

Mynda, Help! Surely one can use Lambda and it's recursive nature to create a Goal-Seek function. All searches fall short.

[@MyOnlineTrainingHub]

I imagine so, but I don't have any tutorials I can point you to and I suspect Goal Seek is more efficient.

[@ZaneRinke]

Thank you for responding. Normally, I would use goal seek, but here we are talking about hundreds of lines, with changing variables. I was hoping for a formula based solution.

[@aquetongmedia]

@@ZaneRinke did you ever find this?

[@GeertDelmulle]

That ME parameter must be something special, because defining recursive LAMBDAs as a local variable in LET normally doesn't work in my experience. Good to know. Thanks.

[@GeertDelmulle]

Hey Mynda, Here's a little challenge for you: another famous example for recursion (ever since the Middle Ages) are the Fibonacci numbers: F(n)=F(n-1)+F(n-2). It leads to the concept of the Golden Ratio. Can you show us how to create a recursive LAMBDA for this and calculate, for instance, Fibonacci(55)? While you're at it, maybe you want to do this both in Excel using a recursive LAMBDA and in Power Query as well, since you know so well how to calculate recursive things in the most efficient way in PQ-M... Are you up for the challenge?

[@MyOnlineTrainingHub]

Hi Geert! Fellow MVP Liam Bastick has written about using LAMBDA for Fibonacci here: www.sumproduct.com/news/article/lambda-formulaic-recursion-its-all-about-me

[@GeertDelmulle]

@@MyOnlineTrainingHub Ah! you're so lazy! LOL! :-) Just kidding. Yes, he got it right and linearized the calculation (the default 'trap'-implementation actually has a Fibonacci series for the calculation time itself, so you do not want to use that.) BTW: you don't need a double IF, so his formula can be simplified further, ever so slightly. FYI, you can check out the Mr.Excel Message Board in the LAMBDA section for other implementations (we didn't use the ME function there).

[@romulusmilea2747]

One question about 3:30 moment: then name was defined, you did not put the = sign before that IF, on the Refer to field. Excel users who used/created named formulas in the past, or even simple names did use = sign. Why this detail was not explained ? I have observed that many Excel experts on KZbin are in competition with each other, who can explain faster, and faster, details do not matter, the ultimate goal is to be as quick as possible. This is not teaching, it is just speeding. However, you do not seem to be one of them, but still I do not understand why explanation about that = sign is missing (surprisingly, on 5:21 the equal sign was used). Thank you.

[@MyOnlineTrainingHub]

Hi Romulus, the formula does include the equals sign, it's just that the formula is long and you're only seeing the second line in the name manager. If you notice the formula I copy from the cell at 3:18 includes the equals sign.

[@JoseAntonioMorato]

I really liked the explanation of the possibility of testing the recursive LAMBDA using the LET function 👍, but this is not very simple to use. 🤔 I inform you that the download file link was missing. 😊

[@MyOnlineTrainingHub]

Thanks, Jose. Not much about LAMBDA is easy. It's an advanced technique, but hopefully you can adapt the examples to your own formulas. I've added the link now. Sorry about that.

[@JoseAntonioMorato]

@@MyOnlineTrainingHub It is an advanced technique, but with the help of videos like yours, little by little we will be able to understand. Thankful. 🤗

[@George-lp3qb]

Also they need to come up with a solution for the argument IntelliSense, otherwise, in my opinion, VBA UDF is still better especially that it is application level

[@MyOnlineTrainingHub]

They're working on it 😊

[@micheleradogna4921]

Can i use factorial for obtain a list? For example, if i want a list with all the number, between 5 and 1(4,3,2,1). Once for a Row. *5* 4 3 2 1

[@MyOnlineTrainingHub]

Hi Michele, you can use the SEQUENCE function for this: www.myonlinetraininghub.com/excel-sequence-function

[@RR-fv2uu]

can u help me

[@MyOnlineTrainingHub]

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

[@beegdigit9811]

Excel has become a functional programming language lol

[@MyOnlineTrainingHub]

😁 yep!