Forum Discussion
A generalised Lambda helper function that return arrays of arrays using bisection.
I think this does it! I have removed the array of thunks from name manager and passed it as a parameter.
I have added an example that arose in a LinkedIn discussion from Marco Filocamo
as well as your example that called for a similar text split.
The 'major' change for the user is to append the Greek letter λ to BYROW so that the formula picks up my function rather than the in-built helper function.
PeterBartholomew1 Impressive work! I've been reading your posts/discussions regarding Thunks, but haven't taken the time to dive in and try to understand them yet. Seems to be full of potential...
I was hoping this method might somehow bypass the limits placed on lambda recursion, but alas, it seems to only work well with a few hundred rows of data or less. I had experimented with recursive lambdas a few months ago and came up with a custom STACKBYROW function that achieved similar results; however, it too was only useful with smaller data sets:
STACKBYROW:
=LAMBDA(array,function,[initial_value],[start],[pad_with],
LET(
n, MAX(start, 1),
f, function(INDEX(array, n, 0)),
v, IF(ISOMITTED(initial_value), f, IFNA(VSTACK(initial_value, f), pad_with)),
IF(n<ROWS(array), STACKBYROW(array, function, v, n+1, pad_with), v)
)
)
When I finally get some time to take look at Thunks, I'll definitely be using this as a basis for understanding how they work. Thanks!
If you are hitting recursion limits, something has been lost along the way. My first experiments with bisection (I dare say pretty standard within professional IT algorithm development but a struggle for me) predated the release of lambda helper functions and were specifically an attempt to get around the recursion limit on total argument count by reducing the depth of any recursion from N to LOG₂(N).
I resurrected the approach to deal with the REDUCE/VSTACK performance limitations that was itself a workaround for the fact that Excel treats arrays of arrays as an error rather than celebrating it as the correct solution to many, if not most, problems. Whilst bisection required twice as many VSTACK operations, most were small and fast rather than repeatedly stacking large arrays.