Bisectional method using recursive Lambda

This idea popped up in my head and after watching some YT about recursive lambda I played around with it for a few hours.
The attached workbook shows a sloppy prototype that I 100% think should work. But it doesn't. What am I doing wrong?

EDIT: There is a better workbook in post #4 and a solved one in post #15.

What is it supposed to be doing? Please explain the logic. Which are the expected results?

Without going into any detail, are you sure it will work to use RECSOLVE within the LAMBDA that is RECSOLVE. As the RECSOLVE is being created, it won't be able to calculate within itself.

RECSOLVE Lambda:
=LAMBDA(min,max,X,goal,IF(ABS(X*3-goal)<1,X,RECSOLVE(IF(X*3-goal>0,min,X),IF(X*3-goal<0,max,X), IF(X*3-goal>0,(min+X)/2,(X+max)/2))))

For example, you get a result if it is created as below:
=LAMBDA(min,max,X,goal,IF(ABS(X*3-goal)<1,X,IF(X*3-goal>0,min,IF(X*3-goal<0,max,IF(X*3-goal>0,(min+X)/2,(X+max)/2)))))

Originally Posted by AliGW

What is it supposed to be doing? Please explain the logic. Which are the expected results?

Ok, that was a very sloppy workbook.
I cleaned it up and added some text here.

Originally Posted by CheeseSandwich

Without going into any detail, are you sure it will work to use RECSOLVE within the LAMBDA that is RECSOLVE. As the RECSOLVE is being created, it won't be able to calculate within itself.

Some people on internet claim it will work:
Microsoft MVP Leila Gharani shows an example here:https://www.youtube.com/watch?v=L7s6Dni1dG8

Mynda Treacy from MyOnlineTrainingHub shows an example here: https://www.youtube.com/watch?v=XAGdSKMV4wA

Article from Ablebits.com that talks about recursive Lambda: https://www.ablebits.com/office-addi...unction-excel/

Article about Lambda making Excel Turing complete and recursive: https://www.infoq.com/articles/excel...ring-complete/

To be honest recursive Lambda is something that is fairly new to me, it just didn't look right. I may well be wrong.

I agree with Cheese Sandwich.

Additionally you have multiple criteria that are the same:

=LAMBDA(min,max,X,goal,IF(ABS(X*3-goal)<1,X,IF(X*3-goal>0,min,IF(X*3-goal<0,max,IF(X*3-goal>0,(min+X)/2,(X+max)/2)))))

abd Excel will only resond to the first.

There are other anomalies.

What RESULT (value) would you expect the formula to return?

Originally Posted by AliGW

What RESULT (value) would you expect the formula to return?

The equation is 3*x=555 so result I expect is 555/3 which is 185.

Originally Posted by AliGW

Additionally you have multiple criteria that are the same:

=LAMBDA(min,max,X,goal,IF(ABS(X*3-goal)<1,X,IF(X*3-goal>0,min,IF(X*3-goal<0,max,IF(X*3-goal>0,(min+X)/2,(X+max)/2)))))

abd Excel will only resond to the first.

Those are formulas for the different parameters for the next recursive step.
It will for sure evaluate all of them regardless if they are similar or not.

Originally Posted by zbor

From the Help: If you call a LAMBDA function from within itself and the call is circular, Excel can return a #NUM! error if there are too many recursive calls.

Yeah, that is why I'm doing these dumb formula tests on the first sheets. As expected with a bisectional formula with an error in the range of 0.5%, it narrows it down in like 10 steps or something.

The recursive lambda should be able to handle a lot more than that according to these articles:
https://techcommunity.microsoft.com/...ns/m-p/2056256
Here it mentions 1024 calls: https://www.sumproduct.com/news/arti...s-all-about-me

This could ofcourse mean that my stop criteria is not working the way I see it in my head and the whole thing loops out of control and throws the #NUM!.
Is my stop criteria wrong?

Those are formulas for the different parameters for the next recursive step.

But they aren't parameters.

I don't understand the mechanics of what you are trying to do well enough, but having dabbled with LAMBDA in named ranges myself, I know that the formulation you are using is not right, and that's why you get a #VALUE! error when the LAMBDA is tested correctly on the worksheet:

=LAMBDA(min,max,X,goal,IF(ABS(X*3-goal)<1,X,RECSOLVE(IF(X*3-goal>0,min,X),IF(X*3-goal<0,max,X), IF(X*3-goal>0,(min+X)/2,(X+max)/2)))),(F15,G15,H15,I15)

I don't have time to watch all the videos - sorry.

Originally Posted by AliGW

But they aren't parameters.

Yes they are. Are you familiar with the concept of recursive functions in general?

Originally Posted by AliGW

I don't understand the mechanics of what you are trying to do well enough, but having dabbled with LAMBDA in named ranges myself, I know that the formulation you are using is not right, and that's why you get a #VALUE! error when the LAMBDA is tested correctly on the worksheet:

=LAMBDA(min,max,X,goal,IF(ABS(X*3-goal)<1,X,RECSOLVE(IF(X*3-goal>0,min,X),IF(X*3-goal<0,max,X), IF(X*3-goal>0,(min+X)/2,(X+max)/2)))),(F15,G15,H15,I15)

I don't have time to watch all the videos - sorry.

You cannot use that method of applying the parameters in the end on a recursive lambda function.
Mynda mentions this in her video.

My work copy of 365 doesn't have LAMBDA yet, but it looks to me like you are just missing the final argument in your recursive call:

=LAMBDA(min,max,X,goal,IF(ABS(X*3-goal)<1,X,RECSOLVE(IF(X*3-goal>0,min,X),IF(X*3-goal<0,max,X), IF(X*3-goal>0,(min+X)/2,(X+max)/2),goal)))

HA! It was that simple! You have good eyes Rory. I'm smashing that rep button like Ivan Lendl!

Attached is the updated and working workbook.

Lambda now looks like this:

Formula:

Please Login or Register  to view this content.

and is implemented like this:

Formula:

Please Login or Register  to view this content.

Are you familiar with the concept of recursive functions in general?

Yes, and I shall turn a blind eye to the negative mplication of this question.

I am glad you got it sorted. Well done.

Looks like i need to learn about recursive Lambda!

I too am glad you got there in the end, it's the best way to learn IMO.

By the way, you need to mark the thread as SOLVED, please.

Please try

Formula:

Please Login or Register  to view this content.

EDIT: Oh I see you found this 23 minutes ago also.
I didn't see your post, beforeI added my solution, because it took me 40 minutes to find the issue.

Yeah Hans I know, my stop condition is silly but that's on purpose. This workbook is just a proof of concept.
I have a workbook at work in which I run through a table with a VBA bisection solver and I played around a lot with stop conditions there. There are multiple stop condition and I ended up putting them all together in one rather elegant formula with a general error percentage as input.
As for the red larger than sign, isn't that the same as I have in my formula?

O yes the red larger sign was correct in your file also.
Thanks for the nice question, now I learned a lot about recursive formulas in Excel.

Thanks for the positive feedback here people! It took me about a full day of only tinkering with this before I figured out the structure and got this far. Rory helped me stumble across the finishline.
I'm really happy about this, it feels like I found myself a new toy to play with.

I couldn't quite let it go, had to tidy it up a bit.:)
This version has a separate lambda for the function you want to solve which is of course how it should be.
And the solving accuracy is now a fraction of the goal and is an external parameter but this one could be either way I guess.

My final question is: Is there a use for this recursive lambda function solver?