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

Hi David

I was aware that for the specific challenge I could avoid thunks altogether but my focus was more to answer the question "can thunks provide a unified approach to dealing with a number of array problems as one moves from scalar, to array, to array of arrays, ... ?"  Ultimately, it could be a case of "why restrict yourself to one financial model?" when you could map them to a changing set of assumptions and create an array of models, rather like data tables on steroids!

Along the way, it would be nice if a single technique were to provide an efficient way of addressing headaches such as handling arrays of ranges, arrays of arrays, ragged arrays, nested arrays, cartesian products of 2D arrays.  The solution may be something other than thunks, but at the moment they are looking promising and it is certainly better than the dreaded #CALC! error that tells you that the solution to your problem is not handled by Excel.

The think that really caught my attention was your idea of applying an array of functions to each data set as a single operation.  Although I would have said the idea "could well be possible", that is not the same thing as adjusting one's thinking sufficiently to exploit the concept with any degree of confidence! 

Well done.