Forum Discussion

ecovonrein's avatar
ecovonrein
Iron Contributor
Jun 30, 2022

HRECURSE instead of MAKEARRAY, recursing LAMBDA

I wish to share with the community some seriously goofy but generic LAMBDA code I created yesterday.   By way of introduction, once you go down the route of SPILLed inputs, you very quickly arrive ...
excel
ecovonrein's avatar
ecovonrein
Iron Contributor
Jul 09, 2022

I just realized that my LAMBDA(a;i;FILTER(a;SEQUENCE(ROWS(a))=i)) reinvents CHOOSEROWS(a;i).  Using that new Excel function, the final HRECURSE reads

 

=LAMBDA(i;n;Prev;fn;LET(This;fn(i;LAMBDA(r;CHOOSEROWS(Prev;r)));IF(i<n;HSTACK(This;HRECURSE(i+1;n;This;fn));This)))

Christopher Graham's avatar
Christopher Graham
Copper Contributor
Aug 17, 2023

ecovonrein Great code! I've used it in VRECURSE form to stack results of an iterative function that wouldn't spill otherwise.

 

Is there a way to update it to refer back to a specific row/column index of the previous step? I need more than just single row/column operations. I'd like to be able to specify INDEX(p, r, c) in the calculation.

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Aug 17, 2023

    Thanks! However, since I wrote this great piece of code, I learned that there is one function in the Excel toolkit that solves the problem without the need for recursion. Now, brace yourself. The great jesters in Seattle have dubbed this function, which allows us to augment things, REDUCE.

    So, if you write:
    =REDUCE(0;SEQUENCE(10);LAMBDA(prev;this;VSTACK(prev;HSTACK(this;INDEX(prev;this-1;1)))))
    then you get what you want. You are successively augmenting "prev" by forever VSTACKing it (I generate 2 columns with this formula), and you have access to all of "prev", which is what you asked for. That is, you don't need to use V/HRECURSE 😉

     

    PS:  Credit for the discovery that REDUCE can be abused in this way goes to lori_m.

    • Christopher Graham's avatar
      Christopher Graham
      Copper Contributor
      Aug 18, 2023

      ecovonrein Thanks. I'm struggling a bit with the REDUCE code on a few fronts: 1) where my function is applied; and 2) where the original array comes into play. 

       

      Is there a post that discusses this approach in more detail (search was unhelpful)?

      • ecovonrein's avatar
        ecovonrein
        Iron Contributor
        Aug 19, 2023

        Sorry, did not have much time yesterday. Here another annotated stab:

        =LET(
	' Initializing Row 0 - the start-up row - however you need it. I only do 2 colums here.
	R0; {1\2};

	' This the equivalent to VRECURSE, iterating 10 times
	REDUCE(R0;SEQUENCE(10); 

		' This is your Lambda.  It receives the evolving results as "tbl" and the heartbeat "i"
		LAMBDA(tbl;i; LET(

			' Since R0 is in tbl(1), tbl(i) is actually row i-1.  But you can access any previous row.
			Ri_1; INDEX(tbl;i;0);

			' Do your thing.  It will be more meaningful than my thing and
			' probably involve HSTACK(c1,c2,...,cn) :)
			Ri; Ri_1+1;

			' Append your result row "Ri" to the "tbl"
			VSTACK(tbl;Ri)
		))
	)
)

Resources