Using Scan function with Oddly shaped arrays

data_junky  More on creating jagged arrays.

A thunk is no more than a function that doesn't require any input parameters.  Like any other function, it contains nothing more than a formula.  In that, it is similar to a defined name which also refers to a formula as text.  Whereas the defined name is evaluated whenever it is referenced within a cell, a function requires a parameter string to trigger its evaluation.  Since a thunk does not require parameters, it will evaluate to give the same result every time it is used.  The formula may required substantial processing effort, in which case one wouldn't wish to call it too often, or it could be as simple a task of returning a pre-evaluated LET variable, in which case the function provides no more than a reference to a region of memory.

Thunks are useful because they allow the programmer to manipulate data structures that are beyond the scope of the Excel calculation engine.  These include data structures such as an array of ranges, or arrays of arrays where the latter might include ragged right arrays or arrays comprising a mix or row and column arrays.

To form a thunk, one performs the required calculation and then encloses the calculated result within a further LAMBDA.  Whilst the array of arrays is treated as an error, the equivalent array of thunks is valid and can be further processed by Excel formulas.  The catch is that, once one has the solution as an array of thunks, Excel will be capable of evaluating any element of the array, but error when asked to evaluate the result as a whole.  There are a number of ways of circumventing the problem.

1. MAKEARRAY.  This will return a 2D array from a column of thunks containing rows (say).  Each row is returned by index multiple times and a single term is returned until the entire array is built up.
2. REDUCE/VSTACK. This will work row by row, at each step stacking the newly returned thunk at the end of the array built so far.  Each of the increasing sized arrays exists within memory until the calculation is complete and the evaluation returns from the recursive process.  Memory management issues can make the evaluation very slow.
3. EVALTHUNKARRλ.  This is a module I have included within the attached workbook.  It takes a column of thunks and uses WRAPROWS to form a two column array of thunks.  MAP allows each pair to be processed in isolation.  The thunk pairs are evaluated, stacked and converted back into a new thunk.  One now as a new array of thunks, but it comprises only half the number of terms.  Eventually the entire problem is reduced to a single thunk that contains the solution to the problem.  At worst the process requires 20 such steps.  That may require significant computational effort, but is not as bad as REDUCE/VSTACK which may require 100 000s such steps.

The final formula uses MAPλ.  The calculation is the same as 3. (above) but the process of forming and stacking thunks is hidden from the user.  This helper function does some pre-processing of the user-specified function before passing it in modified form to the in-build MAP helper function.  Each time the modified function is applied, it first applies the original user-function and then converts the result to a thunk.  Once MAPλ has run through the recursion it will have generated an array of thunks.  As before, EVALTHUNKARRλ will evaluate each thunk within the array and stack the results appropriately.

Developing this little lot was itself an iterative process that involved stops for the fried braincells to recuperate, but the aim was always to hide the complexity from sight within helper functions that can be used without further examination .  The one thing that JoeMcDaid could do to help is to create a native version of EVALTHUNKARRλ (or perhaps it would be better named just STACK) that does not rely upon recursive helper functions but instead simply iterates its way through the stacking process from one to the array size.

Note: in the extreme, result arrays might be far more than simple 2D heterogeneous arrays.  It should be possible to create an array of complete financial or engineering models, comprising headers, white space and multiple blocks of data.  All that is required is that the result fits on one sheet since dynamic arrays do not spill from sheet to sheet.  There may be other resource problems that prevent such solutions but I have no way of telling (so far, so good)!

= LET(
    k, SEQUENCE(n),
    triangle, MAP(k, LAMBDA(a, THUNK(EXPAND(0,,a,0)))),
    EVALTHUNKARRλ(triangle)
  )

= MAPλ(SEQUENCE(n), LAMBDA(a, EXPAND(0,,a,0)))