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:
- value = the initial (current) value;
- value_to_add = the value to add;
- times_to_add = the number of times to add the previous number;
- iteration = the current iteration of the function.
To achieve this initial recursive function I had to create two lambda functions:
1. One that simply adds a value to another - adder:
=LAMBDA(
value,
value_to_add,
value + value_to_add
);
2. And other to take care of the recursive side of things - recur_adder:
= LAMBDA(
value,
value_to_add,
times_to_add,
iteration,
IF(iteration<=times_to_add, recur_adder(adder(value, value_to_add), value_to_add, times_to_add, iteration+1), value)
);
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,
value_to_add,
times_to_add,
iteration_limit,
IF(chunk <= ROUNDDOWN(times_to_add/iteration_limit, 0), bypass_adder(chunk+1, recur_adder(value, value_to_add, iteration_limit, 1), value_to_add, times_to_add, iteration_limit), IF(chunk = (ROUNDDOWN(times_to_add/iteration_limit, 0) + 1), bypass_adder(chunk+1, recur_adder(value, value_to_add, times_to_add-ROUNDDOWN(times_to_add/iteration_limit, 0) *iteration_limit, 1), value, times_to_add, iteration_limit), value))
);
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.
Accepted Solutions