Forum Discussion
A generalised Lambda helper function that return arrays of arrays using bisection.
I may well be running round in circles
I can only imagine what you brain must feel like in your skull...
I have written a BYCOL version that works will minimal modifications
Can you please share that too?
I would argue that if you successfully covered BYROW and BYCOL, you have covered 70%-80% of the array-of-arrays cases (at least in my limited experience) and made a major contribution to our mental health and welfare.
Is the workbook unstable on your computer? I think I will separate the SCAN, with the thunks used to handle the 2 column array used to collect the Fibonacci series, to another workbook since that is where the trouble started.
I have just submitted 'My Feedback' to Microsoft. It is not a system I am familiar with, so we will see what happens!
Not sure what exactly you mean by "unstable", but I'm having enough occasional hiccups to require me to exit Excel and restart. Very frustrating..
I have completed the alternative approach to the problem of expanding a column of thunks to give a 2D array. As before, the task of generating the column of thunks uses a BYROW wrapper that first reduces the column of row arrays to a column of thunks and then uses a binary tree to expand and stack the results.
I do have concerns relating to the possible efficiency of the process but first one needs to obtain answers and only then does efficiency become relevant!
The same challenge but a somewhat different solution. Instead of selecting thunks to combine using INDEX with arguments generated by the binary tree I used array shaping functions WRAPROWS and TAKE to pair up thunks for combining. It still uses a binary tree but REDUCE is used to work through the levels until the entire solution is held as a single array.
This is the main lambda function BYROWλ (made a bit more verbose by the change control block and runtime documentation)
/* FUNCTION NAME: BYROWλ DESCRIPTION: Implements a version of BYROW that will return an array of arrays */ /* REVISIONS: Date Developer Description 09 May 2024 Peter Bartholomew Original Developemnt */ BYROWλ = LAMBDA( // Parameter Declarations array,fnλ, // Procedure LET(help, TRIM(TEXTSPLIT( "DESCRIPTION: →Implements a version of BYROW that will return an array of arrays.¶" & "VERSION: →09 May 2004 ¶" & "PARAMETERS: →¶" & " array →(Required) A two dimensional array of values. ¶" & " fnλ →(Required) A lambda function that accepts a row array as input.", "→", "¶" ) ), // Check inputs array, IF(OR(ISOMITTED(array), array=""), #Value!, array), fnλ, IF(OR(ISOMITTED(fnλ), TYPE(fnλ)<>128), #Value!, fnλ), // Procedure n, ROWS(array), // Implements BYROW returning thunked results at each step thunkArrayϑ, BYROW(array, LAMBDA(row, THUNK(fnλ(row)))), // Recombine pairs of thunks as a binary tree until the root node is reached k, SEQUENCE(LEN(BASE(n - 1, 2))), recombinedϑ, @REDUCE(thunkArrayϑ, k, JOINPAIRSλ), result, TAKE(recombinedϑ(), n), // Handle Error error, MAX(IsError(result)+1), // Return result CHOOSE(error, result, Help) ) );It calls a further function to perform the pairwise stacking of the contents of thunks.
/* FUNCTION NAME: JOINPAIRSλ DESCRIPTION: Called by BYROW to stack the contents of thunks pairwise */ /* REVISIONS: Date Developer Description 09 May 2024 Peter Bartholomew Original Developemnt */ JOINPAIRSλ = LAMBDA(thunkArray, [k], LET( alternate, WRAPROWS(thunkArray, 2, thunk("")), firstpart, TAKE(alternate, , 1), finalpart, TAKE(alternate, , -1), MAP( firstpart, finalpart, LAMBDA(ϑ₁, ϑ₂, LAMBDA(VSTACK((@ϑ₁)(), (@ϑ₂)()))) ) ) );The tricky part was ensuring that the single thunks did not return type 64 (array) but were coerced to type 128 (lambda function).
I have tested the functions up to 8192 rows and, although the performance wasn't sparkling, it only took a few seconds.
Maybe I am lucky then; I haven't needed restarts. In the present case what I hit was a situation in which the SCAN (as applied to generating the Fibonacci series) would show a value error. Recommit the formula and it would show the correct results. But simultaneously one of the other formulas would show an error. Recommit that and, with a bit of luck, both would show as correct.
I have also been experimenting with an alternative approach to expanding an array of thunks. Since many, if not all, nested array problems can be expressed as an array of thunks, that could be regarded as a very general problem arising in many contexts. I was, and still am, rather proud of the binary pointer approach that built the binary tree recursion but the new idea is to use WRAPROWS to place the thunks into pairs and then combine each pair into a single thunk holding two stacked arrays. The process is repeated using REDUCE until there is only one thunk holding the entire solution. Add the null parameter string and there you are, home and dry.
I am still to decide on the best approach for dealing with an odd number of thunks in the array to make sure the #N/A in the final pair does not propagate as an error through the calculation.
Then I will need to test for performance. It is one thing to get some code working for arrays of length 10 and quite another to be sure everything still works when you reach 10,000 (never mind a million)!