Last active
October 8, 2025 13:10
-
-
Save CHatmaker/d0829c4bde303f5f8602c21158831323 to your computer and use it in GitHub Desktop.
Array Essentials Version 2.0: This version ads a few new functions and removes functions made relatively redundant with eta-reduced-formats.
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
| /* Array Essentials V2 - A collection of basic array 5g Functions | |
| This version trims the previous collection of 5g functions made redundant by eta-reduced syntax.*/ | |
| /* FUNCTION NAME: Aboutλ | |
| DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 29 2024 Craig Hatmaker Copyright | |
| Mar 11 2025 Craig Hatmaker General updates | |
| Mar 15 2025 Craig Hatmaker See MLookupλ, XYLookupλ | |
| Mar 24 2025 Craig Hatmaker See DAF versions of common Excel functions | |
| May 15 2025 Craig Hatmaker See AndAλ, AvgAλ, MaxAλ, MinAλ, OrAλ, and SumAλ | |
| Sep 25 2025 Craig Hatmaker Added OnChangeλ, OnChangeCountλ, and OnChangeResetλ | |
| Oct 06 2025 Craig Hatmaker Added Row and Column aware suite of functions | |
| Oct 08 2025 Craig Hatmaker Added web addresses on some functions | |
| */ | |
| Aboutλ = TRIM(TEXTSPLIT( | |
| "About: →Basic functions for working with dynamic arrays. Suggested module name: BXE¶" & | |
| "Version: →BXL: Oct 06 2025¶" & | |
| "Gist URL: →https://gist.github.com/CHatmaker/d0829c4bde303f5f8602c21158831323 ¶" & | |
| "Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" & | |
| "→¶" & | |
| "Function →Description¶" & | |
| " Aboutλ →Produces this table¶" & | |
| " IsBetweenλ →Determine if a value is between a lower and upper limit.¶" & | |
| " Instanceλ →Determine each value's instance within an array.¶" & | |
| " IsInListλ →Determine if a value is one of a list of items.¶" & | |
| " Offsetλ →A version of OFFSET that works with arrays internally and is not volitile.¶" & | |
| " RangeToDAλ →Convert a range reference to a dynamic array reference¶" & | |
| "→¶" & | |
| "Array basics→¶" & | |
| " GetDiagonalsλ →List array's values in diagonals going from up left to down right starting in top right corner¶" & | |
| " FlipArrayλ →Flip array vertically or horizontally¶" & | |
| " RotateArrayλ →Rotate array clockwise or counter clockwise¶" & | |
| "→¶" & | |
| "Array Lookups & Filters→Lookup functions we wish Excel had¶" & | |
| " FilterContainsλ →Filter an array by another array that contains specifice text¶" & | |
| " BYCOLContainsλ →Applies a function to each column of a Values array filterd by a Labels array containing specified text¶" & | |
| " MLookupλ →Find values matching 1+ criteria in 1 to 4 rows or columns (but not both).¶" & | |
| " XYLookupλ →Find values matching 1+ criteria in a column, and 1+ in a header.¶" & | |
| "→¶" & | |
| "On Change Suite →Functions that respond to changes in sequences.¶" & | |
| " OnChangeλ →Indicate an array change with a flag (1)¶" & | |
| " OnChangeCountλ →Increment a counter when an array changes¶" & | |
| " OnChangeResetλ →Reset a counter when an array changes¶" & | |
| "→¶" & | |
| "Running totals →Functions create cummulative totals.¶" & | |
| " RunTotColsλ →Create a running total for values in each column¶" & | |
| " RunTotRowsλ →Create a running total for values in each row¶" & | |
| "→¶" & | |
| "Rolling Suite →Functions that apply to a window over an array.¶" & | |
| " Rollingλ →Roll window over a values and apply a function to it¶" & | |
| " RollAvgλ →Rolling AVERAGE. Same as Rollingλ( Array, , , , AVERAGE)¶" & | |
| " RollMaxλ →Rolling MAX. Same as Rollingλ( Array, , , , MAX)¶" & | |
| " RollMinλ →Rolling MIN. Same as Rollingλ( Array, , , , MIN)¶" & | |
| " RollSumλ →Rolling SUM. Same as Rollingλ( Array)¶" & | |
| "→¶" & | |
| "Array Aware →A collection of functions applied by row or column based on the function's¶" & | |
| "→ position relative to the array (if in the grid)¶" & | |
| " AndAλ →AND an array by column or by row based on the function's position relative to the array (if in the grid)¶" & | |
| " AvgAλ →AVERAGE an array by column or by row based on the function's position relative to the array (if in the grid)¶" & | |
| " MaxAλ →MAX an array by column or by row based on the function's position relative to the array (if in the grid)¶" & | |
| " MinAλ →MIN an array by column or by row based on the function's position relative to the array (if in the grid)¶" & | |
| " OrAλ →OR an array by column or by row based on the function's position relative to the array (if in the grid)¶" & | |
| " SumAλ →SUM an array by column or by row based on the function's position relative to the array (if in the grid)¶" & | |
| " Arrayλ →A LAMBDA helper for applying a function by column or by row based on the function's position relative¶" & | |
| "→ to the array (if in the grid)¶" & | |
| "→¶" & | |
| "Row Aware →A collection of functions that apply themselves to an array's row that they share¶" & | |
| " AndRλ →AND an array's row that this function shares.¶" & | |
| " AvgRλ →AVERAGE an array's row that this function shares.¶" & | |
| " MaxRλ →MAX an array's row that this function shares.¶" & | |
| " MinRλ →MIN an array's row that this function shares.¶" & | |
| " OrRλ →OR an array's row that this function shares.¶" & | |
| " SumRλ →SUM an array's row that this function shares.¶" & | |
| " ProcessRowλ →A LAMBDA helper for applying a function by column for the array's row that this function shares¶" & | |
| "→¶" & | |
| "Column Aware →A collection of functions that apply themselves to an array's column that they share¶" & | |
| " AndCλ →AND an array's column that this function shares.¶" & | |
| " AvgCλ →AVERAGE an array's column that this function shares.¶" & | |
| " MaxCλ →MAX an array's column that this function shares.¶" & | |
| " MinCλ →MIN an array's column that this function shares.¶" & | |
| " OrCλ →OR an array's column that this function shares.¶" & | |
| " SumCλ →SUM an array's column that this function shares.¶" & | |
| " ProcessColλ →A LAMBDA helper for applying a function by column for the array's row that this function shares", | |
| "→","¶" | |
| ) | |
| ); | |
| /* FUNCTION NAME: Instanceλ | |
| DESCRIPTION:*//**Determine each value's instance within an array*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 29 2024 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Prep for Pro Pack | |
| */ | |
| Instanceλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Instanceλ( Values)¶" & | |
| "DESCRIPTION: →Determine each value's instance within an array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-instance%CE%BB¶" & | |
| "VERSION: →BXL: May 27 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Values →(Required) A row or column of values.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Values)), | |
| // Procedure | |
| Elements, COUNTA( Values), | |
| Counter, IF( ROWS( Values) > 1, SEQUENCE( Elements), SEQUENCE( , Elements)), | |
| Result, MAP( Values, Counter, | |
| LAMBDA( Val, n, | |
| SUM(( Val = Values) * (Counter <= n)))), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: IsBetweenλ | |
| DESCRIPTION:*//**Determine if a value is between a lower and upper limit*/ | |
| /* REVISIONS: Date Developer Description | |
| Jun 01 2022 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Added Help | |
| */ | |
| IsBetweenλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| [Low], | |
| [High], | |
| [Inclusive], | |
| // Help | |
| LET( | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →IsBetweenλ(Values, Low, High, [Inclusive])¶" & | |
| "DESCRIPTION: →Determine if a value is between a lower and upper limit.¶" & | |
| "WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-sample-components/5g-isbetween%CE%BB ¶" & | |
| "VERSION: →BXL: May 27 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Values →(Required) One or more values to compare.¶" & | |
| " Low →(Required) The lower limit that the value(s) must be greater than¶" & | |
| " High →(Required) The higher limit that the value(s) must be less than¶" & | |
| " Inclusive →(Optional) TRUE: Value(s) can be equal to Low and/or High (default)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Set optional arguments defaults | |
| Inclusive, IF(ISLOGICAL(Inclusive), Inclusive, TRUE), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( | |
| ISOMITTED(Values), | |
| ISOMITTED(Low), | |
| ISOMITTED(High) | |
| ), | |
| // Procedure | |
| Result, IF( Inclusive, | |
| (Values >= Low) * (Values <= High) = 1, | |
| (Values > Low) * (Values < High) = 1 | |
| ), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: IsInListλ | |
| DESCRIPTION:*//**Determine if a value is between a lower and upper limit*/ | |
| /* REVISIONS: Date Developer Description | |
| Jun 18 2023 Craig Hatmaker Copyright | |
| Feb 28 2024 Craig Hatmaker Updated to new standards | |
| May 26 2025 Craig Hatmaker Pro Pack Prep | |
| */ | |
| IsInListλ = LAMBDA( | |
| // Parameter Declarations | |
| [Value], | |
| [LIST], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →IsInListλ( Value, List)¶" & | |
| "DESCRIPTION: →Determine if a value is one of a list of items.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-isinlist%CE%BB¶" & | |
| "VERSION: →BXL: May 26 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Value →(Required) A value or array of values to compare.¶" & | |
| " List →(Required) A list of values to check.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Value), | |
| ISOMITTED( LIST) | |
| ), | |
| // Procedure | |
| Result, NOT( ISNA( MATCH( Value, List, 0))), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Offsetλ | |
| DESCRIPTION:*//**A version of OFFSET that works with arrays Internally and is not volitile*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 26 2025 Craig Hatmaker Copyright | |
| */ | |
| Offsetλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Rows], | |
| [Cols], | |
| [Height], | |
| [Width], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Offsetλ(Array, [Rows], [Cols], [Height], [Width])¶" & | |
| "DESCRIPTION: →A version of OFFSET that works with arrays internally and is not volitile¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/???¶" & | |
| "VERSION: →BXL: Aug 25 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) The array or reference from which you want to base the offset. ¶" & | |
| " Rows →(Optional) The number of rows down (+), relative to Array's first element that ¶" & | |
| "→ you want offset to get values from. Default = 0.¶" & | |
| " Cols →(Optional) The number of columns, right relative to Array's first element that ¶" & | |
| "→ you want offset to get values from. Default = 0.¶" & | |
| " Height →(Optional) The number of rows that you want the returned values to be.¶" & | |
| "→ Height must be a positive number. Default = 1.¶" & | |
| " Width →(Optional) The number of columns that you want the returned values to be.¶" & | |
| "→ Width must be a positive number. Default = 1", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Check inputs - Set optional arguments' defaults | |
| Rows, IF( ISOMITTED( Rows), 0, Rows), | |
| Cols, IF( ISOMITTED( Cols), 0, Cols), | |
| Height, IF( ISOMITTED( Height), 1, Height), | |
| Width, IF( ISOMITTED( Width), 1, Width), | |
| // Procedure | |
| TakeRows, CHOOSEROWS( Array, SEQUENCE( Height, , Rows + 1)), | |
| TakeCols, CHOOSECOLS( TakeRows, SEQUENCE( Width, , Cols + 1)), | |
| Result, TakeCols, | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RangeToDAλ | |
| DESCRIPTION:*//**Convert a range reference to a dynamic array reference*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 19,2023 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Pro Pack Prep | |
| */ | |
| RangeToDAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Range], | |
| [Rows], | |
| [Columns], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RangeToDAλ(Range, [Rows], [Columns])¶" & | |
| "DESCRIPTION: →Convert a range reference to a dynamic array reference.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-rangetoda%CE%BB¶" & | |
| "VERSION: →BXL: May 27 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Range →(Required) A reference to the range to be converted.¶" & | |
| " Rows →(Optional) Number of rows for the dynamic array. Defaults to 1.¶" & | |
| " Columns →(Optional) Number of columns for the dynamic array. Defaults to 1.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Range), | |
| // Check inputs - Set optional arguments' defaults | |
| Rows, IF( ISNUMBER( Rows), Rows, 1), | |
| Columns, IF( ISNUMBER( Columns), Columns, 1), | |
| // Procedure | |
| Result, OFFSET( Range, 0, 0, Rows, Columns), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| // Array basics | |
| /* FUNCTION NAME: GetDiagonalsλ | |
| DESCRIPTION:*/ | |
| /**List Array diagonals. Direction depends on Direction and Reverse. | |
| Dir.,Rev.: Result | |
| 1, FALSE: ↙, start top left, across then down | |
| -1, FALSE: ↘, start top right, across then down | |
| 1, TRUE: ↗, start top left, down then across | |
| -1, TRUE: ↖, start top right, down then across | |
| */ | |
| /* REVISIONS: Date Developer Description | |
| Aug 13 2025 Craig Hatmaker Copyright | |
| */ | |
| GetDiagonalsλ = LAMBDA( | |
| [Array], | |
| [Direction], | |
| [Reverse], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →GetDiagonalsλ( Array, [Direction], [Reverse])¶" & | |
| "DESCRIPTION: →List Array diagonals. Direction depends on optional arguments:¶" & | |
| "→Dir.,Rev.: Result¶" & | |
| "→1, FALSE: ↙, start top left, across then down¶" & | |
| "→-1,FALSE: ↘, start top right, across then down¶" & | |
| "→1, TRUE: ↗, start top left, down then across¶" & | |
| "→-1,TRUE: ↖, start top right, down then across¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-getdiagonals%CE%BB ¶" & | |
| "VERSION: →BXL: Aug 13 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) Array to list values in diagonals.¶" & | |
| " Direction →(Optional) Diagonal direction. Defaut=1. See DESCRIPTION:¶" & | |
| " Reverse →(Optional) Reverse results. Default=FALSE, See DESCRIPTION:.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Set Defaults | |
| Direction, IF( ISOMITTED( Direction), 1, Direction), | |
| Reverse, IF( ISOMITTED( Reverse), FALSE, Reverse), | |
| // Define reverse CSV function | |
| fnRevCSV, LAMBDA( Text, | |
| LET( | |
| Values, TEXTSPLIT( Text, ","), | |
| Count, COLUMNS( Values), | |
| Reverse, INDEX( Values, 1, SEQUENCE( Count, ,Count, -1)), | |
| Result, TEXTJOIN( ",", ,Reverse), | |
| Result | |
| ) | |
| ), | |
| // Procedure | |
| RowNums, SEQUENCE( ROWS( Array)), | |
| ColNums, SEQUENCE( , Columns( Array)), | |
| Diagonals, RowNums + Direction * ColNums, | |
| Group, GROUPBY( | |
| TOCOL( Diagonals), | |
| TOCOL( Array), | |
| ARRAYTOTEXT , , 0 | |
| ), | |
| Column2, DROP( Group, , 1), | |
| CSV, IF( Reverse, | |
| MAP( Column2, fnRevCSV), | |
| Column2 | |
| ) & ";", | |
| Result, IFNA( DROP( TEXTSPLIT( CONCAT(CSV), ",", ";"),-1), ""), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: FlipArrayλ | |
| DESCRIPTION:*//**Flip array*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 14 2025 Craig Hatmaker Copyright | |
| */ | |
| FlipArrayλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Vertically?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →FlipArrayλ( Array, [Vertically])¶" & | |
| "DESCRIPTION: →Flip array vertically or horizontally¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-fliparray%CE%BB¶" & | |
| "VERSION: →BXL: Aug 13 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) Array to flip.¶" & | |
| " Vertically? →(Optional) TRUE=flip vertically. FALSE=Horizontally.¶" & | |
| "→Default=TRUE.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Set Defaults | |
| Vertically?, IF( ISOMITTED( Vertically?), TRUE, Vertically?), | |
| // Procedure | |
| Rows, ROWS( Array), | |
| Cols, COLUMNS( Array), | |
| Result, IF( Vertically?, | |
| VSTACK( CHOOSEROWS( Array, SEQUENCE( Rows, , Rows, -1))), | |
| HSTACK( CHOOSECOLS( Array, SEQUENCE( Cols, , Cols, -1))) | |
| ), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RotateArrayλ | |
| DESCRIPTION:*//**Rotate array*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 12 2025 Craig Hatmaker Copyright | |
| */ | |
| RotateArrayλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Clockwise?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RotateArrayλ( Array, [Direction])¶" & | |
| "DESCRIPTION: →Rotate array clockwise or counter clockwise¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-rotatearray%CE%BB¶" & | |
| "VERSION: →BXL: Aug 12 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) Array to rotate.¶" & | |
| " Clockwise? →(Optional) TRUE=Rotate clockwise. FALSE=Counter clockwise.¶" & | |
| "→Default=TRUE.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Set Defaults | |
| Clockwise?, IF( ISOMITTED( Clockwise?), TRUE, Clockwise?), | |
| // Procedure | |
| Transpose, TRANSPOSE( Array), | |
| Rows, ROWS( Transpose), | |
| Cols, COLUMNS( Transpose), | |
| Result, IF( Clockwise?, | |
| HSTACK( CHOOSECOLS( Transpose, SEQUENCE( Cols, , Cols, -1))), | |
| VSTACK( CHOOSEROWS( Transpose, SEQUENCE( Rows, , Rows, -1))) | |
| ), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| // Rolling Suite | |
| /* FUNCTION NAME: Rollingλ | |
| DESCRIPTION:*//**Roll a window over an array and apply a function to the window.*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 23 2025 Craig Hatmaker Copyright | |
| */ | |
| Rollingλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| [Function], | |
| [RollRows?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Rollingλ(Array, [Size], [Offset], [AllowPartial], [Function], [RollRows?])¶" & | |
| "DESCRIPTION: →Roll a window over an array and apply a function to the window.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-rolling%CE%BB¶" & | |
| "VERSION: →BXL: Aug 23 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array to roll a window over.¶" & | |
| " Size →(Optional) Window's size (number of values). Must be > 0. Default = 3.¶" & | |
| " Offset →(Optional) Number of values after (+) or prior (-) current position. Default = -2.¶" & | |
| " AllowPartial →(Optional) TRUE (Default) = Apply function to window that starts or ends¶" & | |
| "→ outside the array (the window will be smaller than Size).¶" & | |
| "→ FALSE = Do not apply function when window starts or ends outside array.¶" & | |
| " Function →(Optional) An Excel function accepting an array of values or a LAMBDA.¶" & | |
| "→ Default is SUM()¶" & | |
| " RollRows? →(Optional) TRUE or 1 = Rolling proceeds horizontally by row.¶" & | |
| "→ FALSE or 0 = Rolling proceeds Vertically by column. Default=TRUE", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Check inputs - Set optional arguments' defaults | |
| Size, IF( ISOMITTED( Size), 3, Size), | |
| Offset, IF( ISOMITTED( Offset), -2, Offset), | |
| AllowPartial, IF( ISOMITTED( AllowPartial), TRUE, AllowPartial), | |
| RollRows?, IF( ISOMITTED( RollRows?), TRUE, RollRows?), | |
| // Define Rolling Function | |
| fnRollRow, LAMBDA( Row, Size, Offset, AllowPartial, Function, | |
| LET( | |
| Elements, COLUMNS( Row), | |
| Counter, SEQUENCE( , Elements), | |
| Result, MAP( Counter, | |
| LAMBDA( n, | |
| LET( | |
| Left, n + Offset, | |
| Right, Left + Size - 1, | |
| Window, DROP( TAKE( Row, , Right) , , MAX( 0, Left - 1)), | |
| //Window, OFFSET( Row, 0, MAX( 0, n + offset), 1, Size + MIN(0, n),// | |
| Result, IF( OR( Right < 1, Left > Elements), | |
| NA(), | |
| IF( AND( NOT( AllowPartial), OR(Right > Elements, Left < 1)), | |
| NA(), | |
| IF( ISOMITTED( Function), | |
| SUM( Window), | |
| Function( Window) | |
| ) | |
| ) | |
| ), | |
| Result | |
| ) | |
| ) | |
| ), | |
| Result | |
| ) | |
| ), | |
| // Procedure | |
| Counter, SEQUENCE( IF( RollRows?, ROWS( Array), COLUMNS( Array))), | |
| Result, REDUCE(0, Counter, | |
| LAMBDA(Acc,n, | |
| LET( | |
| Row, IF( RollRows?, CHOOSEROWS( Array, n) , TRANSPOSE( CHOOSECOLS( Array, n))), | |
| Roll, fnRollRow( Row, Size, Offset, AllowPartial, Function), | |
| Rolled, IF( RollRows?, Roll, TRANSPOSE( Roll)), | |
| Result, IF( n = 1, Rolled, IF( RollRows?, VSTACK( Acc, Rolled), HSTACK( Acc, Rolled))), | |
| Result | |
| ) | |
| ) | |
| ), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RollAvgλ | |
| DESCRIPTION:*//**Average a rolling window over a row. Same as Rollingλ( Row, Size, Offset, AllowPartial, AVERAGE)*/ | |
| /* REVISIONS: Date Developer Description | |
| May 29 2025 Craig Hatmaker Copyright | |
| */ | |
| RollAvgλ = LAMBDA( | |
| // Parameter Declarations | |
| [Row], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RollAvgλ(Row, [Size], [Offset], [AllowPartial])¶" & | |
| "DESCRIPTION: →Average a rolling window over a row.¶" & | |
| "→ Same as Rollingλ( Row, Size, Offset, AllowPartial, AVERAGE)¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-rollminmaxavg%CE%BB¶" & | |
| "VERSION: →BXL: May 29 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array to roll a window over.¶" & | |
| " Size →(Optional) Window's size (number of values). Must be > 0. Default = 3.¶" & | |
| " Offset →(Optional) Number of values after (+) or prior (-) current position. Default = -2.¶" & | |
| " AllowPartial →(Optional) TRUE (Default) = Apply function to window that starts or ends¶" & | |
| "→ outside the array (the window will be smaller than Size).¶" & | |
| "→ FALSE = Do not apply function when window starts or ends outside array.¶" & | |
| " RollRows? →(Optional) TRUE or 1 = Rolling proceeds horizontally by row.¶" & | |
| "→ FALSE or 0 = Rolling proceeds Vertically by column. Default=TRUE", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Row), | |
| // Check inputs - Set optional arguments' defaults | |
| Size, IF( ISOMITTED( Size), 3, Size), | |
| Offset, IF( ISOMITTED( Offset), -2, Offset), | |
| AllowPartial, IF( ISOMITTED( AllowPartial), TRUE, AllowPartial), | |
| // Procedure | |
| Result, Rollingλ( Row, Size, Offset, AllowPartial, AVERAGE), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RollMaxλ | |
| DESCRIPTION:*//**Find the maximum value in a rolling window over a row. Same as Rollingλ( Row, Size, Offset, AllowPartial, MAX)*/ | |
| /* REVISIONS: Date Developer Description | |
| May 29 2025 Craig Hatmaker Copyright | |
| */ | |
| RollMaxλ = LAMBDA( | |
| // Parameter Declarations | |
| [Row], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RollMaxλ(Row, [Size], [Offset], [AllowPartial])¶" & | |
| "DESCRIPTION: →Find the maximum value in a rolling window over a row.¶" & | |
| "→ Same as Rollingλ( Row, Size, Offset, AllowPartial, MAX)¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-rollminmaxavg%CE%BB¶" & | |
| "VERSION: →BXL: May 29 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array to roll a window over.¶" & | |
| " Size →(Optional) Window's size (number of values). Must be > 0. Default = 3.¶" & | |
| " Offset →(Optional) Number of values after (+) or prior (-) current position. Default = -2.¶" & | |
| " AllowPartial →(Optional) TRUE (Default) = Apply function to window that starts or ends¶" & | |
| "→ outside the array (the window will be smaller than Size).¶" & | |
| "→ FALSE = Do not apply function when window starts or ends outside array.¶" & | |
| " RollRows? →(Optional) TRUE or 1 = Rolling proceeds horizontally by row.¶" & | |
| "→ FALSE or 0 = Rolling proceeds Vertically by column. Default=TRUE", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Row), | |
| // Check inputs - Set optional arguments' defaults | |
| Size, IF( ISOMITTED( Size), 3, Size), | |
| Offset, IF( ISOMITTED( Offset), -2, Offset), | |
| AllowPartial, IF( ISOMITTED( AllowPartial), TRUE, AllowPartial), | |
| // Procedure | |
| Result, Rollingλ( Row, Size, Offset, AllowPartial, MAX), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RollMinλ | |
| DESCRIPTION:*//**Find the minimum value in a rolling window over a row. Same as Rollingλ( Row, Size, Offset, AllowPartial, MIN)*/ | |
| /* REVISIONS: Date Developer Description | |
| May 29 2025 Craig Hatmaker Copyright | |
| */ | |
| RollMinλ = LAMBDA( | |
| // Parameter Declarations | |
| [Row], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RollMinλ(Row, [Size], [Offset], [AllowPartial])¶" & | |
| "DESCRIPTION: →Find the minimum value in a rolling window over a row.¶" & | |
| "→ Same as Rollingλ( Row, Size, Offset, AllowPartial, MIN)¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-rollminmaxavg%CE%BB¶" & | |
| "VERSION: →BXL: May 29 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array to roll a window over.¶" & | |
| " Size →(Optional) Window's size (number of values). Must be > 0. Default = 3.¶" & | |
| " Offset →(Optional) Number of values after (+) or prior (-) current position. Default = -2.¶" & | |
| " AllowPartial →(Optional) TRUE (Default) = Apply function to window that starts or ends¶" & | |
| "→ outside the array (the window will be smaller than Size).¶" & | |
| "→ FALSE = Do not apply function when window starts or ends outside array.¶" & | |
| " RollRows? →(Optional) TRUE or 1 = Rolling proceeds horizontally by row.¶" & | |
| "→ FALSE or 0 = Rolling proceeds Vertically by column. Default=TRUE", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Row), | |
| // Check inputs - Set optional arguments' defaults | |
| Size, IF( ISOMITTED( Size), 3, Size), | |
| Offset, IF( ISOMITTED( Offset), -2, Offset), | |
| AllowPartial, IF( ISOMITTED( AllowPartial), TRUE, AllowPartial), | |
| // Procedure | |
| Result, Rollingλ( Row, Size, Offset, AllowPartial, MIN), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RollSumλ | |
| DESCRIPTION:*//**Total a rolling window over a row. Same as Rollingλ( Row, Size, Offset, AllowPartial, SUM)*/ | |
| /* REVISIONS: Date Developer Description | |
| May 29 2025 Craig Hatmaker Copyright | |
| */ | |
| RollSumλ = LAMBDA( | |
| // Parameter Declarations | |
| [Row], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RollSumλ(Row, [Size], [Offset], [AllowPartial])¶" & | |
| "DESCRIPTION: →Average a rolling window over a row.¶" & | |
| "→ Same as Rollingλ( Row, Size, Offset, AllowPartial, SUM)¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-rolling%CE%BB¶" & | |
| "VERSION: →BXL: May 29 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array to roll a window over.¶" & | |
| " Size →(Optional) Window's size (number of values). Must be > 0. Default = 3.¶" & | |
| " Offset →(Optional) Number of values after (+) or prior (-) current position. Default = -2.¶" & | |
| " AllowPartial →(Optional) TRUE (Default) = Apply function to window that starts or ends¶" & | |
| "→ outside the array (the window will be smaller than Size).¶" & | |
| "→ FALSE = Do not apply function when window starts or ends outside array.¶" & | |
| " RollRows? →(Optional) TRUE or 1 = Rolling proceeds horizontally by row.¶" & | |
| "→ FALSE or 0 = Rolling proceeds Vertically by column. Default=TRUE", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Row), | |
| // Check inputs - Set optional arguments' defaults | |
| Size, IF( ISOMITTED( Size), 3, Size), | |
| Offset, IF( ISOMITTED( Offset), -2, Offset), | |
| AllowPartial, IF( ISOMITTED( AllowPartial), TRUE, AllowPartial), | |
| // Procedure | |
| Result, Rollingλ( Row, Size, Offset, AllowPartial, SUM), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| // On change suite - Functions that respond to whan an array changes | |
| /* FUNCTION NAME: OnChangeλ | |
| DESCRIPTION:*//**Create a flag (1) when an array changes*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 24 2025 Craig Hatmaker Copyright | |
| */ | |
| OnChangeλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →OnChangeλ( Array)¶" & | |
| "DESCRIPTION: →Create a flag (1) when an array changes¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→/5g-component-libraries/5g-array-essentials-library/5g-onchange-suite¶" & | |
| "VERSION: →BXL: Sep 24 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array of values.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array)), | |
| // Procedure | |
| Rows, ROWS( Array), | |
| Cols, COLUMNS( Array), | |
| Col, TOCOL( Array), | |
| Shift, VSTACK( TAKE( Col, 1), DROP( Col, -1)), | |
| Changes, N(Col <> Shift), | |
| Result, WRAPROWS( Changes, Cols), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: OnChangeCountλ | |
| DESCRIPTION:*//**Count when an array changes*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 24 2025 Craig Hatmaker Copyright | |
| */ | |
| OnChangeCountλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Start], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →OnChangeCountλ( Array, [Start])¶" & | |
| "DESCRIPTION: →Increment a counter when an array changes¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→/5g-component-libraries/5g-array-essentials-library/5g-onchange-suite¶" & | |
| "VERSION: →BXL: Sep 24 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array of values.¶" & | |
| " Start →(Optional) Counter's start value. Default = 1.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array)), | |
| // Set Defaults | |
| Start, IF( ISOMITTED( Start), 1, Start), | |
| // Define OnChange function | |
| fnOnChange, LAMBDA( Array, | |
| LET( | |
| Rows, ROWS( Array), | |
| Cols, COLUMNS( Array), | |
| Col, TOCOL( Array), | |
| Shift, VSTACK( TAKE( Col, 1), DROP( Col, -1)), | |
| Changes, N(Col <> Shift), | |
| Result, WRAPROWS( Changes, Cols), | |
| Result | |
| ) | |
| ), | |
| // Procedure | |
| Flagged, fnOnChange(Array), | |
| Result, SCAN( Start, Flagged, SUM), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: OnChangeResetλ | |
| DESCRIPTION:*//**Count when an array does not change, then reset counter when it does*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 24 2025 Craig Hatmaker Copyright | |
| */ | |
| OnChangeResetλ = LAMBDA( | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →OnChangeResetλ( Array)¶" & | |
| "DESCRIPTION: →Count when an array does not change, then reset counter when it does.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→/5g-component-libraries/5g-array-essentials-library/5g-onchange-suite¶" & | |
| "VERSION: →BXL: Sep 24 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array of values.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array)), | |
| // Define OnChange function | |
| fnOnChange, LAMBDA( Array, | |
| LET( | |
| Rows, ROWS( Array), | |
| Cols, COLUMNS( Array), | |
| Col, TOCOL( Array), | |
| Shift, VSTACK( TAKE( Col, 1), DROP( Col, -1)), | |
| Changes, N(Col <> Shift), | |
| Result, WRAPROWS( Changes, Cols), | |
| Result | |
| ) | |
| ), | |
| // Procedure | |
| Flagged, NOT( OnChangeλ( Array)), | |
| Result, SCAN( 0, Flagged, LAMBDA( Acc, n, Acc * n + 1)), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RunTotColsλ | |
| DESCRIPTION:*//**Create a running total for values in each column of an array*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 05 2024 Craig Hatmaker Initial development w/help from Diarmuid Early | |
| */ | |
| RunTotColsλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| [OpeningValues], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RunTotColsλ( Values, [OpeningValues])¶" & | |
| "DESCRIPTION: →Create a running total for values in each column of an array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-runtotcols%CE%BB¶" & | |
| "VERSION: →BXL: Sep 05 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " Values →(Required) A 1 or 2 dimensional array/range of values.¶" & | |
| " OpeningValues →(Optional) Opening values for each column in Values. Default is 0.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Values)), | |
| // Set Defaults | |
| OpeningValues, IF( ISOMITTED( OpeningValues), 0, OpeningValues), | |
| // Procedure | |
| Values, TRANSPOSE( Values), | |
| Scanned, SCAN( 0, Values, SUM), | |
| /* LET step Scanned produces a running total of everything. | |
| LET step RowReset fixes the first value in each row */ | |
| RowReset, Scanned - DROP( VSTACK( 0, TAKE( Scanned, , -1)), -1), | |
| Result, TRANSPOSE( OpeningValues + RowReset), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RunTotRowsλ | |
| DESCRIPTION:*//**Create a running total for values in each row of an array*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 05 2024 Craig Hatmaker Initial development w/help from Diarmuid Early | |
| */ | |
| RunTotRowsλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| [OpeningValues], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RunTotRowsλ( Values, [OpeningValues])¶" & | |
| "DESCRIPTION: →Create a running total for values in each row of an array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-runtotrows%CE%BB¶" & | |
| "VERSION: →BXL: Sep 05 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " Values →(Required) A 1 or 2 dimensional array/range of values.¶" & | |
| "OpeningValues →(Optional) Opening values for each row in Values. Default is 0.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Values)), | |
| // Set Defaults | |
| OpeningValues, IF( ISOMITTED( OpeningValues), 0, OpeningValues), | |
| // Procedure | |
| Scanned, SCAN( 0, Values, SUM), | |
| /* LET step Scanned produces a running total of everything. | |
| LET step RowReset fixes the first value in each row */ | |
| RowReset, Scanned - DROP( VSTACK( 0, TAKE( Scanned, , -1)), -1), | |
| Result, OpeningValues + RowReset, | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| // Array Lookups and Filters | |
| /* FUNCTION NAME: →FilterContainsλ | |
| DESCRIPTION:*//**→Filters a Values array by a Labels array containing specified text*/ | |
| /* REVISIONS: Date Developer Description | |
| Feb 22 2024 Craig Hatmaker Initial development and copyright | |
| */ | |
| FilterContainsλ = LAMBDA( | |
| // Parameter Declarations | |
| [ValuesToFilter], | |
| [ValueLabels], | |
| [FilterByText], | |
| [IgnoreCase?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →FilterContainsλ( ValuesToFilter, ValueLabels, FilterByText, [IgnoreCase?])¶" & | |
| "DESCRIPTION: →Filters a Values array by a Labels array containing specified text¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-filtercontains%CE%BB¶" & | |
| "VERSION: →BXL: Feb 22 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " ValuesToFilter→(Required) An array of values to be filtered¶" & | |
| " ValueLabels →(Required) An array containing the values' labels¶" & | |
| " FilterByText →(Required) A unique letter, word or phrase to find contained¶" & | |
| "→in ValueLabels¶" & | |
| " IgnoreCase? →(Optional) A flag to determine if Text's case is important.¶" & | |
| "→Default is TRUE", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ValuesToFilter), | |
| ISOMITTED( ValueLabels), | |
| ISOMITTED( FilterByText)), | |
| // Check inputs - Set defaults | |
| IgnoreCase?, IF( ISLOGICAL( IgnoreCase?), IgnoreCase?, TRUE), | |
| Labels, CHOOSECOLS(ValueLabels, 1), | |
| // Procedure | |
| Matches, IF( IgnoreCase?, | |
| SEARCH( FilterByText, Labels), | |
| FIND( FilterByText, Labels) | |
| ), | |
| Result, FILTER( ValuesToFilter, ISNUMBER( Matches)), | |
| // Return Result or help | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: →BYCOLContainsλ | |
| DESCRIPTION:*//**→Applies a function to each column of a Values array filterd by a Labels array containing specified text*/ | |
| /* REVISIONS: Date Developer Description | |
| Feb 22 2024 Craig Hatmaker Initial development and copyright | |
| */ | |
| BYCOLContainsλ = LAMBDA( | |
| // Parameter Declarations | |
| [ValuesToFilter], | |
| [ValueLabels], | |
| [FilterByText], | |
| [Function], | |
| [IgnoreCase?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →BYCOLContainsλ( ValuesToFilter, ValueLabels, FilterByText,¶" & | |
| "→ [Function], [IgnoreCase?])¶" & | |
| "DESCRIPTION: →This is a helper function. It applies a function that we select or create to each¶" & | |
| "→column in a 'values' array filtered by a 'labels' array containing specified text¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-bycolcontains%CE%BB¶" & | |
| "VERSION: →BXL: Feb 22 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " ValuesToFilter→(Required) An array of values to be filtered¶" & | |
| " ValueLabels →(Required) An array containing the values' labels¶" & | |
| " FilterByText →(Required) A unique letter, word or phrase to find contained¶" & | |
| "→in ValueLabels¶" & | |
| " Function →(Optiona) An Excel function or a LAMBDA function to process¶" & | |
| "→a column of values. Default = SUM.¶" & | |
| " IgnoreCase? →(Optional) A flag to determine if Text's case is important.¶" & | |
| "→Default is TRUE.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ValuesToFilter), | |
| ISOMITTED( ValueLabels), | |
| ISOMITTED( FilterByText)), | |
| // Check inputs - Set defaults | |
| IgnoreCase?, IF( ISLOGICAL( IgnoreCase?), IgnoreCase?, TRUE), | |
| Labels, CHOOSECOLS(ValueLabels, 1), | |
| Function, IF( ISOMITTED( Function), SUM, Function), | |
| // Procedure | |
| Matches, IF( IgnoreCase?, | |
| SEARCH( FilterByText, Labels), | |
| FIND( FilterByText, Labels) | |
| ), | |
| Filtered, FILTER( ValuesToFilter, ISNUMBER( Matches)), | |
| Result, BYCOL( Filtered, Function), | |
| // Return Result or help | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: MLookupλ | |
| DESCRIPTION:*//**Multiple criteria dynamic array lookup*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 07 2025 Craig Hatmaker Copyright | |
| Mar 15 2025 Craig Hatmaker Fixed FindValues# to accept numbers or dates entered as strings | |
| */ | |
| MLookupλ = LAMBDA( | |
| // Parameter Declarations | |
| [ReturnArray], | |
| [FindValues1], | |
| [InArray1], | |
| [FindValues2], | |
| [InArray2], | |
| [FindValues3], | |
| [InArray3], | |
| [FindValues4], | |
| [InArray4], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →MLookupλ( ReturnArray, FindValues1, InArray1, [FindValues2], [InArray2],¶" & | |
| "→ [FindValues3], [InArray3], [FindValues4], [InArray4]) ¶" & | |
| "DESCRIPTION: →Find values matching 1+ criteria in 1 to 4 rows or columns (but not both).¶" & | |
| "→Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-mlookup%CE%BB¶" & | |
| "VERSION: →BXL: Mar 07 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " ReturnArray →(Required) An array containing items to return¶" & | |
| " FindValues1 →(Required) An array or comma separated string containing values to find in InArray1¶" & | |
| " InArray1 →(Required) A 1 dimensional array with as many rows or columns as ReturnArray to search with FindValues1¶" & | |
| " FindValues2 →(Optional) Same as FindValues1, but for finding in InArray2¶" & | |
| " InArray2 →(Optional) An array of the same dimensions as InArray1, to search with FindValues2¶" & | |
| " FindValues3 →(Optional) Same as FindValues1, but for finding in InArray3¶" & | |
| " InArray3 →(Optional) An array of the same dimensions as InArray1, to search with FindValues3¶" & | |
| " FindValues4 →(Optional) Same as FindValues1, but for finding in InArray4¶" & | |
| " InArray4 →(Optional) An array of the same dimensions as InArray1, to search with FindValues4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ReturnArray), | |
| OR( ISOMITTED( FindValues1), FindValues1=""), | |
| OR( ISOMITTED( InArray1), InArray1="") | |
| ), | |
| // This function attempts to convert FindValues# to an appropriate Matches# argument | |
| FixFindValues, LAMBDA( FindValues, | |
| IF( OR( ISOMITTED(FindValues), LEN( TRIM( FindValues)) = 0), | |
| {""}, | |
| IF( COUNTA( FindValues) = 1, | |
| LET( | |
| Values, TRIM( TEXTSPLIT( FindValues, ",")), | |
| IFERROR( VALUE( Values), Values)), | |
| FindValues) | |
| ) | |
| ), | |
| FindValues1, FixFindValues(FindValues1), | |
| FindValues2, FixFindValues(FindValues2), | |
| FindValues3, FixFindValues(FindValues3), | |
| FindValues4, FixFindValues(FindValues4), | |
| // This function attempts to convert InArray# to an appropriate Matches# argument | |
| FixInArray, LAMBDA( InArray, FindValues, | |
| IF( OR( ISOMITTED( InArray), LEN( TRIM( FindValues)) = 0), | |
| {""}, | |
| InArray) | |
| ), | |
| // IF InArray# or FindValues# are not an array, use {""} | |
| InArray1, FixInArray( InArray1, FindValues1), | |
| InArray2, FixInArray( InArray2, FindValues2), | |
| InArray3, FixInArray( InArray3, FindValues3), | |
| InArray4, FixInArray( InArray4, FindValues4), | |
| // Determine Matches | |
| Matches1, ISNUMBER( MATCH( InArray1, FindValues1, 0)), | |
| Matches2, ISNUMBER( MATCH( InArray2, FindValues2, 0)), | |
| Matches3, ISNUMBER( MATCH( InArray3, FindValues3, 0)), | |
| Matches4, ISNUMBER( MATCH( InArray4, FindValues4, 0)), | |
| // Set Filter | |
| Filter, Matches1 * Matches2 * Matches3 * Matches4, | |
| // Filter | |
| Result, IF( SUM( Filter) > 0, FILTER( ReturnArray, Filter), #N/A), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: XYLookupλ | |
| DESCRIPTION:*//**2-way dynamic array lookup*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 15 2025 Craig Hatmaker Copyright | |
| */ | |
| XYLookupλ=LAMBDA( | |
| // Parameter Declarations | |
| [ReturnArray], | |
| [VFindValues], | |
| [VInArray], | |
| [HFindValues], | |
| [HInArray], | |
| [IfNotFound], | |
| [MatchMode], | |
| [SearchMode], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →XYLookupλ( ReturnArray, VLookupValues, VArray, HLookupValues, HArray,¶" & | |
| "→[IfNotFound], [MatchMode], [SearchMode])¶" & | |
| "DESCRIPTION: →Find values matching 1+ criteria in a column, and 1+ in a header.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/¶" & | |
| "→5g-modeling/5g-component-libraries/5g-array-essentials-library/5g-xylookup%CE%BB¶" & | |
| "VERSION: →BXL: Mar 15 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " ReturnArray →(Required) A 2 dimensional range or table containing values to return¶" & | |
| " VFindValues →(Required) 1+ criteria to find in VInArray¶" & | |
| " VInArray →(Required) A column of values relating to each row in ReturnArray¶" & | |
| " HFindValues →(Required) 1+ critiera to find in HInArray¶" & | |
| " HInArray →(Required) A row of values (normally a header) relating to each column in ReturnArray.¶" & | |
| " IfNotFound →(Optional) What to return when lookups fail: Default is #NA.¶" & | |
| " MatchMode →(Optional) Specify:¶" & | |
| "→0 - (Default) Exact match. If none found, return #NA.¶" & | |
| "→-1- Exact match. If none found, return next smaller item.¶" & | |
| "→1 - Exact match. If none found, return next larger item.¶" & | |
| "→2 - A wildcard match where *, ?, and ~ have special meaning.¶" & | |
| " SearchMode →(Optional) Specify:¶" & | |
| "→1 - (Default) Search from the start¶" & | |
| "→-1- Search from the end¶" & | |
| "→2 - Search list sorted in ascending order. Fails if unsorted.¶" & | |
| "→-2- Search list sorted in descending order. Fails if unsorted.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ReturnArray), | |
| ISOMITTED( VFindValues), | |
| ISOMITTED( VInArray), | |
| ISOMITTED( HFindValues), | |
| ISOMITTED( HInArray) | |
| ), | |
| // Set defaults | |
| IfNotFound, IF( ISOMITTED( IfNotFound), NA(), IfNotFound), | |
| MatchMode, IF( ISOMITTED( MatchMode), 0, MatchMode), | |
| SearchMode, IF( ISOMITTED( SearchMode), 1, SearchMode), | |
| // This function attempts to convert xLookupValues to an appropriate Matches# argument | |
| FixFindValues, LAMBDA( FindValues, | |
| IF( OR( ISOMITTED(FindValues), LEN( TRIM( FindValues)) = 0), | |
| {""}, | |
| IF( COUNTA( FindValues) = 1, | |
| LET( | |
| Values, TRIM( TEXTSPLIT( FindValues, ",")), | |
| IFERROR( VALUE( Values), Values)), | |
| FindValues) | |
| ) | |
| ), | |
| VFindValues, FixFindValues( VFindValues), | |
| HFindValues, FixFindValues( HFindValues), | |
| // Prep | |
| Cols, COLUMNS( ReturnArray), | |
| Rows, ROWS( ReturnArray), | |
| BaseRow, EXPAND( 0, 1, Cols, 0), | |
| BaseCol, EXPAND( 0, Rows, 1, 0), | |
| RowCounter, SEQUENCE( Rows), | |
| ColCounter, SEQUENCE( , Cols), | |
| // Procedure | |
| VMatches, XMATCH( VFindValues, VInArray, MatchMode, SearchMode), | |
| VFilter, N( REDUCE( BaseCol, VMatches, | |
| LAMBDA( Acc, n, | |
| MAP( Acc, n = RowCounter, OR) | |
| ) | |
| ) | |
| ), | |
| HMatches, XMATCH( HFindValues, HInArray, MatchMode, SearchMode), | |
| HFilter, N( REDUCE( BaseRow, HMatches, | |
| LAMBDA( Acc, n, | |
| MAP( Acc, n = ColCounter, OR) | |
| ) | |
| ) | |
| ), | |
| FilteredRows, FILTER( ReturnArray, VFilter), | |
| FilteredCols, FILTER( FilteredRows, HFilter), | |
| Result, IFNA( FilteredCols, IfNotFound), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| // Dynamic array versions of common Excel functions | |
| /* FUNCTION NAME: AndAλ | |
| DESCRIPTION:*//**AND an array by column or by row based on the function's position | |
| (if in the grid) relative to the array*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Naming standard | |
| Sep 24 2025 Craig Hatmaker Simplification | |
| */ | |
| AndAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [ByCol?], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →AvgAλ( Array, [ByCol?])¶" & | |
| "DESCRIPTION: →AVERAGE an array by column or by row based on the function's position¶" & | |
| "→ (if in the grid) relative to the array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-suma%CE%BB¶" & | |
| "VERSION: →BXL: Sep 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array containing values to process¶" & | |
| " ByCol? →(Optional) If placed in the same row as the Array, this defaults to FALSE (BYROW);¶" & | |
| "→ otherwise, this defaults to TRUE. If the Array is not a range, this defaults to TRUE.", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Process | |
| Result, Arrayλ( Array, AND, ByCol?), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: AvgAλ | |
| DESCRIPTION:*//**AVERAGE an array by column or by row based on the function's position | |
| (if in the grid) relative to the array*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Naming standard | |
| Sep 24 2025 Craig Hatmaker Simplification | |
| */ | |
| AvgAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [ByCol?], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →AvgAλ( Array, [ByCol?])¶" & | |
| "DESCRIPTION: →AVERAGE an array by column or by row based on the function's position¶" & | |
| "→ (if in the grid) relative to the array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-suma%CE%BB¶" & | |
| "VERSION: →BXL: Sep 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array containing values to process¶" & | |
| " ByCol? →(Optional) If placed in the same row as the Array, this defaults to FALSE (BYROW);¶" & | |
| "→ otherwise, this defaults to TRUE. If the Array is not a range, this defaults to TRUE.", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Process | |
| Result, Arrayλ( Array, MAX, ByCol?), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: MaxAλ | |
| DESCRIPTION:*//**MAX an array by column or by row based on the function's position | |
| (if in the grid) relative to the array*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Naming standard | |
| Sep 24 2025 Craig Hatmaker Simplification | |
| */ | |
| MaxAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [ByCol?], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →MaxAλ( Array, [ByCol?])¶" & | |
| "DESCRIPTION: →MAX an array by column or by row based on the function's position¶" & | |
| "→ (if in the grid) relative to the array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-suma%CE%BB¶" & | |
| "VERSION: →BXL: Sep 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array containing values to process¶" & | |
| " ByCol? →(Optional) If placed in the same row as the Array, this defaults to FALSE (BYROW);¶" & | |
| "→ otherwise, this defaults to TRUE. If the Array is not a range, this defaults to TRUE.", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Process | |
| Result, Arrayλ( Array, MAX, ByCol?), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: MinAλ | |
| DESCRIPTION:*//**MIN an array by column or by row based on the function's position | |
| (if in the grid) relative to the array*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Naming standard | |
| Sep 24 2025 Craig Hatmaker Simplification | |
| */ | |
| MinAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [ByCol?], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →MinAλ( Array, [ByCol?])¶" & | |
| "DESCRIPTION: →MIN an array by column or by row based on the function's position¶" & | |
| "→ (if in the grid) relative to the array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-suma%CE%BB¶" & | |
| "VERSION: →BXL: Sep 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array containing values to process¶" & | |
| " ByCol? →(Optional) If placed in the same row as the Array, this defaults to FALSE (BYROW);¶" & | |
| "→ otherwise, this defaults to TRUE. If the Array is not a range, this defaults to TRUE.", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Process | |
| Result, Arrayλ( Array, MIN, ByCol?), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: OrAλ | |
| DESCRIPTION:*//**OR an array by column or by row based on the function's position | |
| (if in the grid) relative to the array*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Naming standard | |
| Sep 24 2025 Craig Hatmaker Simplification | |
| */ | |
| OrAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [ByCol?], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →OrAλ( Array, [ByCol?])¶" & | |
| "DESCRIPTION: →ORs an array by column or by row based on the function's position¶" & | |
| "→ (if in the grid) relative to the array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-suma%CE%BB¶" & | |
| "VERSION: →BXL: Sep 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array containing values to process¶" & | |
| " ByCol? →(Optional) If placed in the same row as the Array, this defaults to FALSE (BYROW);¶" & | |
| "→ otherwise, this defaults to TRUE. If the Array is not a range, this defaults to TRUE.", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Process | |
| Result, Arrayλ( Array, OR, ByCol?), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SumAλ | |
| DESCRIPTION:*//**SUM an array by column or by row based on the function's position | |
| (if in the grid) relative to the array*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| May 27 2025 Craig Hatmaker Naming standard | |
| Sep 24 2025 Craig Hatmaker Simplification | |
| */ | |
| SumAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [ByCol?], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →SumAλ( Array,[ByCol?])¶" & | |
| "DESCRIPTION: →SUMs an array by column or by row based on the function's position¶" & | |
| "→ (if in the grid) relative to the array¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-suma%CE%BB¶" & | |
| "VERSION: →BXL: Sep 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array containing values to process¶" & | |
| " ByCol? →(Optional) If placed in the same row as the Array, this defaults to FALSE (BYROW);¶" & | |
| "→ otherwise, this defaults to TRUE. If the Array is not a range, this defaults to TRUE.", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Process | |
| Result, Arrayλ( Array, SUM, ByCol?), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Arrayλ | |
| DESCRIPTION:*//**A LAMBDA helper for applying a function to an array BYCOL or BYROW | |
| based on the function's position relative to the array (if in the grid)*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 24 2025 Craig Hatmaker Copyright | |
| */ | |
| Arrayλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Function], | |
| [ByCol?], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Arrayλ( Array, [Function], [ByCol?])¶" & | |
| "DESCRIPTION: →A LAMBDA helper for applying a function ¶" & | |
| "→ to an array BYCOL or BYROW based on the ¶" & | |
| "→ function's position relative to the array ¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Sep 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array containing values to process¶" & | |
| " Function →(Optional) A function to apply to the array. ¶" & | |
| "→ The default is SUM.¶" & | |
| " ByCol? →(Optional) If placed in the same row as the Array, ¶" & | |
| "→ this defaults to FALSE (BYROW);¶" & | |
| "→ otherwise, this defaults to TRUE. If the Array is ¶" & | |
| "→ not a range, this defaults to TRUE.", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| // Set Default | |
| ByCol?, IF( ISOMITTED( ByCol?), | |
| IF( ISREF( Array), //Is this a range? | |
| ROW() <> @ROW( Array), | |
| TRUE), | |
| ByCol?), | |
| Function, IF( ISOMITTED( Function), SUM, Function), | |
| // Process | |
| Result, IF( ByCol?, | |
| BYCOL( Array, Function), | |
| BYROW( Array, Function) | |
| ), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| // Row Aware functions | |
| /* FUNCTION NAME: ProcessRowλ | |
| DESCRIPTION:*/ /**Apply a function to an array's row that is in this function's row*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 02 2025 Craig Hatmaker Copyright | |
| */ | |
| ProcessRowλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| [Function], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →ProcessRowλ( Array, [Function])¶" & | |
| "DESCRIPTION: →Apply a function to an array's row that is in this function's row¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-row-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to process¶" & | |
| " Function →(Optional) The function to apply to Array's row that is in this function's row¶" & | |
| "→ This defaults to SUM." , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Handle defaults | |
| Function, IF( ISOMITTED( Function), SUM, Function), | |
| // Procedure | |
| RowNum, ROW() - @ROW( Array) + 1, | |
| Row, CHOOSEROWS( Array, RowNum), | |
| Result, Function( Row), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SumRλ | |
| DESCRIPTION:*/ /**SUM an array's row that is in this function's row*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| SumRλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →SumRλ( Array)¶" & | |
| "DESCRIPTION: →SUM an array's row that is in this function's row¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-row-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to SUM" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| RowNum, ROW() - @ROW( Array) + 1, | |
| Row, CHOOSEROWS( Array, RowNum), | |
| Result, SUM( Row), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: AndRλ | |
| DESCRIPTION:*/ /**AND an array's row that is in this function's row*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| AndRλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →AndRλ( Array)¶" & | |
| "DESCRIPTION: →AND an array's row that is in this function's row¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-row-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to AND" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| RowNum, ROW() - @ROW( Array) + 1, | |
| Row, CHOOSEROWS( Array, RowNum), | |
| Result, AND( Row), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: OrRλ | |
| DESCRIPTION:*/ /**OR an array's row that is in this function's row*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| OrRλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →OrRλ( Array)¶" & | |
| "DESCRIPTION: →OR an array's row that is in this function's row¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-row-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to OR" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| RowNum, ROW() - @ROW( Array) + 1, | |
| Row, CHOOSEROWS( Array, RowNum), | |
| Result, OR( Row), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: AvgRλ | |
| DESCRIPTION:*/ /**AVERAGE an array's row that is in this function's row*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| AvgRλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →AvgRλ( Array)¶" & | |
| "DESCRIPTION: →AVERAGE an array's row that is in this function's row¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-row-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to AVERAGE" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| RowNum, ROW() - @ROW( Array) + 1, | |
| Row, CHOOSEROWS( Array, RowNum), | |
| Result, AVERAGE( Row), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: MaxRλ | |
| DESCRIPTION:*/ /**Find the maximum of an array's row that is in this function's row*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| MaxRλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →MaxRλ( Array)¶" & | |
| "DESCRIPTION: →Find the maximum of an array's row that is in this function's row¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-row-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to MAX" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| RowNum, ROW() - @ROW( Array) + 1, | |
| Row, CHOOSEROWS( Array, RowNum), | |
| Result, MAX( Row), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: MinRλ | |
| DESCRIPTION:*/ /**Find the minimum of an array's row that is in this function's row*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| MinRλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →MinRλ( Array)¶" & | |
| "DESCRIPTION: →Find the minimum of an array's row that is in this function's row¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-row-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to MIN" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| RowNum, ROW() - @ROW( Array) + 1, | |
| Row, CHOOSEROWS( Array, RowNum), | |
| Result, MIN( Row), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| // Column Aware functions | |
| /* FUNCTION NAME: ProcessColλ | |
| DESCRIPTION:*/ /**Apply a function to an array's Column that is in this function's column*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 02 2025 Craig Hatmaker Copyright | |
| */ | |
| ProcessColλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| [Function], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →ProcessColλ( Array, [Function])¶" & | |
| "DESCRIPTION: →Apply a function to an array's column that is in this function's column¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-column-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to process¶" & | |
| " Function →(Optional) The function to apply to Array's column that is in this¶" & | |
| "→ function's column. This defaults to SUM." , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Handle defaults | |
| Function, IF( ISOMITTED( Function), SUM, Function), | |
| // Procedure | |
| ColNum, COLUMN() - @COLUMN( Array) + 1, | |
| Col, CHOOSECOLS( Array, ColNum), | |
| Result, Function( Col), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SumCλ | |
| DESCRIPTION:*/ /**SUM an array's column that is in this function's column*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| SumCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →SumCλ( Array)¶" & | |
| "DESCRIPTION: →SUM an array's column that is in this function's column¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-column-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to SUM" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| ColNum, COLUMN() - @COLUMN( Array) + 1, | |
| Col, CHOOSECOLS( Array, ColNum), | |
| Result, SUM( Col), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: AndCλ | |
| DESCRIPTION:*/ /**AND an array's column that is in this function's column*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| AndCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →AndCλ( Array)¶" & | |
| "DESCRIPTION: →AND an array's column that is in this function's column¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-column-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to AND" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| ColNum, COLUMN() - @COLUMN( Array) + 1, | |
| Col, CHOOSECOLS( Array, ColNum), | |
| Result, AND( Col), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: OrColλ | |
| DESCRIPTION:*/ /**OR an array's column that is in this function's column*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| OrCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →OrCλ( Array)¶" & | |
| "DESCRIPTION: →OR an array's column that is in this function's column¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-column-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to OR" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| ColNum, COLUMN() - @COLUMN( Array) + 1, | |
| Col, CHOOSECOLS( Array, ColNum), | |
| Result, OR( Col), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: AvgCλ | |
| DESCRIPTION:*/ /**AVERAGE an array's column that is in this function's column*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| AvgCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →AvgCλ( Array)¶" & | |
| "DESCRIPTION: →AVERAGE an array's column that is in this function's column¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-column-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to AVERAGE" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| ColNum, COLUMN() - @COLUMN( Array) + 1, | |
| Col, CHOOSECOLS( Array, ColNum), | |
| Result, AVERAGE( Col), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: MaxCλ | |
| DESCRIPTION:*/ /**Find the maximum of an array's column that is in this function's column*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| MaxCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →MaxCλ( Array)¶" & | |
| "DESCRIPTION: →Find the maximum of an array's column that is in this function's column¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-column-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to MAX" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| ColNum, COLUMN() - @COLUMN( Array) + 1, | |
| Col, CHOOSECOLS( Array, ColNum), | |
| Result, MAX( Col), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: MinCλ | |
| DESCRIPTION:*/ /**Find the minimum of an array's column that is in this function's column*/ | |
| /* REVISIONS: Date Developer Description | |
| Oct 01 2025 Craig Hatmaker Copyright | |
| */ | |
| MinCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →MinCλ( Array)¶" & | |
| "DESCRIPTION: →Find the minimum of an array's column that is in this function's column¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-column-aware-suite¶" & | |
| "VERSION: →BXL: Oct 02 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to MIN" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| // Procedure | |
| ColNum, COLUMN() - @COLUMN( Array) + 1, | |
| Col, CHOOSECOLS( Array, ColNum), | |
| Result, MIN( Col), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment