Skip to content

Instantly share code, notes, and snippets.

@pbartxl
Last active November 20, 2025 12:15
Show Gist options
  • Select an option

  • Save pbartxl/a14b250985da31be843ce9ff35d888fc to your computer and use it in GitHub Desktop.

Select an option

Save pbartxl/a14b250985da31be843ce9ff35d888fc to your computer and use it in GitHub Desktop.
A version of Excel MAP helper function that will return an array of arrays
/* FUNCTION NAME: MAPλ
DESCRIPTION: Implements a version of MAP that will return an array of arrays */
/* REVISIONS: Date Developer Description
28 Aug 2024 Peter Bartholomew Adapted from BYROWλ to give MAPλ
31 Aug 2024 Peter Bartholomew JOINPAIRSλ modified to stack 2D result arrays
10 Sep 2024 Peter Bartholomew Modified to accept 3 array argumments before λ-function
15 Nov 2024 David Clements Improved branching using CHOOSE
*/
MAPλ = LAMBDA(
// Parameter Declarations
[array1],
[array2],
[array3],
[fnλ],
// Procedure
LET(
help, TRIM(
TEXTSPLIT(
"DESCRIPTION: →Implements a version of MAP that will return an array of arrays.¶" &
"VERSION: →10 Sep 2024 Peter Bartholomew ¶" &
" →¶" &
"PARAMETERS: →¶" &
"  array1 →(Required) An array of values. ¶" &
"  array2 →(Optional) An array of values of the same dimension as array1 ¶" &
"  array3 →(Optional) An array of values of the same dimension as array1 ¶" &
"   →           (if the optional arrays are omitted, fnλ moves up). ¶" &
"  fnλ →(Required) A lambda function that accepts 1 to 3 arrays as input.",
"→",
"¶"
)
),
// Check inputs
// Locate and set function
args, IFS(
NOT(ISOMITTED(fnλ)), 3,
NOT(ISOMITTED(array3)), 2,
NOT(ISOMITTED(array2)), 1
),
_fnλ, CHOOSE(args, array2, array3, fnλ),
fnλ, IF(TYPE(_fnλ) <> 128, #VALUE!, _fnλ),
// Set and check arrays
PRESENT, LAMBDA(x, AND(NOT(ISOMITTED(x)), TYPE(x)<>128)),
_array1, IF(PRESENT(array1), array1, #VALUE!),
_array2, IF(args>=2, IF(PRESENT(array2), array2, #VALUE!)),
_array3, IF(args>=3, IF(PRESENT(array3), array3, #VALUE!)),
// Procedure
m, ROWS(_array1),
n, COLUMNS(_array1),
// Implements MAP, returning thunked results at each step
thunkArrayϑ, CHOOSE(args,
MAP(_array1, LAMBDA(_arg, LET(term, _fnλ(_arg), THUNK(term)))),
MAP(_array1, _array2, LAMBDA(_arg1, _arg2, LET(term, _fnλ(_arg1, _arg2), THUNK(term)))),
MAP(_array1, _array2, _array3, LAMBDA(_arg1, _arg2, _arg3, LET(term, _fnλ(_arg1, _arg2, _arg3), THUNK(term))))
),
// Recombine pairs of thunks as a binary tree until the root node is reached
result, EVALTHUNKARRλ(thunkArrayϑ),
// Handle Error
error, MAX(ISERROR(result) + 1),
// Return result
return, CHOOSE(error, result, Help),
return
)
);
/* FUNCTION NAME: BYROWλ
DESCRIPTION: Implements a version of BYROW that will return an array of arrays */
/* REVISIONS: Date Developer Description
20 Aug 2025 Peter Bartholomew Reimplemented from MAPλ
*/
BYROWλ = LAMBDA(
// Parameter Declarations
[array],
[fnλ],
// Procedure
LET(
help, TRIM(
TEXTSPLIT(
"DESCRIPTION: →Implements a version of BYROW that will return an array of arrays.¶" &
"VERSION: →20 Aug 2025 Peter Bartholomew ¶" &
" →¶" &
"PARAMETERS: →¶" &
"  array →(Required) An array of values. ¶" &
"  fnλ →(Required) A lambda function that accepts an array as input.",
"→",
"¶"
)
),
// Check inputs
// Set function
_fnλ, IF(TYPE(fnλ) <> 128, #VALUE!, fnλ),
// Set and check arrays
PRESENT, LAMBDA(x, AND(NOT(ISOMITTED(x)), TYPE(x)<>128)),
_array1, IF(PRESENT(array), array, #VALUE!),
// Procedure
// Implements BYROW, returning thunked results at each step
thunkArrayϑ, BYROW(_array1, LAMBDA(_arg, LET(term, _fnλ(_arg), THUNK(term)))),
// Recombine pairs of thunks as a binary tree until the root node is reached
result, EVALTHUNKARRλ(thunkArrayϑ),
// Handle Error
error, MAX(ISERROR(result) + 1),
// Return result
return, CHOOSE(error, result, Help),
return
)
);
/* FUNCTION NAME: EVALTHUNKARRλ
DESCRIPTION: Called by modified helper functions to stack the contents of any array of thunks */
/* REVISIONS: Date Developer Description
14 Oct 2024 Peter Bartholomew Extracted for MAPλ as a separate module
*/
EVALTHUNKARRλ = LAMBDA(thunkArrayϑ,
LET(
m, ROWS(thunkArrayϑ),
n, COLUMNS(thunkArrayϑ),
h, SEQUENCE(CEILING.MATH(LOG(n,2),1)),
recombinedRowsϑ, IF(
n > 1,
BYROW(thunkArrayϑ, LAMBDA(thunkRowϑ, @REDUCE(thunkRowϑ, h, JOINPAIRSλ(1)))),
thunkArrayϑ
),
k, SEQUENCE(CEILING.MATH(LOG(m,2),1)),
recombinedϑ, IF(
m > 1,
REDUCE(recombinedRowsϑ, k, JOINPAIRSλ(0)),
recombinedRowsϑ
),
result, IFNA((@recombinedϑ)(), ""),
result
)
);
/* FUNCTION NAME: JOINPAIRSλ
DESCRIPTION: Called by EVALTHUNKARRλ to stack the contents of thunks pairwise */
/* REVISIONS: Date Developer Description
09 May 2024 Peter Bartholomew Original Development
16 May 2024 Peter Bartholomew Test for unpaired thunk in binary tree
30 Aug 2024 Peter Bartholomew Modify to stack horizontally or vertically
15 Nov 2024 David Clements Efficiency improvements / simplification
*/
JOINPAIRSλ = LAMBDA([by_col], LAMBDA(thunkArray, [k],
LET(
STACKλ, IF(by_col, HSTACK, VSTACK),
alternate, WRAPROWS(thunkArray, 2),
MAP(
TAKE(alternate, , 1),
DROP(alternate, , 1),
LAMBDA(ϑ₁, ϑ₂,
LET(
x₁, ϑ₁(),
x₂, ϑ₂(),
v, IF(TYPE(ϑ₂)=16, x₁, STACKλ(x₁, x₂)),
LAMBDA(v)
)
)
)
)
));
THUNK
= LAMBDA(x,LAMBDA(x));
@pbartxl
Copy link
Author

pbartxl commented Nov 20, 2025

@harun24hr
Thank you for your approval! I have also posted versions of SCAN (SCANVλ and SCANHλ) that work vertically or horizontally as another gist that needs to be combined with this one.
An application that arose recently was to apply YoY growth rates to a complete year of data with a different rates for each row. The initial value was an (n x 12) array.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment