Forum Discussion
BigSpill: A 92‑Function Excel LAMBDA Library for Dynamic Arrays
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.
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)>1, COLUMNS(vector_1)>1),
AND(ROWS(vector_2)>1, COLUMNS(vector_2)>1)),
Is2Big?, k * k₂ > 1000000,
IF(Is2DInput?, #VALUE!,
IF(Is2Big?, #NUM!,
LET(
// Cartesian expansion
x, Echoλ(v, k₂),
y, Resizeλ(v₂, k),
pairs, HSTACK(x, y),
pairs
)))));
I have two more functions in the pipeline for the next update:
Triplewiseλ - Cartesian product of 3 sets
Quadwiseλ - Cartesian product of 4 sets
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.
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!
I appreciate the kind words! My goal with BigSpill is to get the functions out there and get the word out that Excel is a highly capable programming language.
I've downloaded your workbook and have been studying your approach to convolution with thunks. This is certainly a different mental model than the way I think through a problem. The elegance is impressive. It reminds me of your matrix flow accumulation solution - hydro flow. I may have more to say after I've had some time to digest the module.
I've been working on polishing Triplewiseλ/Quadwiseλ. The latter of which helps with making extraction of overlapped windows easier. This is a snip using djclements 's workbook from the Braille challenge.
The thought process I am aiming at is to programme with high-level entities such as data dictionaries or tables with array fields and work towards a solution top to bottom rather than building up from single cells and index arithmetic (addressing single elements of arrays). David's Braille challenge was interesting and I rather regretted using a version of Excel that I couldn't publish. The OP deserved more discussion. djclements
= BRAILLE.READλ(phrase) = BRAILLE.WRITEλ(words)The formulas don't give that much away 😃😃
I'm guessing you didn't solve it with convolution as there's at least one overlap in the windowing that would cause a false return. Unless you noticed this and worked around it?
