Forum Discussion
Lambda Example: Generate Fibonacci series
SergeiBaklan Good point. I think FIB should probably truncate decimals to integers if entered as parameters to be consistent with other functions. It seems not much change is required to do that because SEQUENCE and INDEX functions already do this (though to be precise one should check values very close to integer as you say.)
=LAMBDA(n,
IF(n<3,
SEQUENCE(n,,0),
LET(b,FIB(n-1),
IF(SEQUENCE(n)<INT(n),b,INDEX(b,n-1)+INDEX(b,n-2)))
))
Viz I found this pretty challenging and don't find recursion very intuitive either. I first laid out the expected results for all input values from 1 to 10 horizontally referring to prior results using spill operator and then substituted LET to make it more efficient.
lori_mWas struggling to figure out how to extend an array as well, thanks for this example. Using your FIB array extension as a hint I've got a generic iterator layout after struggling with the formula a bit. Formula was returning a #NUM error, and figured out that TYPE(INDEX(Seed,ROWS(Seed)))=64 (array) rather than 1 for number. Any ideas on better way to do this? Decided to work through Project Euler problems to wrap my head around LAMBDA usage and this is part of my solution to question on Collatz sequence.
=LET(n,<cell_ref or hardcode number>,
LET(Next,LAMBDA(Prior,IF(ISEVEN(Prior),Prior/2,Prior*3+1)),
Seed,SEQUENCE(1,1,n,0),
Iterate,LAMBDA(ME,Seed,
LET(PriorVal,NUMBERVALUE(ARRAYTOTEXT(INDEX(Seed,ROWS(Seed)))),
CurrVal,Next(PriorVal),
CurrResult,IF(SEQUENCE(ROWS(Seed)+1)<(ROWS(Seed)+1),Seed,CurrVal),
IF(PriorVal=1,Seed,IF(CurrVal=1,CurrResult,ME(ME,CurrResult)))
)
),
Iterate(Iterate,Seed)
)
)
I have still to get to grips with your formula; I find recursive formulae especially opaque!
One observation, though. It is possible to conduct the recursion, and maintain a counter that could be used as a backup termination criterion, without building the array as you go.
When the end point is reached, all the values obtained along the way are still present within the memory stack and are accessible one at a time following return from the previous level. In the Fibonacci example I used this to combine the current value and the return array to build the complete series as an array of known length.
- Thanks Peter, I reviewed your version, and translated it for my case; I'll have to experiment with it some, but for this particular problem, I run up against the stack depth (about 170ish recursions deep with the parameter count/setup I'm using). I'm pleased that with your version, I avoid the NUMBERVALUE(ARRAYTOTEXT(INDEX(...))) usage, though I imagine there is probably a better way to handle that as well. Thanks again!
PS: Part of wrapping my head around this is has been this useful post: https://www.sumproduct.com/news/article/lambda-formulaic-recursion-its-all-about-meYour Collatz example looks intriguing I will try and make time to look. And thanks for that useful link; it is stated near the end that:
"do note that the current operand stack limit in Excel is 1,024. This should be borne in mind together with calculation times, as the current recursion limit is set as 1,024 divided by (number of lambda parameters + 1)."
The 1024 stack limit does seem to tally with Sergei's results further up. It appears that the figures all roughly divide 1024 (within a few units of rounding):
341 ~ 3 stack operands per call
256 ~ 4 stack operands per call
146 ~ 7 stack operands per call
This also suggests other parameters may need to be taken into account in addition to lambda parameters.
It's known that recursive lambda allows quite limited number of iteration. I tried to play with different variants of FIB() presented to find max allowed parameter for each. Actually that's about number of iteration, we are within max returned number limit.
In last case I just added text constant within LET().
That's not an analysis, just stats.
