ExcelRobot

/*
Excel Array Manipulation LAMBDA functions
Courtesy of Excel Robot

The Microsoft Excel team recent released 11 new Excel functions for combining, shaping, and resizing arrays.
I had already created similar ones for myself using the amazing LAMBDA function.  Since the new functions
are only available to users running Beta Channel, I've renamed my versions and given them the same function 
names so anyone with LAMBDA and LAMBDA helper functions (like MAKEARRAY), and import these LAMBDAs into your
workbook and have nearly the same functionality.

ExcelRobot

/*
Excel Text Manipulation LAMBDA functions
Courtesy of Excel Robot

The Microsoft Excel team recent released three new Excel functions for breaking apart text by delimiters.
Since the new functions are only available to users running Beta Channel, I've created my own versions and 
given them the same function names so anyone with LAMBDA and LAMBDA helper functions (like MAKEARRAY) can 
import these LAMBDAs into your own workbooks and have nearly the same functionality.

* Feel free to share this collection, appreciate any shout outs to Excel Robot along the way.

ExcelRobot

/*
Name: Split Text To Columns (TEXTTOCOLUMNS)
Description: Separates text into columns similar to Excel's Text To Column feature. 
   One or more delimiters can be specified as an array, but only one multi-characters
   delimiter is supported (default: comma).  If a quoted identfier is specified, any
   delimiters between quotes will be ignored and outer quotes will be removed. Also 
   supports different characters for left quote and right quote by specifying two 
   character quoted identifier (ie: ʻʼ).
Author: Excel Robot (@ExcelRobot)
Category: Text

ExcelRobot

/*
Name: Is Formula Overridden (IsFormulaOverridden)
Description: Checks whether the cell is either not a formula or
    a formula that contains no letters (ie: =123.45+678.90).  Can
    be used with conditional formatting to highlight when users 
    hardcode numbers over your formulas.
Author: Excel Robot (@ExcelRobot)
Category: Auditing
*/
IsFormulaOverridden=LAMBDA(Cell,

ExcelRobot

/*
Name: IsFormulaConsistent (IsFormulaConsistent)
Description: Given a cell, checks to make sure the cell above and below have
    consistent formulas.  Uses FORMULATEXT and compares the formulas to make 
    sure they are the same except for numbers that increase/decrease by 1.
    Optional parameter to specify the cell to compare with.  This function is
    recursive. Can be used with conditional formatting to highlight cells that 
    have inconsistent formulas.   
Author: Excel Robot (@ExcelRobot)
Category: Auditing

ExcelRobot

/*
Calculate By Parallel Period LAMBDA function was inspired by Helen Wall (https://www.linkedin.com/in/helenrmwall/)
who posted a clever April fools post showing off the "new DAX measure functionality" in Excel (https://www.linkedin.com/posts/helenrmwall_excel-dax-dataanalytics-activity-6915787496492560384-71V0?utm_source=linkedin_share&utm_medium=member_desktop_web)
which was =CALCULATE(AVERAGE(Rate,PARALLELPERIOD(Date,0,MONTH))).  Of course this is sadly not possible
in Excel formulas.  But since I'm obsessed with the LAMBDA function I took it as a challenge to create 
a CALCBYPARALLELPERIOD function that anyone can use to have the same functionality as what Helen teased
could be done in formulas.  Thanks to Helen for the idea, and also thanks to Wall Street Prep for their 
EOQUARTER function included below and used in this solution (https://www.wallstreetprep.com/knowledge/excel-lambda-function).

* Please feel use and share this collection, courtesy of Excel Robot.

ExcelRobot

/*
Name: Direct Precedents (DIRECTPRECEDENTS)
Description: Lists all direct precedents for specified cell.
Author: Excel Robot (@ExcelRobot)
Category: Formula
*/
DIRECTPRECEDENTS = LAMBDA(cell,LET(
   tokens, TOKENIZEFORMULA(OFFSET(cell,0,0,1,1)),
   isterm, INDEX(tokens,,1)="Term",
   activesheetname, MID(CELL("filename",INDIRECT("$A$1")),FIND("]",CELL("filename",INDIRECT("$A$1")))+1,999),

ExcelRobot

/*
Name: Choose Table Columns (CHOOSETABLECOLUMNS)
Description: Similar to CHOOSECOLUMNS but can accept an array of column header names or 
   array of column indexes.  If column headers are used, assumes first row of Table is
   header row.
Author: Excel Robot (@ExcelRobot)
Category: Table
*/
CHOOSETABLECOLUMNS = LAMBDA(Table, ColumnsToSelect, LET(
    FirstColumnToSelect, INDEX(ColumnsToSelect,1,1), 

ExcelRobot

/*
These lambdas were inspired by the work of Mike Wolfe's article "Displaying Human
Readable Time Spans" (https://nolongerset.com/displaying-time-spans/).  He provided
the logic and I enjoyed the puzzle of converting that logic to dynamic arrays. 

Included within:
 * HUMANIZESECONDS - Converts seconds to human readable text
 * PLURALIZE - Adapts wording based on whether number is singular/plural and 
    positive/negative.
 * CONVERTTOSECONDS - Converts units of years, weeks, days, hours, minutes, and 

ExcelRobot

/*
Name: Convert Number To Ordinal (ADDTH)
Description: Converts number to ordinal ie: 1st, 2nd, 3rd, 4th, 11th, 12th, 13th, 21st, etc.
Author: Excel Robot (@ExcelRobot)
Inspired By: Rick de Groot (https://www.linkedin.com/posts/rickmaurinus_powerbi-businessintelligence-dax-activity-6920723485937790976-Wzdb?utm_source=linkedin_share&utm_medium=member_desktop_web)
Category: Conversion
*/
ADDTH = LAMBDA(number,LET(
    LastDigit, RIGHT(number),
    LastTwo, RIGHT(number, 2),