Skip to content

Instantly share code, notes, and snippets.

@pbartxl
Last active November 5, 2024 23:33
Show Gist options
  • Save pbartxl/a14b250985da31be843ce9ff35d888fc to your computer and use it in GitHub Desktop.
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
*/
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λ, IFS(
args=1, array2,
args=2, array3,
args=3, fnλ),
fnλ, IF(TYPE(_fnλ) <> 128, #VALUE!, _fnλ),
// Set and check arrays
PRESENT, LAMBDA(x, AND(NOT(ISOMITTED(x)), 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ϑ, IFS(
args=1, MAP(_array1, LAMBDA(_arg, LET(term, _fnλ(_arg), THUNK(term)))),
args=2, MAP(_array1, _array2, LAMBDA(_arg1, _arg2, LET(term, _fnλ(_arg1, _arg2), THUNK(term)))),
args=3, 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: 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
*/
JOINPAIRSλ = LAMBDA([by_col], LAMBDA(thunkArray, [k],
LET(
alternate, WRAPROWS(thunkArray, 2, thunk("\")),
firstpart, TAKE(alternate, , 1),
finalpart, TAKE(alternate, , -1),
MAP(
firstpart,
finalpart,
LAMBDA(ϑ₁, ϑ₂,
LET(
x₁, (@ϑ₁)(),
x₂, (@ϑ₂)(),
v, IF(@x₂ = "\",
x₁,
IF(by_col, HSTACK(x₁, x₂), VSTACK(x₁, x₂))
),
THUNK(v)
)
)
)
)
));
THUNK
= LAMBDA(x,LAMBDA(x));
@ExcelRobot
Copy link

Brilliant work Peter! Similar in concept to my TILE function but works with lambdas that take up to three parameters.

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