What do you think of thunks?

Hi David

You raise some really interesting points.

"Honestly, the number of scenarios I've come across where thunks were required, or where thunks were the most efficient solution, have been few and far between"

My thoughts are that, as with any computational framework, there tend to be a spectrum of methods from methods of choice though to methods of last resort.  That is not new.  For me OFFSET tends to be a method of last resort because of its volatility, yet I like the function's syntax and the order of calculation when it appears in a formula allows it to achieve results that would otherwise be problematic.

The same might be said for thunks.  They are something of an untested curiosity, yet they provide a uniform approach to dealing with arrays of arrays (that is normal spreadsheet solutions).  The question is "where do they sit on the spectrum of methods?"  Some of the properties of thunks are not new but, rather, simply inherited from the Defined Names that have been around from 1992 or earlier.

For example, one could define a Name 'maxOccurrences' to be 

= MAX(COUNTIFS(array, array))

As it stands, it is simply the definition of a computationally intensive formula.  It is not evaluated until it is used by a grid formula.  The catch is that the formula is re-evaluated from scratch every time it is referenced.  Something similar goes for the thunked version:

= maxOccurrencesλ

where

maxOccurrencesλ = LAMBDA(MAX(COUNTIFS(array#, array#)))

The difference here is that merely being referenced by a grid formula is not sufficient to force its evaluation.  That only happens when it is provided with its parameter string

= maxOccurrencesλ()

Where I am going with this somewhat rambling discourse is that thunks and, in particular, arrays of thunks could be the method of choice for dealing with problems involving arrays of arrays with problem-specific approaches such as MAKEARRAY being the methods of last resort.

Before signing off, I ought to confess to a weakness in the argument.  At present there is no inbuilt function that will persist that calculation beyond the in-memory LAMBDA scope in which it was created, by outputting the result to the grid.  The following function does the job. but one shouldn't be dipping into a user-provided Lambda to achieve core functionality!

p.s. Some of your recommendations found their way into my GIST.  Thank you!

/*  FUNCTION NAME:  EVALTHUNKARRλ
    DESCRIPTION:    Called by modified helper functions to stack the contents of any array of thunks  */
/*  REVISIONS:      Date            Developer           Description
                    14 Oct 2024     Peter Bartholomew   Extracted for MAPλ as a separate module  
*/
EVALTHUNKARRλ = LAMBDA(thunkArrayϑ,
    LET(
        m, ROWS(thunkArrayϑ),
        n, COLUMNS(thunkArrayϑ),
        h, SEQUENCE(CEILING.MATH(LOG(n,2),1)),
        recombinedRowsϑ, IF(
            n > 1,
            BYROW(thunkArrayϑ, LAMBDA(thunkRowϑ, @REDUCE(thunkRowϑ, h, JOINPAIRSλ(1)))),
            thunkArrayϑ
        ),
        k, SEQUENCE(CEILING.MATH(LOG(m,2),1)),
        recombinedϑ, IF(
            m > 1, 
            REDUCE(recombinedRowsϑ, k, JOINPAIRSλ(0)), 
            recombinedRowsϑ
        ),
        result, IFNA((@recombinedϑ)(), ""),
        result
    )
);

/*  FUNCTION NAME:  JOINPAIRSλ
    DESCRIPTION:    Called by EVALTHUNKARRλ to stack the contents of thunks pairwise  */
/*  REVISIONS:      Date            Developer           Description
                    09 May 2024     Peter Bartholomew   Original Development  
                    16 May 2024     Peter Bartholomew   Test for unpaired thunk in binary tree
                    30 Aug 2024     Peter Bartholomew   Modify to stack horizontally or vertically
                    15 Nov 2024     David Clements      Efficiency improvements / simplification
*/

JOINPAIRSλ = LAMBDA([by_col], LAMBDA(thunkArray, [k],
    LET(
        STACKλ,    IF(by_col, HSTACK, VSTACK),
        alternate, WRAPROWS(thunkArray, 2),
        MAP(
            TAKE(alternate, , 1),
            DROP(alternate, , 1),
            LAMBDA(ϑ₁, ϑ₂,
                LET(
                    x₁, ϑ₁(), 
                    x₂, ϑ₂(), 
                    v,  IF(TYPE(ϑ₂)=16, x₁, STACKλ(x₁, x₂)), 
                    LAMBDA(v)
                )
            )
        )
    )
));

A version of Excel MAP helper function that will return an array of arrays