Skip to content

Instantly share code, notes, and snippets.

@pbartxl
Created February 26, 2022 23:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save pbartxl/04584dd9a9e8fe9c61a10511c78f390c to your computer and use it in GitHub Desktop.
Save pbartxl/04584dd9a9e8fe9c61a10511c78f390c to your computer and use it in GitHub Desktop.
/*
The ArrayRef function is the starting point for reading data from an array of cross-tab arrays into Excel
as a term of a multidimensional array.
A is a range anchored on the first element of the array and covering all elements of the array,
nᵣ, dᵣ, and sᵣ are references to steering date that define the properties of A. They may also be hard-wired within the formula.
nᵣ defines the number of terms associated with each dimension of A
dᵣ defines the direction, across or down the sheet, in which the dimension is read, and
sᵣ defines the stride length from one element of A to the next term along the given dimension.
(the stride length allows for blank rows or columns so is not necessesarily a simply product of selected nᵣ).
The presence of two Lambda funtions allows the parameters to be given in two groups. Supplying the first group stell leaves a Lambda function,
which may be named, that requires the array of indices iᵣ if it is to return an element of the array.
*/
ArrayRefλ = LAMBDA(A, nᵣ, dᵣ, sᵣ,
LAMBDA([iᵣ],
IF(
ISOMITTED(iᵣ),
nᵣ,
LET(
r_, 1 + SUM(IF(dᵣ = "down", (iᵣ - 1) * sᵣ, 0)),
c_, 1 + SUM(IF(dᵣ = "across", (iᵣ - 1) * sᵣ, 0)),
INDEX(A, r_, c_)
)
)
)
);
/*
DATAλ is the name given to a specific application of ArrayRef. Its definition contains parameters that control the functionality of ArrayRef
but still requires the index array iᵣ to be provided,
*/
DATAλ = ArrayRefλ(recurrentData, nᵣ, dᵣ, sᵣ);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment