5g: Array Essential Library

Miscellaneous array functions

• CountCλ Count how many of one or more characters are in a text string

• 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.

• PctIncrDecrλ Apply percentage increases/(decreases) to a value over 1+ columns.

• RangeToDAλ Convert a range reference to a dynamic array reference

Array lookup functions we wish Excel always 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.

Running Totals

• RunTotColsλ Create a running total for values in each column of an array.

• RunTotRowsλ Create a running total for values in each row of an array.

Rolling Suite

• Rollingλ Eta reduced LAMBDA function, like BYCOL(), for applying many of Excel's functions to rolling windows.

• RollAvgλ Rolling AVERAGE. Same as Rollingλ( Row, Size, Offset, AllowPartial, AVERAGE) 

• RollMaxλ Rolling MAX. Same as Rollingλ( Row, Size, Offset, AllowPartial, MAX) 

• RollMinλ Rolling MIN. Same as Rollingλ( Row, Size, Offset, AllowPartial, MIN) 

• RollSumλ Rolling SUM. Same as Rollingλ( Row, Size, Offset, AllowPartial, SUM) 

• RollRowsλ Same as Rollingλ but for multiple rows

• RollColsλ A column oriented version of Rollingλ for multiple columns

Range aware versions of common Excel Functions

These are range placement aware versions of common Excel functions. When placed in the same column as a range's top left cell, these functions process the range by column. When placed in the same row as a range's top left cell, by row. NOTE! When used with arrays (that are not ranges) we must tell the function if we want to process the array by column or by row. 

• AndAλ A range placement aware version of AND().

• AvgAλ A range placement aware version of AVERAGE().

• MaxAλ A range placement aware version of MAX().

• MinAλ A range placement aware version of MIN().

• OrAλ A range placement aware version of OR().

• SumAλ A range placement aware version of SUM().

• ArrayPrep Conform Array2 (if exists) to Array1 before applying a function