Excel's LAMBDA with a "kind of" composite function

Question

Ever since I learnt that Excel is now Turing-complete, I understood that I can now "program" Excel using exclusively formulas, therefore excluding any use of VBA whatsoever.

I do not know if my conclusion is right or wrong. In reality, I do not mind.

However, to my satisfaction, I have been able to "program" the two most basic structures of program flow inside formulas: 1- branching the control flow (using an IF function has no secrets in excel) and 2- loops (FOR, WHILE, UNTIL loops).

Let me explain a little more in detail my findings. (Remark: because I am using a Spanish version of Excel 365, the field separator in formulas is the semicolon (";") instead of the comma (",").

A- Acumulator in a FOR loop

B- Factorial (using product)

C- WHILE loop

D-UNTIL loop

E- The notion of INTERNAL/EXTERNAL SCOPE

And now, the time of my question has arrived:

I want to use a formula that is really an array of formulas

I want to use an accumulator for the first number in the "tuple" whereas I want a factorial for the second number in the tuple. And all this using a single excel formula. I think I am not very far away from succeeding.

The REDUCE function accepts a LET function that contains 2 LAMBDAS instead of a single LAMBDA function. Until here, everything is perfect. However, the LET function seems to return only a "single" function instead of a tuple of functions

I can return (in the picture) function "x" or function "y" but not the tuple (x,y).

I have tried to use HSTACK(x,y), but it does not seem to work.

I am aware that this is a complex question, but I've done my best to make myself understood.

Can anybody give me any clues as to how I could solve my problem?

Answer 1

Very nice question.

I noticed that in your attempts you have given REDUCE() a single constant value in the 1st parameter. Funny enough, the documentation nowhere states you can't give values in array-format. Hence you could use the 1st parameter to give all the constants in (your case; horizontal) array-format, and while you loop through the array of the 2nd parameter you can apply the different types of logic using CHOOSE():

=REDUCE({0,1},SEQUENCE(5),LAMBDA(a,b,CHOOSE({1,2},a+b,a*b))) 

This way you have a single REDUCE() function which internal processes will update the given constants from the 1st parameter in array-form. You can now start stacking multiple functions horizontally and input an array of constants, for example:

=REDUCE({0,1,100},SEQUENCE(5),LAMBDA(a,b,CHOOSE({1,2,3},a+b,a*b,a/b)))

I suppose you'd have to use {0\1} and {1\2} like I'd have to in my Dutch version of Excel.

---

Given your accumulator:

Formula in A1:

=REDUCE(F1:G1,SEQUENCE(F3),LAMBDA(a,b,CHOOSE({1,2},a+b,a*b)))