Last active
November 8, 2025 02:55
-
-
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 | |
| Nov 07 2025 Craig Hatmaker Major updates | |
| */ | |
| Aboutλ = TRIM(TEXTSPLIT( | |
| "About: →Basic functions for working with dynamic arrays. Suggested module name: BXE¶" & | |
| "Version: →BXL: Nov 04 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¶" & | |
| " Instanceλ →Determine each value's instance within an array.¶" & | |
| " IsBetweenλ →Determine if a value is between a lower and upper limit.¶" & | |
| " 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 up to 4 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.¶" & | |
| "→¶" & | |
| "Multi Lookups →Return arrays that exactly matches up to 4 criteria¶" & | |
| " ListMLλ →List values from an array based on 4 or fewer exact match criteria (helper function)¶" & | |
| " AvgMLλ →Average an array based on 4 or fewer exact match criteria¶" & | |
| " CountMLλ →Average an array based on 4 or fewer exact match criteria¶" & | |
| " MaxMLλ →Average an array based on 4 or fewer exact match criteria¶" & | |
| " MinMLλ →Average an array based on 4 or fewer exact match criteria¶" & | |
| " SumMLλ →Average an array based on 4 or fewer exact match criteria¶" & | |
| "→¶" & | |
| "Array Versions →Array friendly versions of Excel's *IFS functions¶" & | |
| " Compareλ →Determine if a value or array meets a criteria. This is the *Ifs core engine.¶" & | |
| " ListIfsAλ →List values from an array that match up to 4 criteria (helper function)¶" & | |
| " AvgIfsAλ →An array friendly version of AVERAGEIFS()¶" & | |
| " CountIfsAλ →An array friendly version of COUNTIFS()¶" & | |
| " MaxIfsAλ →An array friendly version of MAXIFS()¶" & | |
| " MinIfsAλ →An array friendly version of MINIFS()¶" & | |
| " SumIfsAλ →An array friendly version of SUMIFS()¶" & | |
| "→¶" & | |
| "Array position Aware→A collection of functions applied by row or column based on the function's¶" & | |
| "→ position relative to the array (if in the grid)¶" & | |
| " ProcessAλ →A helper function for processing all columns or rows based on this function's position relative to an array¶" & | |
| " 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)¶" & | |
| "→ to the array (if in the grid)¶" & | |
| "→¶" & | |
| "Array Row Aware →A collection of functions that apply themselves to an array's row that they share¶" & | |
| " ProcessRCλ →A helper function for processing a column or row based on this function's position relative to an array¶" & | |
| " AndRCλ →AND an array's row that this function shares.¶" & | |
| " AvgRCλ →AVERAGE an array's row that this function shares.¶" & | |
| " MaxRCλ →MAX an array's row that this function shares.¶" & | |
| " MinRCλ →MIN an array's row that this function shares.¶" & | |
| " OrRCλ →OR an array's row that this function shares.¶" & | |
| " SumRCλ →SUM an array's row that this function shares.¶" & | |
| "→¶" & | |
| "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)", | |
| "→","¶" | |
| ) | |
| ); | |
| /* 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?, ISOMITTED( Values), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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)))), | |
| Result | |
| ) | |
| ), | |
| 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) | |
| ), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // Procedure | |
| Result, IF( Inclusive, | |
| (Values >= Low) * (Values <= High) = 1, | |
| (Values > Low) * (Values < High) = 1 | |
| ), | |
| Result | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* 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) | |
| ), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // Procedure | |
| Result, NOT( ISNA( MATCH( Value, List, 0))), | |
| Result | |
| ) | |
| ), | |
| 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), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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, | |
| Result | |
| ) | |
| ), | |
| 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), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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), | |
| Result | |
| ) | |
| ), | |
| 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), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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), ""), | |
| Result | |
| ) | |
| ), | |
| 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), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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))) | |
| ), | |
| Result | |
| ) | |
| ), | |
| 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), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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))) | |
| ), | |
| Result | |
| ) | |
| ), | |
| 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)), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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)), | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* 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)), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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), | |
| Result | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* 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="") | |
| ), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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), | |
| Result | |
| ) | |
| ), | |
| 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) | |
| ), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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), | |
| Result | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| // Multi-list | |
| /* FUNCTION NAME: ListMLλ | |
| DESCRIPTION:*//**Return values from array that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| ListMLλ = LAMBDA( | |
| [ArrayToReturn], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| [Function], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ListMLλ( ArrayToReturn, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4], [Function]) ¶" & | |
| "DESCRIPTION: →Return values from array that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-listifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToReturn →(Required) An array containing items to be returned¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4¶" & | |
| " Function →(Optional) A function to process the returned array.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToReturn), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| Array, IF( OR( ISOMITTED( CriteriaArray2), CriteriaArray2 = ""), | |
| MLookupλ( | |
| ArrayToReturn, | |
| Criteria1, CriteriaArray1), | |
| IF( OR( ISOMITTED( CriteriaArray3), CriteriaArray3 = ""), | |
| MLookupλ( | |
| ArrayToReturn, | |
| Criteria1, CriteriaArray1, | |
| Criteria2, CriteriaArray2), | |
| IF( OR( ISOMITTED( CriteriaArray4), CriteriaArray4 = ""), | |
| MLookupλ( | |
| ArrayToReturn, | |
| Criteria1, CriteriaArray1, | |
| Criteria2, CriteriaArray2, | |
| Criteria3, CriteriaArray3), | |
| MLookupλ( | |
| ArrayToReturn, | |
| Criteria1, CriteriaArray1, | |
| Criteria2, CriteriaArray2, | |
| Criteria3, CriteriaArray3, | |
| Criteria4, CriteriaArray4) | |
| ) | |
| ) | |
| ), | |
| Result, IF( ISOMITTED( Function), Array, Function(Array)), | |
| Result | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: AvgMLλ | |
| DESCRIPTION:*//**Average an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| AvgMLλ = LAMBDA( | |
| [ArrayToAvg], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →AvgMLλ( ArrayToAvg, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Average an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-avgifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToSum →(Required) An array containing items to average¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToAvg), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListIfsAλ( ArrayToAvg, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| AVERAGE | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: CountMLλ | |
| DESCRIPTION:*//**Count an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| CountMLλ = LAMBDA( | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →CountMLλ( ArrayToCount, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Count an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-avgifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( CriteriaArray1), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListMLλ( CriteriaArray1, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| COUNTA | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: MaxMLλ | |
| DESCRIPTION:*//**Find the maximum in an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| MaxMLλ = LAMBDA( | |
| [ArrayToMax], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →MaxMLλ( ArrayToMax, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Find the maximum in an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-maxifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToMax →(Required) An array containing items to find the maxiumum in¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToMax), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListMLλ( ArrayToMax, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| MAX | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: MinMLλ | |
| DESCRIPTION:*//**Find the minimum in an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| MinMLλ = LAMBDA( | |
| [ArrayToMin], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →MinMLλ( ArrayToMin, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Find the Minimum in an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-minifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToMin →(Required) An array containing items to find the miniumum in¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToMin), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListMLλ( ArrayToMin, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| MIN | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: SumMLλ | |
| DESCRIPTION:*//**Total an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| SumMLλ = LAMBDA( | |
| [ArrayToSum], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →SumMLλ( ArrayToAdd, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Total an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-sumifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToSum →(Required) An array containing items to total¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToSum), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListMLλ( ArrayToSum, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| SUM | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| // Dynamic array versions of common Excel functions that only accept ranges | |
| /* FUNCTION NAME: Compareλ | |
| DESCRIPTION:*//**Determine if a value or array meets a criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| Compareλ = LAMBDA( | |
| [Array], | |
| [Criteria], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Compareλ( Array, Criteria) ¶" & | |
| "DESCRIPTION: →Determine if a value or array meets the criteria¶" & | |
| "→This includes: =, >, <, >=, <=, and wildcard matches (*, ?)¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-compare%CE%BB¶" & | |
| "VERSION: →BXL: Nov 03 25¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) A value or array to compare against criteria¶" & | |
| " Criteria →(Required) Criteria to compare to Array. ¶" & | |
| "→ Exact and wildcard matches do not require an equals sign.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| ISOMITTED( Criteria) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // Declare function to create REGEX wildcard search (if needed) | |
| fnWildCard, LAMBDA( | |
| Compare, | |
| LET( | |
| Equals, REGEXREPLACE( Compare, "\=", ""), | |
| Asterisk, REGEXREPLACE( Equals, "\*", ".*"), | |
| Question, REGEXREPLACE( Asterisk, "\?", "."), | |
| Result, "^" & Question, | |
| Result | |
| ) | |
| ), | |
| // Determine comparison operator (if any) | |
| Operator, IF( REGEXTEST( Criteria, "\*|\?", 1), | |
| "WC", | |
| IFNA( REGEXEXTRACT(Criteria, ">=|<=|<|>|="), "") | |
| ), | |
| // Remove operator from criteria | |
| CritValue, TRIM( SUBSTITUTE( Criteria, Operator, "")), | |
| // Convert numbers as text to numbers | |
| Compare, IFERROR( VALUE( CritValue), CritValue), | |
| // Convert Operator to number to use more efficient CHOOSE instead of SWITCH | |
| OprNum, IFNA( XMATCH( Operator, {"<=",">=",">","<","=","WC"}, 0), 5), | |
| // Perform comparison | |
| Result, CHOOSE( OprNum, | |
| Array <=Compare, | |
| Array >=Compare, | |
| Array > Compare, | |
| Array < Compare, | |
| Array = Compare, | |
| REGEXTEST(Array, fnWildCard( CritValue), 1) | |
| ), | |
| Result | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: ListIfsAλ | |
| DESCRIPTION:*//**Return values from array that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| ListIfsAλ = LAMBDA( | |
| [ArrayToReturn], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| [Function], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ListIfsAλ( ArrayToReturn, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4], [Function]) ¶" & | |
| "DESCRIPTION: →Return values from array that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-listifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToReturn →(Required) An array containing items to be returned¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4¶" & | |
| " Function →(Optional) A function to process the returned array.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToReturn), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| Matches, IF( OR( ISOMITTED( CriteriaArray2), AND(TRIM(CriteriaArray2)="")), | |
| Compareλ( CriteriaArray1, Criteria1), | |
| IF( OR( ISOMITTED( CriteriaArray3), AND(TRIM(CriteriaArray3)="")), | |
| Compareλ( CriteriaArray1, Criteria1) * | |
| Compareλ( CriteriaArray2, Criteria2), | |
| IF( OR( ISOMITTED( CriteriaArray4), AND(TRIM(CriteriaArray4)="")), | |
| Compareλ( CriteriaArray1, Criteria1) * | |
| Compareλ( CriteriaArray2, Criteria2) * | |
| Compareλ( CriteriaArray3, Criteria3), | |
| Compareλ( CriteriaArray1, Criteria1) * | |
| Compareλ( CriteriaArray2, Criteria2) * | |
| Compareλ( CriteriaArray3, Criteria3) * | |
| Compareλ( CriteriaArray4, Criteria4) | |
| ) | |
| ) | |
| ), | |
| Array, FILTER( ArrayToReturn, Matches), | |
| Result, IFERROR( IF( ISOMITTED( Function), Array, Function(Array)), #N/A), | |
| Function(Array) | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: AvgIfsAλ | |
| DESCRIPTION:*//**Average an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| AvgIfsAλ = LAMBDA( | |
| [ArrayToAvg], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →AvgIfsAλ( ArrayToAvg, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Average an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-avgifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToSum →(Required) An array containing items to average¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToAvg), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListIfsAλ( ArrayToAvg, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| AVERAGE | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: CountIfsAλ | |
| DESCRIPTION:*//**Count an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| CountIfsAλ = LAMBDA( | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →CountIfsAλ( ArrayToCount, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Count an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-avgifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( CriteriaArray1), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListIfsAλ( CriteriaArray1, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| COUNTA | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: MaxIfsAλ | |
| DESCRIPTION:*//**Find the maximum in an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| MaxIfsAλ = LAMBDA( | |
| [ArrayToMax], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →MaxIfsAλ( ArrayToMax, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Find the maximum in an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-maxifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToMax →(Required) An array containing items to find the maxiumum in¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToMax), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListIfsAλ( ArrayToMax, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| MAX | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: MinIfsAλ | |
| DESCRIPTION:*//**Find the minimum in an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| MinIfsAλ = LAMBDA( | |
| [ArrayToMin], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →MinIfsAλ( ArrayToMin, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Find the Minimum in an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-minifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToMin →(Required) An array containing items to find the miniumum in¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToMin), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListIfsAλ( ArrayToMin, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| MIN | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: SumIfsAλ | |
| DESCRIPTION:*//**Total an array's values that match up to 4 different criteria.*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 01 2025 Craig Hatmaker Copyright | |
| */ | |
| SumIfsAλ = LAMBDA( | |
| [ArrayToSum], | |
| [CriteriaArray1], | |
| [Criteria1], | |
| [CriteriaArray2], | |
| [Criteria2], | |
| [CriteriaArray3], | |
| [Criteria3], | |
| [CriteriaArray4], | |
| [Criteria4], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →SumIfsAλ( ArrayToAdd, CriteriaArray1, Criteria1, [CriteriaArray2], [Criteria2],¶" & | |
| "→ [CriteriaArray3], [Criteria3], [CriteriaArray4], [Criteria4]) ¶" & | |
| "DESCRIPTION: →Total an array's values that match up to 4 different criteria.¶" & | |
| "→Exact matches only. Return #N/A if nothing found.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-sumifs%CE%BB¶" & | |
| "VERSION: →BXL: Nov 01 25¶" & | |
| "PARAMETERS: →¶" & | |
| " ArrayToSum →(Required) An array containing items to total¶" & | |
| " CriteriaArray1→(Required) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria1¶" & | |
| " Criteria1 →(Required) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray1¶" & | |
| " CriteriaArray2→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria2¶" & | |
| " Criteria2 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray3→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria3¶" & | |
| " Criteria3 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray2¶" & | |
| " CriteriaArray4→(Optional) An array column or row with as many columns or rows as ArrayToSum¶" & | |
| "→ that is tested by Criteria4¶" & | |
| " Criteria4 →(Optional) An array row or column, or a comma separated list of values ¶" & | |
| "→ to find in CriteriaArray4", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ArrayToSum), | |
| OR( ISOMITTED( Criteria1), TRIM(Criteria1)=""), | |
| OR( ISOMITTED( CriteriaArray1), AND(TRIM(CriteriaArray1)="")) | |
| ), | |
| // Procedure | |
| Result, IF( Help?, | |
| Help, | |
| ListIfsAλ( ArrayToSum, | |
| CriteriaArray1, Criteria1, | |
| CriteriaArray2, Criteria2, | |
| CriteriaArray3, Criteria3, | |
| CriteriaArray4, Criteria4, | |
| SUM | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| // Dynamic array and position aware versions of common Excel functions | |
| /* FUNCTION NAME: ProcessAλ | |
| DESCRIPTION:*//**Apply a function to all array rows or columns | |
| based on the function's position relative to the array (if in the grid)*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 24 2025 Craig Hatmaker Copyright | |
| */ | |
| ProcessAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Function], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ProcessAλ( Array, [Function])¶" & | |
| "DESCRIPTION: →Apply a function to all array rows (if beside ) or columns (if above or below)¶" & | |
| "→This is a helper function¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "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.", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // Set Default | |
| ByRow?, IF( ISREF( Array), | |
| ROW() = @ROW( Array), | |
| FALSE), | |
| Function, IF( ISOMITTED( Function), SUM, Function), | |
| // Process | |
| Result, IF( ByRow?, | |
| BYROW( Array, Function), | |
| BYCOL( Array, Function) | |
| ), | |
| Result | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: AndAλ | |
| DESCRIPTION:*/ /**AND all of an array's rows or columns*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| AndAλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →AndAλ( Array)¶" & | |
| "DESCRIPTION: →AND all of an array's rows (if beside) or columns (if above or below)¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to AND", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessAλ( Array, AND)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: AvgAλ | |
| DESCRIPTION:*/ /**AVERAGE all of an array's rows or columns*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| AvgAλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →AvgAλ( Array)¶" & | |
| "DESCRIPTION: →AVERAGE all of an array's rows (if beside) or columns (if above or below)¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to AVERAGE" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessAλ( Array, AVERAGE)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: MaxAλ | |
| DESCRIPTION:*/ /**Find the MAX of all of an array's rows or columns*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| MaxAλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →MaxAλ( Array)¶" & | |
| "DESCRIPTION: →Find the MAX of all of an array's rows (if beside) or columns (if above or below)¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to MAX" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessAλ( Array, MAX)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: MinAλ | |
| DESCRIPTION:*/ /**Find the MIN of all of an array's rows or columns*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| MinAλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →MinAλ( Array)¶" & | |
| "DESCRIPTION: →Find the MIN of all of an array's rows (if beside) or columns (if above or below)¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to MIN" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessAλ( Array, MIN)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: OrAλ | |
| DESCRIPTION:*/ /**OR all of an array's rows or columns*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| ORAλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →OrAλ( Array)¶" & | |
| "DESCRIPTION: →OR all of an array's rows (if beside) or columns (if above or below)¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to OR" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessAλ( Array, OR)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: SumAλ | |
| DESCRIPTION:*/ /**SUM all of an array's rows or columns*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| SumAλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →SumAλ( Array)¶" & | |
| "DESCRIPTION: →SUM all of an array's rows (if beside) or columns (if above or below)¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to SUM" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessAλ( Array, SUM)), | |
| Result | |
| ) | |
| ); | |
| // Array Row or column aware functions | |
| /* FUNCTION NAME: ProcessRCλ | |
| DESCRIPTION:*/ /**Array position aware function for processing a single row or column*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| ProcessRCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| [Function], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →ProcessRCλ( Array, [Function])¶" & | |
| "DESCRIPTION: →Process array row or column. This is a helper function ¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "→This processes a single array row (if beside) or column (if above or below).¶" & | |
| "→Returns #VALUE! when not in the array's rows or columns¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to process¶" & | |
| " Function →(Optional) The function to apply to the Array's row (if beside array)¶" & | |
| "→ or column(if above or below). This defaults to SUM." , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Array), | |
| NOT( ISREF( Array))), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // Handle defaults | |
| Function, IF( ISOMITTED( Function), SUM, Function), | |
| // Procedure | |
| ByRow?, IF( ISREF( Array), | |
| (ROW() >= @ROW( Array)) * (ROW() <= (@ROW( Array) + ROWS( Array) - 1)), | |
| FALSE), | |
| RCNum, IF( ByRow?, | |
| ROW() - @ROW( Array) + 1, | |
| COLUMN() - @COLUMN( Array) + 1), | |
| RowOrCol, IF( ByRow?, | |
| CHOOSEROWS( Array, RCNum), | |
| CHOOSECOLS( Array, RCNum)), | |
| Result, Function( RowOrCol), | |
| Result | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: AndRCλ | |
| DESCRIPTION:*/ /**AND an array's row or column*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| AndRCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →AndRCλ( Array)¶" & | |
| "DESCRIPTION: →AND an array row or column. ¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "→This processes a single array row (if beside) or column (if above or below).¶" & | |
| "→Returns #VALUE! when not in the array's rows or columns¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to AND" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessRCλ( Array, AND)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: AvgRCλ | |
| DESCRIPTION:*/ /**AVERAGE an array's row or column*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| AvgRCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →AvgRCλ( Array)¶" & | |
| "DESCRIPTION: →AVERAGE an array row or column. ¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "→This processes a single array row (if beside) or column (if above or below).¶" & | |
| "→Returns #VALUE! when not in the array's rows or columns¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to AVERAGE" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessRCλ( Array, AVERAGE)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: MaxRCλ | |
| DESCRIPTION:*/ /**MAX an array's row or column*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| MaxRCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →MaxRCλ( Array)¶" & | |
| "DESCRIPTION: →Find the maximum value in an array row or column. ¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "→This processes a single array row (if beside) or column (if above or below).¶" & | |
| "→Returns #VALUE! when not in the array's rows or columns¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to MAX" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessRCλ( Array, MAX)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: MinRCλ | |
| DESCRIPTION:*/ /**MIN an array's row or column*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| MinRCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →MinRCλ( Array)¶" & | |
| "DESCRIPTION: →Find the minimum value in an array row or column. ¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "→This processes a single array row (if beside) or column (if above or below).¶" & | |
| "→Returns #VALUE! when not in the array's rows or columns¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to Min" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessRCλ( Array, MIN)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: OrRCλ | |
| DESCRIPTION:*/ /**AND an array's row or column*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| OrRCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →OrRCλ( Array)¶" & | |
| "DESCRIPTION: →OR an array row or column. ¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "→This processes a single array row (if beside) or column (if above or below).¶" & | |
| "→Returns #VALUE! when not in the array's rows or columns¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to OR" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessRCλ( Array, OR)), | |
| Result | |
| ) | |
| ); | |
| /* FUNCTION NAME: SumRCλ | |
| DESCRIPTION:*/ /**SUM an array's row or column*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 03 2025 Craig Hatmaker Copyright | |
| */ | |
| SumRCλ = LAMBDA( | |
| // Arguments | |
| [Array], | |
| LET( | |
| // Help | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →SumRCλ( Array)¶" & | |
| "DESCRIPTION: →Total an array row or column. ¶" & | |
| "→This is part of the array position aware suite of functions¶" & | |
| "→This processes a single array row (if beside) or column (if above or below).¶" & | |
| "→Returns #VALUE! when not in the array's rows or columns¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/ ¶" & | |
| "→5g-component-libraries/5g-array-essentials-library/5g-array-position-aware-suite¶" & | |
| "VERSION: →BXL: Nov 03 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array →(Required) An array in the grid (cell reference) to SUM" , | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array), | |
| Result, IF( Help?, Help, ProcessRCλ( Array, SUM)), | |
| 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)), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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), | |
| Result | |
| ) | |
| ), | |
| 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)), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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), | |
| Result | |
| ) | |
| ), | |
| 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)), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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)), | |
| Result | |
| ) | |
| ), | |
| 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)), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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), | |
| Result | |
| ) | |
| ), | |
| 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)), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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, | |
| Result | |
| ) | |
| ), | |
| 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 | |
| Nov 04 2025 Craig Hatmaker Made RollRows? position aware | |
| */ | |
| 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?), ROW() <> @ROW(Array), 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 | |
| ) | |
| ), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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 | |
| ) | |
| ) | |
| ), | |
| Result | |
| ) | |
| ), | |
| 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 | |
| Nov 04 2025 Craig Hatmaker Made RollRows? position aware | |
| */ | |
| RollAvgλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| [RollRows?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RollAvgλ(Array, [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( Array), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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?), ROW() <> @ROW(Array), RollRows?), | |
| // Procedure | |
| Result, Rollingλ( Array, Size, Offset, AllowPartial, AVERAGE, RollRows?), | |
| Result | |
| ) | |
| ), | |
| 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 | |
| Nov 04 2025 Craig Hatmaker Made RollRows? position aware | |
| */ | |
| RollMaxλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| [RollRows?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RollMaxλ(Array, [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( Array), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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?), ROW() <> @ROW(Array), RollRows?), | |
| // Procedure | |
| Result, Rollingλ( Array, Size, Offset, AllowPartial, MAX, RollRows?), | |
| Result | |
| ) | |
| ), | |
| 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 | |
| Nov 04 2025 Craig Hatmaker Made RollRows? position aware | |
| */ | |
| RollMinλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| [RollRows?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RollMinλ(Array, [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( Array), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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?), ROW() <> @ROW(Array), RollRows?), | |
| // Procedure | |
| Result, Rollingλ( Array, Size, Offset, AllowPartial, MIN, RollRows?), | |
| Result | |
| ) | |
| ), | |
| 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 | |
| Nov 04 2025 Craig Hatmaker Made RollRows? position aware | |
| */ | |
| RollSumλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| [RollRows?], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RollSumλ(Array, [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( Array), | |
| Result, IF( Help?, | |
| Help, | |
| LET( | |
| // 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?), ROW() <> @ROW(Array), RollRows?), | |
| // Procedure | |
| Result, Rollingλ( Array, Size, Offset, AllowPartial, SUM, RollRows?), | |
| Result | |
| ) | |
| ), | |
| Result | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment