01-13-2021 03:29 AM - edited 01-13-2021 04:13 AM
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:
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:
___________________________________
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.
Thursday
Yeah, currently there are some serious limits on Lambda recursion. I think at the moment it has something to do with the number of Lambda parameters in use and the complexity of the Lamcda calculation.
Sadly this currently makes it difficult to use recursive Lambdas on even moderately sized ranges.
But this is early Beta ... I am sure the Excel team understands the problem.