Excel Lambda functions for numerical analyses

EX_NumericalMethods

Template = LAMBDA(
    //  Parameter Declarations
    x,
    LET(
        //  Help
        //  Error Messages
        //  Check Inputs
        //  Procedure
        //  Handle Error
        //  Return Result
        x,
        x,
        x
    )
);

/* ROOTS OF FUNCTIONS */
BISEC = LAMBDA(

    //  Parameter Declarations
    f,
    lbound,
    ubound,
    [prec],

    LET(

        //  Help
        Help, TRIM(
            TEXTSPLIT(
                "DESCRIPTION:->Returns the root of a function using Bi-section method.
                VERSION:->1.0
                PARAMETERS:->f: Lambda function, lbound: lower bound of searching range, ubound: upper bound of searching range, prec: convergence criteria",
                "->",
                "
                "
            )
        ),

        //  Error Messages
        ErrorMessages, {
            "f should be a lambda function",
            "lbound, ubound, and prec should be numerical"
        },
    
        //  Check Inputs
        prec, IF(ISOMITTED(prec), 1E-15, prec),
        _err1, FALSE(), //IN FUTURE
        _err2, FALSE(), //IN FUTURE
        ErrorArray, VSTACK(ISERROR(_err1), ISERROR(_err2)),
        Messages, FILTER(ErrorMessages, ErrorArray, ""),

        //  Procedure
        c, (lbound + ubound) / 2,
        fl, f(lbound), fu, f(ubound), fc, f(c),
        Result, IF(
            ABS(fc) < prec,
            c,
            IF(
                SIGN(fl) = SIGN(fc),
                BISEC(f, c, ubound, prec),
                BISEC(f, lbound, c, prec)
            )
        ),

        //  Handle Error
        Error, IF(OR(ErrorArray), 3, IF(OR(ISERROR(Result)), 2, 1)),
        
        //  Return Result
        CHOOSE(Error, Result, Help, Messages)

    )
);

FALSEPOS = LAMBDA(

    //  Parameter Declarations
    f,
    lbound,
    ubound,
    [prec],

    LET(

        //  Help
        Help, TRIM(
            TEXTSPLIT(
                "DESCRIPTION:->Returns the root of a function using false pos method.
                VERSION:->1.0
                PARAMETERS:->f: Lambda function, lbound: lower bound of searching range, ubound: upper bound of searching range, prec: convergence criteria",
                "->",
                "
                "
            )
        ),

        //  Error Messages
        ErrorMessages, {
            "f should be a lambda function",
            "lbound, ubound, and prec should be numerical"
        },
    
        //  Check Inputs
        prec, IF(ISOMITTED(prec), 1E-15, prec),
        _err1, FALSE(), //IN FUTURE
        _err2, FALSE(), //IN FUTURE
        ErrorArray, VSTACK(ISERROR(_err1), ISERROR(_err2)),
        Messages, FILTER(ErrorMessages, ErrorArray, ""),

        //  Procedure
        fl, f(lbound), fu, f(ubound),
        c, ubound - fu * (lbound - ubound) / (fl - fu),
        fc, f(c),
        Result, IF(
            ABS(fc) <= prec,
            c,
            IF(
                SIGN(fl) = SIGN(fc),
                FALSEPOS(f, c, ubound, prec),
                FALSEPOS(f, lbound, c, prec)
            )
        ),

        //  Handle Error
        Error, IF(OR(ErrorArray), 3, IF(OR(ISERROR(Result)), 2, 1)),
        
        //  Return Result
        CHOOSE(Error, Result, Help, Messages)

    )
);

NR = LAMBDA(

    //  Parameter Declarations
    f,
    x0,
    [prec],

    LET(

        //  Help
        Help, TRIM(
            TEXTSPLIT(
                "DESCRIPTION:->Returns the root of a function using newton raphson method.
                VERSION:->1.0
                PARAMETERS:->f: Lambda function, x0: start value, prec: convergence criteria",
                "->",
                "
                "
            )
        ),

        //  Error Messages
        ErrorMessages, {
            "f should be a lambda function",
            "x0, and prec should be numerical"
        },

        //  Check Inputs
        prec, IF(ISOMITTED(prec), 1E-15, prec),
        _err1, FALSE(), //IN FUTURE
        _err2, FALSE(), //IN FUTURE
        ErrorArray, VSTACK(ISERROR(_err1), ISERROR(_err2)),
        Messages, FILTER(ErrorMessages, ErrorArray, ""),

        //  Procedure
        fx, f(x0),
        Result, IF(
            ABS(fx) <= prec,
            x0,
            LET(
                xr, x0 - fx / DF(f)(x0),
                NR(f, xr, prec)
            )
        ),

        //  Handle Error
        Error, IF(OR(ErrorArray), 3, IF(OR(ISERROR(Result)), 2, 1)),
        
        //  Return Result
        CHOOSE(Error, Result, Help, Messages)
    
    )
);

/* LINEAR ALGEBRAIC EQUATIONS */
LINSOLVE = LAMBDA(_coefmat, _constmat, MMULT(MINVERSE(_coefmat), _constmat));

/* CURVE FITTING */
POLEST = LAMBDA(listY, listX, n, LINEST(listY, POWER(listX, TRANSPOSE(SEQUENCE(n, 1, 1, 1)))));

CPOLINT = LAMBDA(x, coeffs,
    LET(
        n, COUNT(coeffs),
        MMULT(
            POWER(x, TRANSPOSE(SEQUENCE(n, 1, n - 1, -1))) * coeffs,
            SEQUENCE(n, 1, 1, 0)
        )
    )
);

DPOLINT = LAMBDA(listY, listX, n, x,
    MMULT(
        POWER(x, TRANSPOSE(SEQUENCE(n + 1, 1, n, -1))) * POLEST(listY, listX, n),
        SEQUENCE(n + 1, 1, 1, 0)
    )
);

/* NUMERICAL DIFFERENTATION */
DF = LAMBDA(f,
    LAMBDA(x,
        LET(
            dx, 0.00001,
            (f(x + dx) - f(x - dx)) / (2 * dx)
        )
    )
);

/* EXTREMA */
LOCALEXT = LAMBDA(f, lbound, ubound,
    LET(
        extx, BISEC(DF(f), lbound, ubound, 0.00001),
        exty, f(extx),
        MAKEARRAY(1, 2, LAMBDA(ir, ic, CHOOSE(ic, extx, exty)))
    )
);