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

Question

What is BigSpill?BigSpill is an Excel LAMBDA library containing 92 functions across 10 categories, built from extensive experimentation with dynamic arrays. The goal is to provide elegant, efficient tools that make sheet‑level formulas easier to read, more modular, and more dynamic.&nbsp;The library includes a mix of:quality‑of‑life helpersessential primitivesmid‑level operatorsdeveloper‑level toolsa few functions you might not expect to see in ExcelWho is it for?Everyone.BigSpill aims to make complex operations more approachable and expressive.A few examples from the library:PairwiseλStaircaseλGrainλPolarGridλConvolveλFoldλTessellateλRevealλKnapsackλMagnifyλTraverseλThe repository includes full documentation and 11 sample workbooks so you can explore the functions without setup.If you’re interested in experimenting with dynamic arrays, or LAMBDA workflows, feel free to take a look. Feedback and suggestions are always welcome.I've attached 1 onboarding workbook. There are 10 more at the link below.GitHub:BigSpill&nbsp;

peterbartholomew1 · Answer

Patrick2788​&nbsp;I am hugely impressed by the level of effort that must have gone into preparing this compendium of spreadsheet techniques!&nbsp; 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!

harun24hr · Answer

Visitors would be be benefited if you attached a sample file with sample data and output of each function. Little explanation would make the file excellent for visitors. Can you please share sample file and use of functions?

Edit: I got the sample files to your GitHub repository. Still I will advise to attach a sample file the post for easy way.

patrick2788 · Answer

Thank you for taking a look. I didn't attach sample workbooks here because the forum tends to gobble up attachments!

patrick2788 · Answer

Pairwiseλ is my approach to generating a Cartesian product of 2 sets. Here is a demo with a deck of cards created from rank and suit.&nbsp;The function is written as simply as possible. The goal was to make it fast, efficient, and predictable./* [GR06] -------------------------------------------------------------------------
Function:     Pairwiseλ
Category:     Grid Analytics (Categorical Analysis)
Author:       Patrick H.
Version:      1.1
Dependencies: Echoλ, Resizeλ

Description:
    Creates vertical pairs from 1D and scalar inputs. Inputs are flattened with
    TOCOL to normalize orientation. 2D arrays are strictly prohibited.

Parameters:
    vector_1    - 1D vector or scalar.
    vector_2    - 1D vector or scalar.
------------------------------------------------------------------------------*/

Pairwiseλ=
LAMBDA(
    vector_1,
    vector_2,

LET(
    // Flatten inputs
    v,          TOCOL(vector_1),
    v₂,         TOCOL(vector_2),

// Element counts
    k,          COUNTA(v),
    k₂,         COUNTA(v₂),

// Halting scenarios
    Is2DInput?, OR(AND(ROWS(vector_1)&gt;1, COLUMNS(vector_1)&gt;1),
                   AND(ROWS(vector_2)&gt;1, COLUMNS(vector_2)&gt;1)),

Is2Big?,    k * k₂ &gt; 1000000,

IF(Is2DInput?,  #VALUE!,

IF(Is2Big?,     #NUM!,

LET(
    // Cartesian expansion
    x,      Echoλ(v, k₂),
    y,      Resizeλ(v₂, k),
    pairs,  HSTACK(x, y),

pairs
    
)))));&nbsp;I have two more functions in the pipeline for the next update:Triplewiseλ - Cartesian product of 3 setsQuadwiseλ - Cartesian product of 4 sets&nbsp;These two are of note because they provide an elegant way to generate indices. I'm toying with Windowedλ which extracts overlapping windows (n x n) from a 2D grid in row-major/col-major order. All will be in the next update.

m_tarler · Answer

Hi Patrick2788​ ,&nbsp; as always I enjoy seeing what you have created.&nbsp; 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 &gt; 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.&nbsp; Here is an example from your sample sheet where I added Toppings to your icecream...&nbsp;

Forum Discussion

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

10 Replies