Last active
March 10, 2026 20:55
-
-
Save halbuki/3243638af945b4e8edf85bebc6b56887 to your computer and use it in GitHub Desktop.
Excel Lambda functions for array manipulation
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
| /* USE NAMESPACE "ARR" */ | |
| REPLACENTH = LAMBDA(array, xnew, trow, [tcol], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION:->Replaces the element in array at row trow and column tcol with xnew. If tcol is omitted it defaults to 1.\nVERSION:->1.0\nPARAMETERS:->array as array, xnew as any, trow as number, tcol as number (optional)", | |
| "->", | |
| "\n" | |
| )), | |
| // Error Messages | |
| ErrorMessages, {"trow is not a number.", "tcol is not a number."}, | |
| // Determine if tcol is omitted | |
| isTcolOmitted, ISOMITTED(tcol), | |
| // Check inputs: 1 if trow not number, 2 if tcol provided and not number | |
| CheckInputs, IF(NOT(ISNUMBER(trow)), 1, IF(AND(NOT(isTcolOmitted), NOT(ISNUMBER(tcol))), 2, 0)), | |
| // Set column index | |
| tcolVal, IF(isTcolOmitted, 1, MAX(tcol, 1)), | |
| // Procedure | |
| Result, MAKEARRAY( | |
| ROWS(array), | |
| COLUMNS(array), | |
| LAMBDA(irow, icol, | |
| IF(AND(irow = trow, icol = tcolVal), xnew, INDEX(array, irow, icol)) | |
| ) | |
| ), | |
| CHOOSE(CheckInputs + 1, | |
| Result, | |
| Help, | |
| INDEX(ErrorMessages, CheckInputs) | |
| ) | |
| ) | |
| ); | |
| ARRMAPLAM = LAMBDA(array, transf, [cond], | |
| LET( | |
| Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION:->Applies transformation function transf to each element of array where cond (optional) returns TRUE. If cond is omitted, applies to all elements.\nVERSION:->1.0\nPARAMETERS:->array as array, transf as function, cond as function (optional)", | |
| "->", | |
| "\n" | |
| )), | |
| ErrorMessages, {}, | |
| CheckInputs, 0, | |
| isCondOmitted, ISOMITTED(cond), | |
| Result, MAKEARRAY( | |
| ROWS(array), | |
| COLUMNS(array), | |
| LAMBDA(ir, ic, | |
| IF( | |
| IF(isCondOmitted, TRUE, cond(array, ir, ic)), | |
| transf(array, ir, ic), | |
| INDEX(array, ir, ic) | |
| ) | |
| ) | |
| ), | |
| CHOOSE(CheckInputs + 1, | |
| Result, | |
| Help, | |
| "" | |
| ) | |
| ) | |
| ); |
Comments are disabled for this gist.