8

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

Acumulator in a FOR loop

B- Factorial (using product)

Factorial

C- WHILE loop

WHILE loop

D-UNTIL loop

UNTIL loop

E- The notion of INTERNAL/EXTERNAL SCOPE

SCOPE

And now, the time of my question has arrived:

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

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

tuple of functions (still to be completed)

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?

ZygD
  • 22,092
  • 39
  • 79
  • 102
vsoler
  • 1,027
  • 2
  • 8
  • 17
  • 2
    `=REDUCE(0,SEQUENCE(5),LET(x,LAMBDA(a,b,a+b),y,LAMBDA(a,b,a*b),CHOOSE({1,2},x,y)))` works for me, I do not have HSTACK and I use `,` instead of `;` and `{1,2}` is a horizontal array for me. – Scott Craner Jul 27 '22 at 21:57
  • thank you, Scott, butit doesn't work for me. the first parameter of the REDUCE function should be {0, 1} I always get 0 (zero) as the second cell for this REDUCE formula, which is incorrect, because a factorial is never zero – vsoler Jul 27 '22 at 23:28
  • 2
    Then do two REDUCE inside a LET: `=LET(seq,SEQUENCE(5),x,REDUCE(0,seq,LAMBDA(a,b,a+b)),y,REDUCE(1,seq,LAMBDA(a,b,a*b)),CHOOSE({1,2},x,y)` – Scott Craner Jul 28 '22 at 02:48
  • it works !!! thank you very much. The solution that you propose consists of stacking horizontally, by means of a CHOOSE function, two values, which are the result of the two REDUCE functions. I was looking for a solution that consisted of stacking horizontally two functions, which would then be applied to an array of constants {0, 1} in order to yield the result, but I am arriving to the conclusion, weather it is true or not, that functions cannot be stacked. Or, at least, this is what it seems to me. – vsoler Jul 28 '22 at 05:24

1 Answers1

6

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():

enter image description here

=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:

enter image description here

Formula in A1:

=REDUCE(F1:G1,SEQUENCE(F3),LAMBDA(a,b,CHOOSE({1,2},a+b,a*b)))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 2
    Excellent answer, JvdV, and definitely the one I was looking for: thank you – vsoler Jul 28 '22 at 15:53
  • 2
    Now I can use as many functions as I need in my loop – vsoler Jul 28 '22 at 15:55
  • Just one important remark: your formula works with the CHOOSE function but doesn't seem to work with HSTACK. This puzzles me, because it is my understanding that these two functions are interchangeable with each other. Perhaps you could tell me if HSTACK, which I like best because it has one fewer parameter, works for you. – vsoler Jul 28 '22 at 16:00
  • 1
    @vsoler, I had an `HSTACK()` alternative in, but since `HSTACK()` does also take arrays it would stack way too many values. Instead I went with `CHOOSE()` which does not seem to work that way. FYI, to overcome the issue I had to use `INDEX()` but since that seemed too verbose I removed it from the answer. `=REDUCE({0,1},SEQUENCE(5),LAMBDA(a,b,HSTACK(INDEX(a,1)+b,INDEX(a,2)*b)))` – JvdV Jul 28 '22 at 17:18
  • I also like your solution with HSTACK/INDEX, I find it very practical. – vsoler Jul 28 '22 at 21:12