Last active
April 6, 2025 07:37
-
-
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 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 | |
| */ | |
| Aboutλ = TRIM(TEXTSPLIT( | |
| "About: →Basic functions for working with dynamic arrays. Suggested module name: BXE¶" & | |
| "Version: →BXL: Mar 24 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¶" & | |
| " CountCλ →Count how many of one or more characters are in a text string¶" & | |
| " IsBetweenλ →Determine if a value is between a lower and upper limit.¶" & | |
| " Instanceλ →Determine each value's instance within an array.¶" & | |
| " IsInListλ →Determine if a value is one of a list of items.¶" & | |
| " RangeToDAλ →Convert a range reference to a dynamic array reference¶" & | |
| " RunTotColsλ →Create a running total for values in each column¶" & | |
| " RunTotRowsλ →Create a running total for values in each row¶" & | |
| "→¶" & | |
| "Lookups →Lookup functions we wish Excel had¶" & | |
| " MLookupλ →Find values matching 1+ criteria in 1 to 4 rows or columns (but not both).¶" & | |
| " XYLookupλ →Find values matching 1+ criteria in a column, and 1+ in a header.¶" & | |
| "→¶" & | |
| "Rolling Suite →Functions that apply to a window over an array.¶" & | |
| " Rollingλ →Create a window rolling over a row to apply a function to¶" & | |
| " RollingAvgλ →Rolling AVERAGE¶" & | |
| " RollingMaxλ →Rolling MAX¶" & | |
| " RollingMinλ →Rolling MIN¶" & | |
| " RollingSumλ →Rolling SUM¶" & | |
| "→¶" & | |
| "DAFs →Dynamic array versions of common Excel functions¶" & | |
| " Andλ →A dynamic array version of AND() that processes each row or column¶" & | |
| " Avgλ →A dynamic array version of AVERAGE() that processes each row or column¶" & | |
| " Maxλ →A dynamic array version of MAX() that processes each row or column¶" & | |
| " Minλ →A dynamic array version of MIN() that processes each row or column¶" & | |
| " Orλ →A dynamic array version of OR() that processes each row or column¶" & | |
| " Sumλ →A dynamic array version of SUM() that processes each row or column¶" & | |
| " SumAλ →An alias for SUMλ that is easier to pick from Excel's dropdown¶" & | |
| " ArrayVersionPrep →Prep Array1 plus nothing, a scalor, or another array before applying a function", | |
| "→","¶" | |
| ) | |
| ); | |
| /* FUNCTION NAME: CountCλ | |
| DESCRIPTION:*//**Count occurences of character(s) in a string*/ | |
| /* REVISIONS: Date Developer Description | |
| Feb 23 2024 Craig Hatmaker Copyright | |
| */ | |
| CountCλ = LAMBDA( | |
| // Parameter Declarations | |
| [Text], | |
| [Characters], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →CountCλ( Text, Characters)¶" & | |
| "DESCRIPTION: →Count how many of one or more characters are in a string.¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Feb 23 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " Text →(Required) The string to interrogate¶" & | |
| " Characters →(Required) A single character, or CSV of characters to find in Text¶" & | |
| "→NOTE! Case sensitive¶" & | |
| "EXAMPLES: →¶" & | |
| " Formula →=CountCλ( ""Mary had a little lamb"", ""a,b"")¶" & | |
| " Result →5", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Text), | |
| ISOMITTED( Characters) | |
| ), | |
| // Set defaults | |
| Characters, TEXTSPLIT( Characters, "," ), | |
| // Procedure | |
| Result, LEN(Text) - SUM(LEN(TEXTSPLIT( Text, Characters))), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Instanceλ | |
| DESCRIPTION:*//**Determine each value's instance within an array*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 29 2024 Craig Hatmaker Copyright | |
| */ | |
| Instanceλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Instanceλ( Values)¶" & | |
| "DESCRIPTION: →Determine each value's instance within an array¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Nov 29 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " Values →(Required) A row or column of values.¶" & | |
| "EXAMPLES :→¶" & | |
| " Formula: →=Instanceλ({2,3,3,4,1,3,3,4,1,1})¶" & | |
| " Result: →1,1,2,1,1,3,4,2,2,3", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Values)), | |
| // Procedure | |
| Elements, COUNTA( Values), | |
| Counter, IF( ROWS( Values) > 1, SEQUENCE( Elements), SEQUENCE( , Elements)), | |
| Result, MAP( Values, Counter, | |
| LAMBDA( Val, n, | |
| SUM(( Val = Values) * (Counter <= n)))), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: IsBetweenλ | |
| DESCRIPTION:*//**Determine if a value is between a lower and upper limit*/ | |
| /* REVISIONS: Date Developer Description | |
| Jun 01 2022 Craig Hatmaker Copyright | |
| Apr 12 2023 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: Feb 24 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)¶" & | |
| "EXAMPLES: →¶" & | |
| " Result →Formula¶" & | |
| " FALSE →=IsBetweenλ(1, 2, 4)¶" & | |
| " TRUE →=IsBetweenλ(Today(), Today(), Today()+1)¶" & | |
| " FALSE →=IsBetweenλ(""B"", ""B"", ""D"", FALSE)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Set optional arguments defaults | |
| Inclusive, IF(ISLOGICAL(Inclusive), Inclusive, TRUE), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( | |
| ISOMITTED(Values), | |
| ISOMITTED(Low), | |
| ISOMITTED(High) | |
| ), | |
| // Procedure | |
| Result, IF( Inclusive, | |
| (Values >= Low) * (Values <= High) = 1, | |
| (Values > Low) * (Values < High) = 1 | |
| ), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: IsInListλ | |
| DESCRIPTION:*//**Determine if a value is between a lower and upper limit*/ | |
| /* REVISIONS: Date Developer Description | |
| Jun 18 2023 Craig Hatmaker Copyright | |
| Feb 28 2024 Craig Hatmaker Updated to new standards | |
| */ | |
| 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: →<coming soon>¶" & | |
| "VERSION: →BXL: Nov 29 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " Value →(Required) A value or array of values to compare.¶" & | |
| " List →(Required) A list of values to check¶" & | |
| "→¶" & | |
| "EXAMPLES :→¶" & | |
| " Result →Formula¶" & | |
| " FALSE → =IsInListλ(1, {2,3,4})¶" & | |
| " TRUE → =IsInListλ(1, {1,3,5})¶" & | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Value), | |
| ISOMITTED( LIST) | |
| ), | |
| // Procedure | |
| Result, NOT( ISNA( MATCH( Value, List, 0))), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RangeToDAλ | |
| DESCRIPTION:*//**Convert a range reference to a dynamic array reference*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 19,2023 Craig Hatmaker Copyright | |
| */ | |
| 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: →<coming soon>¶" & | |
| "VERSION: →BXL: Nov 29 2024¶" & | |
| "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.¶" & | |
| "→¶" & | |
| "EXAMPLES: →=RangeToDAλ(A1, , ModelPeriodCount)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Range), | |
| // Check inputs - Set optional arguments' defaults | |
| Rows, IF( ISNUMBER( Rows), Rows, 1), | |
| Columns, IF( ISNUMBER( Columns), Columns, 1), | |
| // Procedure | |
| Result, OFFSET( Range, 0, 0, Rows, Columns), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Rollingλ | |
| DESCRIPTION:*//**Create a window rolling over a row to apply a function to*/ | |
| /* REVISIONS: Date Developer Description | |
| Feb 23 2024 Craig Hatmaker Copyright | |
| */ | |
| Rollingλ = LAMBDA( | |
| // Parameter Declarations | |
| [Row], | |
| [Size], | |
| [Offset], | |
| [AllowPartial], | |
| [Function], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Rollingλ(Row, [Size], [Offset], [Function])¶" & | |
| "DESCRIPTION: →Moves a window across a row and applies a function to the window¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Nov 29 2024¶" & | |
| "PARAMETERS: →¶" & | |
| " Row →(Required) A row or values to roll the window over.¶" & | |
| " Size →(Optional) Window's size (number of values selected). Must be > 0. Defaults to 3.¶" & | |
| " Offset →(Optional) Number of values to the right (+) or Left (-) of current position. Defaults to -2.¶" & | |
| " AllowPartial →(Optional) TRUE (default) Apply function to window that starts before, or ends after row's start and end.¶" & | |
| " Function →(Optional) An Excel function accepting an array of values or a LAMBDA. Default is SUM()¶" & | |
| "→¶" & | |
| "EXAMPLES: →=Rollingλ(SEQUENCE( ,10), , , AVERAGE)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Row), | |
| // Check inputs - Set optional arguments' defaults | |
| Size, IF( ISOMITTED( Size), 3, Size), | |
| Offset, IF( ISOMITTED( Offset), -2, Offset), | |
| AllowPartial, IF( ISOMITTED( AllowPartial), TRUE, AllowPartial), | |
| // Procedure | |
| Elements, COLUMNS( Row), | |
| Counter, SEQUENCE( , Elements, 0), | |
| Result, MAP( Counter, | |
| LAMBDA( | |
| n, | |
| LET(Right, n + Offset + Size - 1, | |
| Left, n + Offset, | |
| Window, TAKE( TAKE( Row, , Right) , , -(MIN(Right, Elements) - Left + 1)), | |
| 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//Textjoin(",", , left, Right, MIN(Right, Elements) - Left + 1) | |
| ) | |
| ) | |
| ), | |
| // Return Result or help | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RunTotColsλ | |
| DESCRIPTION:*//**Create a running total for values in each column of an array*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 05 2024 Craig Hatmaker Initial development w/help from Diarmuid Early | |
| */ | |
| RunTotColsλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| [OpeningValues], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RunTotColsλ( Values, [OpeningValues])¶" & | |
| "DESCRIPTION: →Create a running total for values in each column of an array¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "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.¶" & | |
| "EXAMPLES :→¶" & | |
| " Formula: →=RunTotColsλ( SEQUENCE(3,4))¶" & | |
| " Result: →01,02,03,04;¶" & | |
| "→06,08,10,12;¶" & | |
| "→15,18,21,24", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Values)), | |
| // Set Defaults | |
| OpeningValues, IF( ISOMITTED( OpeningValues), 0, OpeningValues), | |
| // Procedure | |
| Values, TRANSPOSE( Values), | |
| Scanned, SCAN( 0, Values, SUM), | |
| /* LET step Scanned produces a running total of everything. | |
| LET step RowReset fixes the first value in each row */ | |
| RowReset, Scanned - DROP( VSTACK( 0, TAKE( Scanned, , -1)), -1), | |
| Result, TRANSPOSE( OpeningValues + RowReset), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RunTotRowsλ | |
| DESCRIPTION:*//**Create a running total for values in each row of an array*/ | |
| /* REVISIONS: Date Developer Description | |
| Sep 05 2024 Craig Hatmaker Initial development w/help from Diarmuid Early | |
| */ | |
| RunTotRowsλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| [OpeningValues], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →RunTotRowsλ( Values, [OpeningValues])¶" & | |
| "DESCRIPTION: →Create a running total for values in each row of an array¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "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.¶" & | |
| "EXAMPLES :→¶" & | |
| " Formula: →=RunTotRowsλ( SEQUENCE(3,4))¶" & | |
| " Result: →01,03,06,10;¶" & | |
| "→05,11,18,26;¶" & | |
| "→09,19,30,42", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Values)), | |
| // Set Defaults | |
| OpeningValues, IF( ISOMITTED( OpeningValues), 0, OpeningValues), | |
| // Procedure | |
| Scanned, SCAN( 0, Values, SUM), | |
| /* LET step Scanned produces a running total of everything. | |
| LET step RowReset fixes the first value in each row */ | |
| RowReset, Scanned - DROP( VSTACK( 0, TAKE( Scanned, , -1)), -1), | |
| Result, OpeningValues + RowReset, | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* 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¶" & | |
| "→¶" & | |
| "EXAMPLE: →Assume we have this table named: tblFruit¶" & | |
| " Data →Fruit Color Origin Price¶" & | |
| "→Apples Red USA 1.50¶" & | |
| "→Pears Golden GTM 1.00¶" & | |
| "→Apples Golden PE 1.25¶" & | |
| "→Grapes Red MX 1.35¶" & | |
| "→Grapes Golden PE 1.00¶" & | |
| " Scenario: →Find the price of red apples¶" & | |
| " Formula: →=MLookupλ(tblFruit[Price], ""red"", tblFruit[Color], ""Apples"", tblFruit[Fruit])¶" & | |
| " Result →1.50¶" & | |
| " Scenario: →Find the price of red apples or grapes¶" & | |
| " Formula: →=MLookupλ(tblFruit[Price], ""Red"", tblFruit[Color],""Apples,Grapes"", tblFruit[Fruit])¶" & | |
| " Result →1.50;1.35¶" & | |
| " Scenario: →Find the price of red or golden apples or pears¶" & | |
| " Formula: →=MLookupλ(tblFruit[Price], ""Red,Golden"", tblFruit[Color],""Apples,Pears"", tblFruit[Fruit])¶" & | |
| " Result →1.50; 1.00; 1.25¶" & | |
| " Scenario: →Return rows for Apples or Grapes that are Red or Green and come from Peru, Guatamala, or USA¶" & | |
| " Formula: →=MLookupλ(tblFruit[Price], ""Red,Golden"", tblFruit[Color],""Apples,Pears"", tblFruit[Fruit])¶" & | |
| " Result →Apples,Red,USA,1.50", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ReturnArray), | |
| OR( ISOMITTED( FindValues1), FindValues1=""), | |
| OR( ISOMITTED( InArray1), InArray1="") | |
| ), | |
| // This function attempts to convert FindValues# to an appropriate Matches# argument | |
| FixFindValues, LAMBDA( FindValues, | |
| IF( OR( ISOMITTED(FindValues), LEN( TRIM( FindValues)) = 0), | |
| {""}, | |
| IF( COUNTA( FindValues) = 1, | |
| LET( | |
| Values, TRIM( TEXTSPLIT( FindValues, ",")), | |
| IFERROR( VALUE( Values), Values)), | |
| FindValues) | |
| ) | |
| ), | |
| FindValues1, FixFindValues(FindValues1), | |
| FindValues2, FixFindValues(FindValues2), | |
| FindValues3, FixFindValues(FindValues3), | |
| FindValues4, FixFindValues(FindValues4), | |
| // This function attempts to convert InArray# to an appropriate Matches# argument | |
| FixInArray, LAMBDA( InArray, FindValues, | |
| IF( OR( ISOMITTED( InArray), LEN( TRIM( FindValues)) = 0), | |
| {""}, | |
| InArray) | |
| ), | |
| // IF InArray# or FindValues# are not an array, use {""} | |
| InArray1, FixInArray( InArray1, FindValues1), | |
| InArray2, FixInArray( InArray2, FindValues2), | |
| InArray3, FixInArray( InArray3, FindValues3), | |
| InArray4, FixInArray( InArray4, FindValues4), | |
| // Determine Matches | |
| Matches1, ISNUMBER( MATCH( InArray1, FindValues1, 0)), | |
| Matches2, ISNUMBER( MATCH( InArray2, FindValues2, 0)), | |
| Matches3, ISNUMBER( MATCH( InArray3, FindValues3, 0)), | |
| Matches4, ISNUMBER( MATCH( InArray4, FindValues4, 0)), | |
| // Set Filter | |
| Filter, Matches1 * Matches2 * Matches3 * Matches4, | |
| // Filter | |
| Result, IF( SUM( Filter) > 0, FILTER( ReturnArray, Filter), #N/A), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: XYLookupλ | |
| DESCRIPTION:*//**2-way dynamic array lookup*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 15 2025 Craig Hatmaker Copyright | |
| */ | |
| XYLookupλ=LAMBDA( | |
| // Parameter Declarations | |
| [ReturnArray], | |
| [VFindValues], | |
| [VInArray], | |
| [HFindValues], | |
| [HInArray], | |
| [IfNotFound], | |
| [MatchMode], | |
| [SearchMode], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →XYLookupλ( ReturnArray, VLookupValues, VArray, HLookupValues, HArray,¶" & | |
| "→[IfNotFound], [MatchMode], [SearchMode])¶" & | |
| "DESCRIPTION: →Find values matching 1+ criteria in a column, and 1+ in a header.¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "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.¶" & | |
| "→¶" & | |
| "EXAMPLE: →Assume we have data created by this formula in cell B2:¶" & | |
| " Data →=LET(¶" & | |
| "→Dates, EDATE(""2024-01-01"", SEQUENCE( ,12,0)),¶" & | |
| "→Fruit, {""Apples"";""Pears"";""Grapes"";""Figs""},¶" & | |
| "→Data, SEQUENCE(4, 12),¶" & | |
| "→VSTACK( HSTACK(""Fruit↓ Dates->"", Dates), HSTACK( Fruit, Data)))¶" & | |
| " Scenario: →Find the price of Figs on Jun 1st 2024¶" & | |
| " Formula →=XYLookupλ(C3:N6,""Figs"", B3:B6,""2024-06-01"", C2:N2)¶" & | |
| " Result →42¶" & | |
| " Scenario: →Find the price of Apples and Figs on 2024-03-01 and 2024-05-15¶" & | |
| " Formula →=XYLookupλ(C3:N6,""Apples,Figs"", B3:B6,""2024-05-01, 2024-08-01"", C2:N2,,-1)¶" & | |
| " Result →{5, 8; 41, 44}", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( ReturnArray), | |
| ISOMITTED( VFindValues), | |
| ISOMITTED( VInArray), | |
| ISOMITTED( HFindValues), | |
| ISOMITTED( HInArray) | |
| ), | |
| // Set defaults | |
| IfNotFound, IF( ISOMITTED( IfNotFound), NA(), IfNotFound), | |
| MatchMode, IF( ISOMITTED( MatchMode), 0, MatchMode), | |
| SearchMode, IF( ISOMITTED( SearchMode), 1, SearchMode), | |
| // This function attempts to convert xLookupValues to an appropriate Matches# argument | |
| FixFindValues, LAMBDA( FindValues, | |
| IF( OR( ISOMITTED(FindValues), LEN( TRIM( FindValues)) = 0), | |
| {""}, | |
| IF( COUNTA( FindValues) = 1, | |
| LET( | |
| Values, TRIM( TEXTSPLIT( FindValues, ",")), | |
| IFERROR( VALUE( Values), Values)), | |
| FindValues) | |
| ) | |
| ), | |
| VFindValues, FixFindValues( VFindValues), | |
| HFindValues, FixFindValues( HFindValues), | |
| // Prep | |
| Cols, COLUMNS( ReturnArray), | |
| Rows, ROWS( ReturnArray), | |
| BaseRow, EXPAND( 0, 1, Cols, 0), | |
| BaseCol, EXPAND( 0, Rows, 1, 0), | |
| RowCounter, SEQUENCE( Rows), | |
| ColCounter, SEQUENCE( , Cols), | |
| // Procedure | |
| VMatches, XMATCH( VFindValues, VInArray, MatchMode, SearchMode), | |
| VFilter, N( REDUCE( BaseCol, VMatches, | |
| LAMBDA( Acc, n, | |
| MAP( Acc, n = RowCounter, OR) | |
| ) | |
| ) | |
| ), | |
| HMatches, XMATCH( HFindValues, HInArray, MatchMode, SearchMode), | |
| HFilter, N( REDUCE( BaseRow, HMatches, | |
| LAMBDA( Acc, n, | |
| MAP( Acc, n = ColCounter, OR) | |
| ) | |
| ) | |
| ), | |
| FilteredRows, FILTER( ReturnArray, VFilter), | |
| FilteredCols, FILTER( FilteredRows, HFilter), | |
| Result, IFNA( FilteredCols, IfNotFound), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| // Dynamic array versions of common Excel functions | |
| /* FUNCTION NAME: ANDλ | |
| DESCRIPTION:*//**A dynamic array version of AND() that processes rows or columns at a time*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| */ | |
| Andλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array1], | |
| [Array2], | |
| [ByCol], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Andλ( Array1, [Array2], [ByCol])¶" & | |
| "DESCRIPTION: →A dynamic array version of AND() that processes rows or columns at a time¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Mar 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array1 →(Required) An array containing values to compare¶" & | |
| "→ If ByCol = TRUE and we need to compare many separate arrays¶" & | |
| "→ HSTACK them first into this argument. If ByCol = FALSE, VSTACK them.¶" & | |
| " Array2 →(Required) A single character or an array to compare with Array1.¶" & | |
| "→ If ByCol = TRUE and Array2 is an array, Array2 must have the same¶" & | |
| "→ number of columns as Array1. If ByCol = FALSE, Array2 must have the¶" & | |
| "→ same number of rows as Array1¶" & | |
| " ByCol →(Required) TRUE=processes columns. FALSE=Processes rows. ¶" & | |
| "→ If this function is not in the same row as Array1, this defaults to TRUE¶" & | |
| "EXAMPLES: →¶" & | |
| " Setup →J4# contains this array:=SEQUENCE( 3, 5, -7) > -5¶" & | |
| "→J8# contains this array:=SEQUENCE( 1, 5, 2, -1) < 1¶" & | |
| "→J10 contains this value: 1¶" & | |
| " Formula →=Andλ(J4#)¶" & | |
| " Result →FALSE, FALSE, FALSE, TRUE, TRUE¶" & | |
| " Formula →=Andλ(J4#,J10)¶" & | |
| " Result →FALSE, FALSE, FALSE, FALSE, FALSE¶" & | |
| " Formula →=Andλ(J4#, J8#)¶" & | |
| " Result →FALSE, FALSE, FALSE, TRUE, TRUE¶" & | |
| " Formula →=Andλ(J4#, , FALSE)¶" & | |
| " Result →FALSE; TRUE; TRUE¶" & | |
| " Formula →=Andλ(J4#, J8#, FALSE)¶" & | |
| " Result →FALSE; #N/A; #N/A", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array1), | |
| // Process | |
| Result, ArrayVersionPrepλ( Array1, Array2, ByCol, AND), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Avgλ | |
| DESCRIPTION:*//**A dynamic array version of AVERAGE() that processes rows or columns at a time*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| */ | |
| Avgλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array1], | |
| [Array2], | |
| [ByCol], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Avgλ( Array1, [Array2], [ByCol])¶" & | |
| "DESCRIPTION: →A dynamic array version of AVERAGE() that processes rows or columns at a time¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Mar 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array1 →(Required) An array containing values to compare¶" & | |
| "→ If ByCol = TRUE and we need to compare many separate arrays¶" & | |
| "→ HSTACK them first into this argument. If ByCol = FALSE, VSTACK them.¶" & | |
| " Array2 →(Required) A single character or an array to compare with Array1.¶" & | |
| "→ If ByCol = TRUE and Array2 is an array, Array2 must have the same¶" & | |
| "→ number of columns as Array1. If ByCol = FALSE, Array2 must have the¶" & | |
| "→ same number of rows as Array1¶" & | |
| " ByCol →(Required) TRUE=processes columns. FALSE=Processes rows. ¶" & | |
| "→ If this function is not in the same row as Array1, this defaults to TRUE¶" & | |
| "EXAMPLES: →¶" & | |
| " Setup →J4# contains this array:=SEQUENCE( 3, 5, -7)¶" & | |
| "→J8# contains this array:=SEQUENCE( 1, 5, 2, -1)¶" & | |
| "→J10 contains this value: 1¶" & | |
| " Formula →=Avgλ(J4#)¶" & | |
| " Result →-2, -1, 0, 1, 2¶" & | |
| " Formula →=Avgλ(J4#,J10)¶" & | |
| " Result →-1.25, -0.5, 0.25, 1, 1.75¶" & | |
| " Formula →=Avgλ(J4#, J8#)¶" & | |
| " Result →-1, -0.5, 0, 0.5, 1¶" & | |
| " Formula →=Avgλ(J4#, , FALSE)¶" & | |
| " Result →-5; 0; 5¶" & | |
| " Formula →=Avgλ(J4#, J8#, FALSE)¶" & | |
| " Result →-2.5; #N/A; #N/A", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array1), | |
| // Process | |
| Result, ArrayVersionPrepλ( Array1, Array2, ByCol, AVERAGE), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Maxλ | |
| DESCRIPTION:*//**A dynamic array version of MAX() that processes rows or columns at a time*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| */ | |
| Maxλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array1], | |
| [Array2], | |
| [ByCol], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Maxλ( Array1, [Array2], [ByCol])¶" & | |
| "DESCRIPTION: →A dynamic array version of MAX() that processes rows or columns at a time¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Mar 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array1 →(Required) An array containing values to compare¶" & | |
| "→ If ByCol = TRUE and we need to compare many separate arrays¶" & | |
| "→ HSTACK them first into this argument. If ByCol = FALSE, VSTACK them.¶" & | |
| " Array2 →(Required) A single character or an array to compare with Array1.¶" & | |
| "→ If ByCol = TRUE and Array2 is an array, Array2 must have the same¶" & | |
| "→ number of columns as Array1. If ByCol = FALSE, Array2 must have the¶" & | |
| "→ same number of rows as Array1¶" & | |
| " ByCol →(Required) TRUE=processes columns. FALSE=Processes rows. ¶" & | |
| "→ If this function is not in the same row as Array1, this defaults to TRUE¶" & | |
| "EXAMPLES: →¶" & | |
| " Setup →J4# contains this array:=SEQUENCE( 3, 5, -7)¶" & | |
| "→J8# contains this array:=SEQUENCE( 1, 5, 2, -1)¶" & | |
| "→J10 contains this value: 1¶" & | |
| " Formula →=Maxλ(J4#)¶" & | |
| " Result →3, 4, 5, 6, 7¶" & | |
| " Formula →=Maxλ(J4#,J10)¶" & | |
| " Result →3, 4, 5, 6,7¶" & | |
| " Formula →=Maxλ(J4#, J8#)¶" & | |
| " Result →3, 4, 5, 6, 7¶" & | |
| " Formula →=Maxλ(J4#, , FALSE)¶" & | |
| " Result →-3; 2; 7 ¶" & | |
| " Formula →=Maxλ(J4#, J8#, FALSE)¶" & | |
| " Result →2; #N/A; #N/A", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array1), | |
| // Process | |
| Result, ArrayVersionPrepλ( Array1, Array2, ByCol, MAX), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Minλ | |
| DESCRIPTION:*//**A dynamic array version of MIN() that processes rows or columns at a time*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| */ | |
| Minλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array1], | |
| [Array2], | |
| [ByCol], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Minλ( Array1, [Array2], [ByCol])¶" & | |
| "DESCRIPTION: →A dynamic array version of MIN() that processes rows or columns at a time¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Mar 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array1 →(Required) An array containing values to compare¶" & | |
| "→ If ByCol = TRUE and we need to compare many separate arrays¶" & | |
| "→ HSTACK them first into this argument. If ByCol = FALSE, VSTACK them.¶" & | |
| " Array2 →(Required) A single character or an array to compare with Array1.¶" & | |
| "→ If ByCol = TRUE and Array2 is an array, Array2 must have the same¶" & | |
| "→ number of columns as Array1. If ByCol = FALSE, Array2 must have the¶" & | |
| "→ same number of rows as Array1¶" & | |
| " ByCol →(Required) TRUE=processes columns. FALSE=Processes rows. ¶" & | |
| "→ If this function is not in the same row as Array1, this defaults to TRUE¶" & | |
| "EXAMPLES: →¶" & | |
| " Setup →J4# contains this array:=SEQUENCE( 3, 5, -7)¶" & | |
| "→J8# contains this array:=SEQUENCE( 1, 5, 2, -1)¶" & | |
| "→J10 contains this value: 1¶" & | |
| " Formula →=Minλ(J4#)¶" & | |
| " Result →-7, -6, -5, -4, -3¶" & | |
| " Formula →=Minλ(J4#,J10)¶" & | |
| " Result →-7, -6, -5, -4, -3¶" & | |
| " Formula →=Minλ(J4#, J8#)¶" & | |
| " Result →-7, -6, -5, -4, -3¶" & | |
| " Formula →=Minλ(J4#, , FALSE)¶" & | |
| " Result →-7; 2; 3 ¶" & | |
| " Formula →=Minλ(J4#, J8#, FALSE)¶" & | |
| " Result →-7; #N/A; #N/A", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array1), | |
| // Process | |
| Result, ArrayVersionPrepλ( Array1, Array2, ByCol, MIN), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Orλ | |
| DESCRIPTION:*//**A dynamic array version of AND() that processes rows or columns at a time*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| */ | |
| Orλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array1], | |
| [Array2], | |
| [ByCol], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Orλ( Array1, [Array2], [ByCol])¶" & | |
| "DESCRIPTION: →A dynamic array version of OR() that processes rows or columns at a time¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Mar 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array1 →(Required) An array containing values to compare¶" & | |
| "→ If ByCol = TRUE and we need to compare many separate arrays¶" & | |
| "→ HSTACK them first into this argument. If ByCol = FALSE, VSTACK them.¶" & | |
| " Array2 →(Required) A single character or an array to compare with Array1.¶" & | |
| "→ If ByCol = TRUE and Array2 is an array, Array2 must have the same¶" & | |
| "→ number of columns as Array1. If ByCol = FALSE, Array2 must have the¶" & | |
| "→ same number of rows as Array1¶" & | |
| " ByCol →(Required) TRUE=processes columns. FALSE=Processes rows. ¶" & | |
| "→ If this function is not in the same row as Array1, this defaults to TRUE¶" & | |
| "EXAMPLES: →¶" & | |
| " Setup →J4# contains this array:=SEQUENCE( 3, 5, -7) > 3¶" & | |
| "→J8# contains this array:=SEQUENCE( 1, 5, 2, -1)¶" & | |
| "→J10 contains this value: 1¶" & | |
| " Formula →=Orλ(J4#)¶" & | |
| " Result →FALSE, TRUE, TRUE, TRUE, TRUE¶" & | |
| " Formula →=Orλ(J4#,J10)¶" & | |
| " Result →TRUE, TRUE, TRUE, TRUE, TRUE¶" & | |
| " Formula →=Orλ(J4#, J8#)¶" & | |
| " Result →FALSE, TRUE, TRUE, TRUE, TRUE¶" & | |
| " Formula →=Orλ(J4#, , FALSE)¶" & | |
| " Result →FALSE; FALSE; TRUE¶" & | |
| " Formula →=Orλ(J4#, J8#, FALSE)¶" & | |
| " Result →TRUE; #N/A; #N/A", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array1), | |
| // Process | |
| Result, ArrayVersionPrepλ( Array1, Array2, ByCol, OR), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Sumλ | |
| DESCRIPTION:*//**A dynamic array version of SUM() that processes rows or columns at a time*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| */ | |
| Sumλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array1], | |
| [Array2], | |
| [ByCol], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Sumλ( Array1, [Array2], [ByCol])¶" & | |
| "DESCRIPTION: →A dynamic array version of SUM() that processes rows or columns at a time¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Mar 24, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array1 →(Required) An array containing values to compare¶" & | |
| "→ If ByCol = TRUE and we need to compare many separate arrays¶" & | |
| "→ HSTACK them first into this argument. If ByCol = FALSE, VSTACK them.¶" & | |
| " Array2 →(Required) A single character or an array to compare with Array1.¶" & | |
| "→ If ByCol = TRUE and Array2 is an array, Array2 must have the same¶" & | |
| "→ number of columns as Array1. If ByCol = FALSE, Array2 must have the¶" & | |
| "→ same number of rows as Array1¶" & | |
| " ByCol →(Required) TRUE=processes columns. FALSE=Processes rows. ¶" & | |
| "→ If this function is not in the same row as Array1, this defaults to TRUE¶" & | |
| "EXAMPLES: →¶" & | |
| " Setup →J4# contains this array:=SEQUENCE( 3, 5, -7)¶" & | |
| "→J8# contains this array:=SEQUENCE( 1, 5, 2, -1)¶" & | |
| "→J10 contains this value: 1¶" & | |
| " Formula →=Sumλ(J4#)¶" & | |
| " Result →-6,-3, 0, 3, 6¶" & | |
| " Formula →=Sumλ(J4#,J10)¶" & | |
| " Result →-5, -2, 1, 4, 7¶" & | |
| " Formula →=Sumλ(J4#, J8#)¶" & | |
| " Result →-4, -2, 0, 2, 4¶" & | |
| " Formula →=Sumλ(J4#, , FALSE)¶" & | |
| " Result →-25; 0; 25 ¶" & | |
| " Formula →=Sumλ(J4#, J8#, FALSE)¶" & | |
| " Result →-25; #N/A; #N/A", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array1), | |
| // Process | |
| Result, ArrayVersionPrepλ( Array1, Array2, ByCol, SUM), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SumAλ | |
| DESCRIPTION:*//**(Sumλ Alias) A dynamic array version of SUM() that processes rows or columns at a time*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| */ | |
| SumAλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array1], | |
| [Array2], | |
| [ByCol], | |
| Sumλ( Array1, Array2, ByCol) | |
| ); | |
| /* FUNCTION NAME: ArrayVersionPrepλ | |
| DESCRIPTION:*//**Prep Array1 plus nothing, a scalor, or another array before applying a function*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 22 2025 Craig Hatmaker Copyright | |
| */ | |
| ArrayVersionPrepλ = LAMBDA( | |
| // Parameter Declarations | |
| [Array1], | |
| [Array2], | |
| [ByCol], | |
| [Function], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ArrayVersionPrepλ( Array1, [Array2], [ByCol])¶" & | |
| "DESCRIPTION: →Prep Array1 plus nothing, a scalor, or another array before applying a function" & | |
| "→ This meant as a support function for Minλ, Maxλ, Sumλ, and Avgλ ¶" & | |
| "WEBPAGE: →<coming soon>¶" & | |
| "VERSION: →BXL: Mar 22, 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Array1 →(Required) An array of values¶" & | |
| "→ If ByCol = TRUE and we need to compare many separate arrays¶" & | |
| "→ HSTACK them first into this argument. If ByCol = FALSE, VSTACK them.¶" & | |
| " Array2 →(Optional) A single value or a second array to process with Array1.¶" & | |
| "→ If ByCol = TRUE and Array2 is an array, Array2 must have the same¶" & | |
| "→ number of columns as Array1. If ByCol = FALSE, Array2 must have the¶" & | |
| "→ same number of rows as Array1¶" & | |
| " ByCol →(Required) TRUE=processes columns. FALSE=Processes rows. ¶" & | |
| "→ If this function is not in the same row as Array1, this defaults to TRUE¶" & | |
| "EXAMPLES: →¶" & | |
| " Setup →J4# contains this array:=SEQUENCE( 3, 5, -7)¶" & | |
| "→J8# contains this array:=SEQUENCE( 1, 5, 2, -1)¶" & | |
| "→J10 contains this value: 1¶" & | |
| " Formula →=Sumλ(J4#)¶" & | |
| " Result →-6,-3, 0, 3, 6¶" & | |
| " Formula →=Sumλ(J4#,J10)¶" & | |
| " Result →-5, -2, 1, 4, 7¶" & | |
| " Formula →=Sumλ(J4#, J8#)¶" & | |
| " Result →-4, -2, 0, 2, 4¶" & | |
| " Formula →=Sumλ(J4#, , FALSE)¶" & | |
| " Result →-25; 0; 25 ¶" & | |
| " Formula →=Sumλ(J4#, J8#, FALSE)¶" & | |
| " Result →-25; #N/A; #N/A", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, ISOMITTED( Array1), | |
| // Set defaults | |
| // If ByCol omitted, assume TRUE if this function is NOT in the same row as Array1 | |
| ByCol, IF( ISOMITTED( ByCol), | |
| IF( ISREF( Array1), | |
| ROW() <> @ROW( Array1), // Only works if Array1 is in a range | |
| TRUE), | |
| ByCol), | |
| Function, IF( ISOMITTED( Function), MAX, Function), | |
| // Conform Array2's dimensions for processing | |
| Rows, IF( ByCol, 1, ROWS( Array1)), | |
| Cols, IF( ByCol, COLUMNS( Array1), 1), | |
| Omitted, ISOMITTED( Array2), | |
| IsSingle, AND( NOT( Omitted), COUNTA( Array2) = 1), | |
| IsArray, NOT( OR( Omitted, IsSingle)), | |
| Array2, IF( IsSingle, | |
| EXPAND( Array2, Rows, Cols, Array2), | |
| Array2), | |
| Array, IF( Omitted, | |
| Array1, | |
| IF( ByCol, | |
| VSTACK( Array1, Array2), | |
| HSTACK( Array1, Array2) | |
| ) | |
| ), | |
| // Process | |
| Result, IF( ByCol, | |
| BYCOL( Array, Function), | |
| BYROW( Array, Function) | |
| ), | |
| // Return Result | |
| IF(Help?, Help, Result) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment