BigSpill: A 92‑Function Excel LAMBDA Library for Dynamic Arrays

Patrick2788​ 

I am hugely impressed by the level of effort that must have gone into preparing this compendium of spreadsheet techniques!  I normally investigate an idea but fail to take it to the appropriate level of documentation for publication.

I have recently also worked on calculations that use convolution but presented it as deferred financial commitments rather than as a mathematical operation (I once did that using Fast Fourier Transformation).

DEFERREDλ = LAMBDA(current, spread, [initial],
    LET(
        _initialϑ, IF(ISOMITTED(initial), THUNK(HSTACK(0, 0 * spread)), THUNK(initial)),
        accountsϑ, SCAN(_initialϑ, current,
            LAMBDA(incomingϑ, expense,
                LET(
                    distributed, expense * HSTACK(0, spread),
                    committed,   HSTACK(DROP(incomingϑ(),, 1), 0) + distributed,
                    THUNK(committed)
                )
            )
        ),
        return, MAP(accountsϑ, LAMBDA(aϑ, @(aϑ()))),
        return
    )
);

Rather than the 'sensible' approach of laying out the array and the kernel othogonally to generate a 2D array, I chose to work with an array of thunks!

Hi Patrick2788​ ,  as always I enjoy seeing what you have created.  In light of the above and the comment about going to triple and quad I thought of an alternative to your Pairwise that can handle ROWS so you can just call it multiple times as needed:

PairRowsλ=
LAMBDA(
    vector_1,
    vector_2,

LET(
    // Element counts
    k,          ROWS(vector_1),
    k₂,         ROWS(vector_2),
    Size,    k * k₂,

IF(Size > 1000000,  #NUM!,

LET(
    // Cartesian expansion
    x,      CHOOSEROWS(vector_1, INT(SEQUENCE(Size,,0)/k₂)+1),
    y,      CHOOSEROWS(vector_2, MOD(SEQUENCE(Size,,0),k₂)+1),
    pairs,  HSTACK(x, y),

    pairs
    
))));

So instead of being limited to a single column to get repeated it will pair full rows.  Here is an example from your sample sheet where I added Toppings to your icecream...