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!