Frequent Visitor

# Lambda Functions - Limit of Iterations

Greetings,

I'm experimenting with the powerful Lambda Functions on Excel and stumbled into something I'm not quite understanding. Is there a limit of iterations to recursive lambda functions? How is it determined? I'll try to explain:

I created a simple recursive lambda function that takes 4 arguments:

1. value             = the initial (current) value;
3. times_to_add = the number of times to add the previous number;
4. iteration         = the current iteration of the function.

To achieve this initial recursive function I had to create two lambda functions:

``````=LAMBDA(
value,
);``````

2. And other to take care of the recursive side of things - recur_adder:

``````= LAMBDA(
value,
iteration,
);``````

The formula works as intended, but only if the number of iterations is less than 203. I initially thought that it might had something to do with the maximum number of iterations of Excel, so I changed it to a 1.000, but it didn't made a difference.

___________________________________

Thinking that I was going to be able to outsmart Excel (as usual), I decided to create a third recursive lambda function, one that would work around the 203 limit I was facing, breaking the calculations of the recur_adder function in "chunks" of 203 iterations. For example, if the input was 1.000 iterations and the limit of iterations is at 203, this new function would call the recur_adder 5 times, with 203 iterations the first 4 times, and with 188 iterations the last time.

I gave it a try by creating this lambda function - bypass_adder:

``````= LAMBDA(
chunk,
value,
iteration_limit,
);``````

This formula also worked as intended, but the new bypass_adder function was only able to perform 2 chunks of iterations, and the limit for the iterations on recur_adder went down to 201. To sum up:

• Using only the recur_adder I was able to iterate 203 times;
• Using the bypass_adder I was able to iterate 401 times - 2 chunk iterations on bypass_adder > one chunk of 201 iterations + another chunk of 200 iterations on recur_adder;
• Strangely enough, I found other combinations of times_to_add x iteration_limit on the bypass_adder that also work, but stop working if I increase times_to_add by just 1. Those other combinations yeild even more iterations, so I imagine that there's no limit of iterations per se, but some kind of time limit of execution or something;

___________________________________

Upon investigating the variables times_to_add and iteration_limit in the bypass_adder function I found a weirdly correlated quadratic behaviour between them:

By using 100 as the limit for iterations on the bypass_adder it was possible to make 8.599 iterations on the adder function, extending a lot from the 203 iterations I was getting only with one recursive function (recur_adder). I imagine that chaining multiple functions similar to the bypass_adder would extend even further, but I guess that this is getting too convoluted.

The question remains: what determines the limit of iterations? I have some ideas for using recursive lambda functions (a genetic algorithm for optimizations, for instance), and being able to extend this limit is important.