Created
February 26, 2022 23:00
-
-
Save pbartxl/04584dd9a9e8fe9c61a10511c78f390c to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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