Forum Discussion

djclements's avatar
djclements
Bronze Contributor
Jan 20, 2025

Good day Peter,

As always, I appreciate the demonstration and hope others do too. Personally, I find thunks to be quite useful for highly complex scenarios, as they can be an elegant way of handling nested arrays; however, it's not necessarily the first method I would reach for in any given situation. 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, but that hasn't stopped me from experimenting with them in the least. ;)

For this particular challenge, there's a couple of methods that come to mind. For starters, MAP with OFFSET would work, although it seems like a bit of a cheat considering the table data range begins on row 3, leaving exactly 2 rows above to spare (if there weren't enough rows above the data range to complete the task, this wouldn't work):

=FILTER(Data,MAP(dataValues,OFFSET(dataValues,-2,0),OFFSET(dataValues,2,0),LAMBDA(v,b,a,MAX(b:a)=v)))

Another option could be BYROW with INDEX and a SEQUENCE matrix:

=FILTER(Data,BYROW(INDEX(VSTACK(0,0,dataValues,0,0),SEQUENCE(ROWS(Data),,0)+SEQUENCE(,5)),MAX)=dataValues)

On the topic of thunks, though, you may find this interesting... when it comes to returning the results for multiple functions in a single array (e.g. ROWS, ISREF, MAX, etc.), you can do so with a single MAP function:

=LET(
    arrϑ, ROLLINGRANGEλ(dataValues, 5),
    func, HSTACK(ISREF,ROWS,AVERAGE,MAX),
    MAP(IFNA(func,arrϑ),IFNA(arrϑ,func),LAMBDA(fn,ϑ,fn(ϑ())))
)

Plus, the extra column on the end in your sample file:

=LET(
    arrϑ, ROLLINGRANGEλ(dataValues, 5),
    func, HSTACK(ISREF,ROWS,AVERAGE,MAX),
    calc, MAP(IFNA(func,arrϑ),IFNA(arrϑ,func),LAMBDA(fn,ϑ,fn(ϑ()))),
    HSTACK(calc,IF(TAKE(calc,,-1)=dataValues,dataValues,""))
)

Fun, fun, fun! Have a good one!

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 20, 2025

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.

 

Resources