Excel Robot ExcelRobot
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
| /* FUNCTION NAME: MAPλ | |
| DESCRIPTION: Implements a version of MAP that will return an array of arrays */ | |
| /* REVISIONS: Date Developer Description | |
| 28 Aug 2024 Peter Bartholomew Adapted from BYROWλ to give MAPλ | |
| 31 Aug 2024 Peter Bartholomew JOINPAIRSλ modified to stack 2D result arrays | |
| 10 Sep 2024 Peter Bartholomew Modified to accept 3 array argumments before λ-function | |
| */ | |
| MAPλ = LAMBDA( | |
| // Parameter Declarations | |
| [array1], |
CHatmaker
/ BXL LAMBDA Reporting with Arrays
Last active
April 7, 2024 15:06
5G functions for Excel for Reporting with Arrays
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
| /* FUNCTION NAME: Aboutλ | |
| DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 17 2023 Craig Hatmaker Copyright | |
| Apr 17 2023 Craig Hatmaker Reorganizing and adding help | |
| Jul 03 2023 Craig Hatmaker See nFoldCartProdλ and PriorDimProdλ | |
| Jul 11 2023 Craig Hatmaker See UnPivotDetailλ | |
| Feb 11 2024 Craig Hatmaker See UnPivotDetailλ | |
| */ |
CHatmaker
/ BXL LAMBDA Dynamic Ranges
Last active
January 27, 2024 15:46
5G functions for Excel for Dynamic Ranges
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
| /* FUNCTION NAME: Aboutλ | |
| DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 02 2023 Craig Hatmaker Original Development | |
| Jun 08 2023 Craig Hatmaker CBSE Compliant | |
| Jan 17 2024 Craig Hatmaker See DynamicArrayλ | |
| */ | |
| Aboutλ =TEXTSPLIT( | |
| "About: →BXL's Dynamic Array module. Suggested module name: BDR¶" & |
CHatmaker
/ BXL Excel LAMBDA Math
Last active
January 27, 2024 15:47
5G functions for Excel: Miscellaneous Math
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
| /* Function Description | |
| Aboutλ About this module and links to online resources | |
| Fibonacciλ Determine the nth number in a Fibonacci sequence | |
| */ | |
| /* FUNCTION NAME: Aboutλ | |
| DESCRIPTION:*/ /**Displays the URL to this module's Gist which includes documentation*/ |
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
| /* USE NAMESPACE "List" */ | |
| Accumulate = LAMBDA(_list, _seed, _accumulator, REDUCE(_seed, _list, _accumulator)); | |
| AllTrue = LAMBDA(_list, AND(_list)); | |
| AnyTrue = LAMBDA(_list, OR(_list)); | |
| Average = LAMBDA(_list, AVERAGE(_list)); |
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
| /* ROOTS OF FUNCTIONS */ | |
| BISEC = LAMBDA(f, lbound, ubound, [prec], | |
| LET( | |
| c, (lbound + ubound) / 2, | |
| fl, f(lbound), fu, f(ubound), fc, f(c), | |
| IF( | |
| ABS(fc) < MAX(prec, 1E-15), | |
| c, | |
| IF( | |
| SIGN(fl) = SIGN(fc), |
CHatmaker
/ BXL LAMBDA Excel Dates.txt
Last active
June 12, 2024 01:24
5g Functions for Excel: Dates
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
| /* Module Contains 5g Compliant LAMBDAs that deal with dates */ | |
| /* FUNCTION NAME: Aboutλ | |
| DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/ | |
| /* REVISIONS: Date Developer Description | |
| Mar 17 2023 Craig Hatmaker Original Development | |
| Mar 22 2023 Craig Hatmaker Added About | |
| Apr 06 2023 Craig Hatmaker Added Help to LAMBDAs | |
| Aug 28 2023 Craig Hatmaker Conformed to new template | |
| Jan 02 2024 Craig Hatmaker See CountDOWλ |
CHatmaker
/ BXL LAMBDA Excel CrtIdxλ
Last active
April 21, 2024 18:35
5g Functions for Excel: CrtIdxλ
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
| /* FUNCTION NAME: CrtIdxλ | |
| DESCRIPTION:*/ /**Creates an array of indexes that can be used with INDEX() can combine | |
| all rows in all tables as would a database 'Natrual Join' or 'Cross Join.'*/ | |
| /* This was developed for multidimensional modeling. A key to multidimensional modeling | |
| is the ability to combine all instances of all dimensions, and then access each | |
| dimension's value. A dimension is a category of things like: | |
| Customers, Products, Regions, Months, etc. | |
| When faced with the situation where each dimension has a piece of information needed | |
| for a particular calculation such as: |
ncalm
/ excel-lambda-OUTLIERS.txt
Created
April 23, 2022 13:35
This collection of Excel lambda functions provides functionality to quickly apply the standard deviation test on a series of transformations to a continuous variable
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
| /* | |
| OUTLIERS | |
| These lambda functions allow us to quickly apply the standard deviation test to a series of transformed variables | |
| Included in this file: | |
| OUTLIER.THRESHOLDS - for calculating outlier thresholds using a standard deviation test | |
| inputs: |
ncalm
/ excel-lambda-ISLEAPYEAR.txt
Last active
June 15, 2023 09:42
This Excel lambda function returns TRUE for a leap year and FALSE for all other years
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
| /* | |
| ISLEAPYEAR | |
| Calculates whether a year is a leap year | |
| Inputs: | |
| - yr is the year you want to test | |
| Returns: | |
| TRUE if the year is a leap year, FALSE otherwise |
NewerOlder