What do you think of thunks?

Introducing new parameters to a problem often means considering a new approach. For example:

=FILTER(Data, MAXIFS(Data[Value], Data[Date], ">=" & Data[Date] - 2, Data[Date], "<=" & Data[Date] + 2) = Data[Value])

However, I do respect PeterBartholomew1 for his quest to find a generalized approach for any given situation. My answer to the question, "What do you think of thunks?", remains the same: I think they're great for some situations and overkill for others. I would highly encourage anyone who's taken an interest in this sort of thing to explore the concepts that Peter has shared, but to also explore alternative methods as well. The more you know, the better your chances will become for finding an appropriate solution to any problem. ;)

I have my prejudices, but I would expect my approach to become stronger.  If, say, XLOOKUP were used to identify first and last cells in each range, then the range ":" operator would return a variable length range for each mapped value.  I could put the search criterial within a Lambda function to modularise the problem and then return the ranges within thunks (ranges, especially of varying size would throw an error within MAP).

I can then perform the calculations on the individual time slices of the data table with a second application of MAP (or, better, a custom version of MAP that expands the thunks automatically before passing the range to the function specified by the formula

MAPϑ
= LAMBDA(ϑ, FNλ, 
    MAP(ϑ, LAMBDA(ϑ, FNλ(ϑ())))
);

The strategy would be to break the calculation into a hierarchy of Lambda functions, each one with a well defined task.  In essence, I propose to use thunks as a 'pass by reference' strategy, as opposed to 'pass by value'.

I would think Daniel's approach could be easily adapted to that:

=FILTER(C3:D25,MAP(C3:C25,LAMBDA(x,AND(XLOOKUP(x,C3:C25,D3:D25)>N(FILTER(D3:D25,(x-2<C3:C25)*(x+2>C3:C25),0)))))))

Hi Matt

The formula works fine.  It is something of a curiosity, given that during my career I probably wrote more matrix formulas than I did natural language paragraphs, that I don't use matrix multiplication more now.  It is an extremely fast function even working well with1000x1000 arrays where it still kept up with a 1000 step SCAN.  Mind you, explaining matrix multiplication to a lay audience can almost be as bad as explaining a thunk!

Rather perversely, I used a data dictionary comprising thunks to scroll though the variables of your LET function and examine the matrices.

=LET(
    in_vals,       dataValues,
    in_roll_width, I1,
    expanded_vals, VSTACK(
        EXPAND(0, in_roll_width, , 0),
        in_vals,
        EXPAND(0, in_roll_width, , 0)
    ),
    _count,   SEQUENCE(ROWS(expanded_vals)),
    _greater, expanded_vals > TRANSPOSE(expanded_vals),
    _diag,    ABS(_count - TRANSPOSE(_count)) <= in_roll_width,
    max_locations, --(
        DROP(
            DROP(
                MMULT(_greater * _diag, SEQUENCE(ROWS(expanded_vals), , 1, 0)),
                in_roll_width
            ),
            -in_roll_width
        ) = 2 * in_roll_width
    ),
    out, FILTER(Data, max_locations),
    dict, DB.DICTIONARYλ(
        "expanded_vals", expanded_vals,
        "count",        _count,
        "greater",      _greater,
        "diag",         _diag,
        "maxLocations",  max_locations,
        "out",           out
    ),
    DB.GETVλ(dict)(idx)
)

The 'dict' object can be returned as the result of the LET, or here I used a spinner to do the scrolling.

I have been trying to build generic capability but it is only too easy to miss the specific.  Surveying the horizon is one thing, but it is not of much value if I trip over my shoelaces!

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.

I have seen your posts on the use of sql for calculations far from the simple extraction, filtering and joining of text from tables that I associate with scope for sql.  What is not obvious to me is where the scripts are loaded, in particular within which app?

select Date,Value from (SELECT rowid old_rowid,*, max(`Value`) OVER (

  ORDER BY rowid ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING

) AS group_max FROM RollingMax) where group_max=Value order by old_rowid;

2 lines merge to one row