SOLVED

Lambda Functions - Limit of Iterations

Regular Visitor

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;
  2. value_to_add = the value to add;
  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:

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:

9dca3f1a-018e-4e12-8522-a2bd784fd837.png

 

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.

3 Replies

@xThomasM 

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.

best response confirmed by xThomasM (Regular Visitor)
Solution

@xThomasM 

There are some answers on

Lambda Example: Generate Fibonacci series - Microsoft Tech Community

which in turn references

https://www.sumproduct.com/news/article/lambda-formulaic-recursion-its-all-about-me

 

Basically it is to do with the limit of 1024 arguments on the stack.  Since each recursion generates a new set of arguments, this builds rapidly.

The basic calculation is

= QUOTIENT( 1024, N)

 

I think nesting the recursive Lambdas provides the product of the recursions available from each level but the number of arguments is the sum of those at the two levels.

 

My practice of using LET to break the calculation into understandable steps may well be counterproductive in terms of adding to the argument count.