BXL 5g Functions LAMBDA for Excel: Financial Model Utilities

BXL 5g Functions LAMBDA for Excel: Financial Model Utilities

/*  FUNCTION NAME:  Aboutλ
    DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/*  REVISIONS:      Date            Developer       Description  
                    Apr 10 2025     Craig Hatmaker  Original Development
*/

Aboutλ = TRIM(TEXTSPLIT(
    "About:         →BXL's Financial Model Utilities. Suggested module name: FMU¶" &
    "Version:       →Apr 10 2025¶" &
    "Gist URL:      →https://gist.github.com/CHatmaker/51abd8149e2f50c4aff5b6eae9b9045a ¶" &
    "Website:       →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" &
    "→¶" &
    "Function       →Description¶" & 
    "Aboutλ         →Produces this table¶" & 
    "Refs2Txtsλ     →Convert single cell references in formulas to cell reference's labels.¶" & 
    "Formula2LETλ   →Convert single cell reference formulas to LET statements.", 
    "→","¶"
    )
);


    

/*  FUNCTION NAME:  Refs2Txtsλ
    DESCRIPTION:*/  /**Convert single cell references in formulas to cell reference's labels.*/
/*  REVISIONS:      Date            Developer       Description  
                    Apr 10 2025     Craig Hatmaker  Copyright
*/
Refs2Txtsλ = LAMBDA( 
    [FormulaCells], 
    [FormulaLabels],
//  Help
    LET(
        Help,   TRIM( TEXTSPLIT(
                    "FUNCTION:      →Refs2Txtλ(FormulaCell, FormulaLabels)¶" &
                    "DESCRIPTION:   →Convert single cell references in formulas to cell reference's labels.¶" &
                    "WEBSITE:       →<Comming soon>¶" &
                    "VERSION:       →BXL: Apr 10 2025¶" &
                    "PARAMETERS:    →¶" &
                    " FormulaCells  →(Required) One or more cells containing formulas to convert¶" &
                    " FormulaLables →(Required) Cells containing labels for cell references¶" &
                    "EXAMPLE:       →¶" &
                    " Formula       →=Refs2Txtsλ(J10:J16, E5:E16)¶" &
                    " Result        →2",
                    "→",
                    "¶"
                    )
                ),
    //  Check inputs - Omitted required arguments
        Help?,      OR( ISOMITTED( FormulaCells), 
                        ISOMITTED( FormulaLabels)
                    ),
    //  Constants
        CellRef,     "(\$?[A-Za-z]{1,3}\$?)",
    //  Procedure
        Ref2Text,   LAMBDA( 
                        FormulaCell, 
                        FormulaLabels,
                        LET(
                            FormulaText,    FORMULATEXT( FormulaCell),
                            FormulaColumn,  COLUMN(FormulaCell),
                            LabelRow,       @ROW( FormulaLabels),
                            LabelCount,     ROWS( FormulaLabels),
                            Counter,        SEQUENCE( LabelCount),
                            Result,         REDUCE( FormulaText, Counter,
                                                LAMBDA( Text, n,
                                                    LET(
                                                        Label,      INDEX( FormulaLabels, n),
                                                        Row,        n + LabelRow -1,
                                                        Ref,        REGEXEXTRACT(Text, CellRef & Row),
                                                        RefCol,     IF( ISNA( Ref), FormulaColumn, COLUMN( INDIRECT( Ref))),
                                                        NewText,    REGEXREPLACE( Text, CellRef & Row, Label),
                                                        Result,     NewText & IF( RefCol = FormulaColumn -1 , " <prior column>", ""),
                                                        Result
                                                    )
                                                )
                                            ),
                            Result
                        )
                    ),
        Result,     MAP( 
                        FormulaCells, 
                        LAMBDA( 
                            FormulaCell, 
                            Ref2Text( FormulaCell, FormulaLabels)
                        )
                    ),
    //  Return Result
        CHOOSE(Help? + 1, Result, Help)
    )
);



/*  FUNCTION NAME:  Formula2LETλ
    DESCRIPTION:*/  /**Convert single cell reference formulas to LET statements.*/
/*  REVISIONS:      Date            Developer       Description  
                    Apr 10 2025     Craig Hatmaker  Copyright
*/
Formula2LETλ = LAMBDA( 
    [FormulaCells], 
    [FormulaLabels],
//  Help
    LET(
        Help,   TRIM( TEXTSPLIT(
                    "FUNCTION:      →Formulas2LETλ(FormulaCell, FormulaLabels)¶" &
                    "DESCRIPTION:   →Convert single cell reference formulas to LET statements.¶" &
                    "WEBSITE:       →<Comming soon>¶" &
                    "VERSION:       →BXL: Apr 10 2025¶" &
                    "PARAMETERS:    →¶" &
                    " FormulaCells  →(Required) One or more cells containing formulas to convert¶" &
                    " FormulaLables →(Required) Cells containing labels for cell references¶" &
                    "EXAMPLE:       →¶" &
                    " Formula       →=Formula2LETλ(J10:J16, E5:E16)¶" &
                    " Result        →2",
                    "→",
                    "¶"
                    )
                ),
    //  Check inputs - Omitted required arguments
        Help?,      OR( ISOMITTED( FormulaCells), 
                        ISOMITTED( FormulaLabels)
                    ),
    //  Procedure        
        Formula2Text,   LAMBDA(
                        FormulaCell, 
                        FormulaLabels,
                        LET(
                            FormulaText,    Refs2Txtsλ( FormulaCell, FormulaLabels),
                            LabelRow,       ROW( FormulaCell) - @ROW( FormulaLabels) + 1,
                            Label,          TRIM( INDEX( FormulaLabels, LabelRow)),
                            Result,         Label & ", " & RIGHT( FormulaText, LEN( FormulaText) - 1) & "," ,
                            Result
                        )
                    
                    ),
        Result,     MAP( 
                        FormulaCells, 
                        LAMBDA( 
                            FormulaCell, 
                            Formula2Text( FormulaCell, FormulaLabels)
                        )
                    ),
    //  Return Result
        CHOOSE(Help? + 1, Result, Help)
    )
);