Forum Discussion
A generalised Lambda helper function that return arrays of arrays using bisection.
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?
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.