Forum Discussion
A generalised Lambda helper function that return arrays of arrays using bisection.
Specifically this required days of the week to be sorted by multiple price criteria.
[This is intended as a topic for discussion as opposed to a specific request for help]
I used the problem to develop a Lambda function that uses a binary tree to select the rows at the leaf nodes and perform a straightforward sort. The results then get stacked pairwise until the blue table of results is returned
BYROWλ
"Applies a user-defined function by row and collects array results into a stacked array"
=LET(resultϑ, BYROW(array, ThunkFnλ), BinaryTreeλ(ROWS(resultϑ), "", Derefϑarrλ))
The main bisection recursion is hidden from the user
BinaryTreeλ
"Generates binary numbers stacking blocks pairwise to an upper limit"
=LET(
maxNode, BASE(nodeCount - 1, 2),
maxLevel, LEN(maxNode),
level, LEN(parentNode),
maxChild, LEFT(maxNode, 1 + level),
IF(
level < maxLevel - 1,
LET(
childNode0, BinaryTreeλ(nodeCount, parentNode & 0, FNλ),
childNode1, BinaryTreeλ(nodeCount, parentNode & 1, FNλ),
IF((parentNode & 0) = maxChild, childNode0, VSTACK(childNode0, childNode1))
),
IF(
(parentNode & 0) = maxChild,
FNλ(parentNode & 0),
VSTACK(FNλ(parentNode & 0), FNλ(parentNode & 1))
)
)
)
but it is intended to non-problem specific and hence reusable.
A key feature is that the user provides a Lambda function that performs the required calculation as if BYROW would return the result, but the function is converted to one the returns a thunk and hence avoids the array of arrays problem.
ThunkFnλ
"Accepts a user-defined Lambda function that returns an array result and generates a related function that returns the corresponding thunk"
=LAMBDA(arr, LAMBDA(userFnλ(arr)))
Finally
Derefϑarrλ
"Dereferences a term from a thunk array using a zero-based binary pointer and expands the resulting scalar thunk"
=INDEX(resultϑ, DECIMAL(ptr, 2) + 1, 1)()
converts the binary node pointer to a decimal index.
This is all crazily heavy but, hopefully, would be simple to reuse for other problems since the main processing is problem independent.
21 Replies
I must be dense (or my brain may have been scrambled by trying to wrap itself around the concept of thunks for so long) but let ask a simple question. You say:
"it is intended to non-problem specific and hence reusable. ...the user provides a Lambda function that performs the required calculation as if BYROW would return the result.."So let's say I have range of three one-column rows each containing a first and last name, comma separated ("Elvis,Presley", for example).
Trying=BYROW(range,LAMBDA(row,TEXTSPLIT(row,",")))results in your classic #CALC! and the admonition that "nested arrays are not supported".
What do I need to modify in your attached workbook so as to get the desired output (a 2X3 array)?
As you mention, this is not a specific request for help. In an ideal world (short of Excel resolving the array-of-arrays issue natively), I would have a black box into which I would feed the relevant array, the attack approach (by row, by column), the function to apply (TEXTSPLIT(), in this example) and the argument(s) required by that specific function (a delimiter, in this example) - and have the box spit out the correct outcome; something like:
=blackbox(range,byrow,textsplit,",")One can only hope, right?
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!
You might well ask! As things stand I believe there is an error in the calculation that means it only works correctly when applied to the original test data. What was intended was that the user would supply a new Lambda function that accepts one row of their data and produces the result they require of it, be it a scalar (which works with the inbuilt helper function BYROW), a row array or, even, a 2D array.
It seems, however that, while I was deriving the algorithm for building the binary tree and 'pruning' branches that would have allowed the indices go beyond the array size and led to #REF! errors , I had introduced a global variable 'resultϑ' to provide the thunk array as test input. Although I now calculate the thunk array within the formula, I suspect that variable is going out of scope during the recursion and the formula is defaulting to the workbook-scoped version and producing a hybrid mess of results.
Back to the drawing board! I will let you know what happens.
