Forum Discussion

Viz's avatar
Viz
Brass Contributor
Jan 05, 2021

Lambda Example: Generate Fibonacci series

In this post, I would like to explain how I have used Lambda to create a function to generate a Fibonacci series array.   This example can also be used to understand how to create an array where th...
excel
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 18, 2021

tboulden 

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.

tboulden's avatar
tboulden
Iron Contributor
Jan 19, 2021
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-me
  • lori_m's avatar
    lori_m
    Iron Contributor
    Jan 19, 2021

    tboulden 

    Your 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.

Resources