Toolbox of named excel lambdas

xlTools.txt

/**Cumulative sum over array*/
CSUM = LAMBDA(arr,
    SCAN(0,arr,LAMBDA(a,c,a+c)));

/**Ignores Null ref (empty, space or 0), returns "" or VAl if provided*/
IFN = LAMBDA(REF,FUN,[VAL],
IF(OR(REF="",REF=" ",REF=0),IF(ISOMITTED(VAL),"",VAL),FUN)
);

/**1D linear interpolation, set SORTED=0 for unsorted data - presorting data recommended*/
INTERP1 = LAMBDA(X,XARRAY,YARRAY,[SORTED],
    IF(OR(ISOMITTED(SORTED),SORTED=1,SORTED=0),
    LET(
        SORT_METHOD, IF(OR(ISOMITTED(SORTED),SORTED=1),2,1),
        X_1, XLOOKUP(X,XARRAY,XARRAY,,-1,SORT_METHOD),
        X_2, XLOOKUP(X,XARRAY,XARRAY,,1,SORT_METHOD),
        Y_1, XLOOKUP(X,XARRAY,YARRAY,,-1,SORT_METHOD),
        Y_2, XLOOKUP(X,XARRAY,YARRAY,,1,SORT_METHOD),
        IF(X=X_1,Y_1,Y_1+(X-X_1)*(Y_2-Y_1)/(X_2-X_1))
    ))
);

/**2D linear interpolation, set SORTED=0 for unsorted data - presorting data recommended*/
INTERP2 = LAMBDA(X,Y,XARRAY,YARRAY,ZARRAY,[SORTED],
    IF(OR(ISOMITTED(SORTED),SORTED=1,SORTED=0),
    LET(
        SORT_METHOD, IF(OR(ISOMITTED(SORTED),SORTED=1),2,1),
        X_1, XLOOKUP(X,XARRAY,XARRAY,,-1,SORT_METHOD),
        X_2, XLOOKUP(X,XARRAY,XARRAY,,1,SORT_METHOD),
        Y_1, XLOOKUP(Y,YARRAY,YARRAY,,-1,SORT_METHOD),
        Y_2, XLOOKUP(Y,YARRAY,YARRAY,,1,SORT_METHOD),
        Z_11, XLOOKUP(X_1,XARRAY,XLOOKUP(Y_1,YARRAY,ZARRAY,,0,SORT_METHOD),,0,SORT_METHOD),
        Z_12, XLOOKUP(X_1,XARRAY,XLOOKUP(Y_2,YARRAY,ZARRAY,,0,SORT_METHOD),,0,SORT_METHOD),
        Z_21, XLOOKUP(X_2,XARRAY,XLOOKUP(Y_1,YARRAY,ZARRAY,,0,SORT_METHOD),,0,SORT_METHOD),
        Z_22, XLOOKUP(X_2,XARRAY,XLOOKUP(Y_2,YARRAY,ZARRAY,,0,SORT_METHOD),,0,SORT_METHOD),
        Z_1, IF(Y=Y_1,Z_12,Z_11+(Y-Y_1)*(Z_12-Z_11)/(Y_2-Y_1)),
        Z_2, IF(Y=Y_1,Z_21,Z_21+(Y-Y_1)*(Z_22-Z_21)/(Y_2-Y_1)),
        IF(X=X_1,Z_1,Z_1+(X-X_1)*(Z_2-Z_1)/(X_2-X_1))
    ))
);

/**Basic 1D linear generator from 2 pts*/
LEXT = LAMBDA(X,XS,YS,
    LET(X_1,INDEX(XS,1),
        X_2, INDEX(XS,2),
        Y_1, INDEX(YS,1),
        Y_2, INDEX(YS,2),
        Y_1+(X-X_1)*(Y_2-Y_1)/(X_2-X_1)
        )
);

//*Applies array of substitutions - array inputs ok*/
MultiSub = LAMBDA(TEXT, OLD, NEW,
    LET(
        len, COUNTA(OLD),
        IF(len=1,SUBSTITUTE(TEXT,OLD,NEW),
            LET(
                old_i, TAKE(OLD,1),
                old_r, TAKE(OLD,-len+1),
                new_i, TAKE(NEW,1),
                new_r, TAKE(NEW,-len+1),
                MultiSub(SUBSTITUTE(TEXT,old_i,new_i),old_r,new_r)
            )//_LET
        )//_IF
    )//_LET
);

/**Maps funtion to outer projection of a row and column input, 
function must be lambda taking row and column inputs*/
OuterMap = LAMBDA(ROW_, COL_, FUNC_,
    LET(
        m, ROWS(COL_),
        n, COLUMNS(ROW_),
        mi, MAKEARRAY(1,n,LAMBDA(r,c,1)),
        ni, MAKEARRAY(m,1,LAMBDA(r,c,1)),
        RA, ROW_*ni,
        CA, COL_*mi,
        MAP(RA,CA,FUNC_)
    )
);

/**function to return relative error element wise between two arrays, ARR_1 is denominator
options for unsigned/signed, ignore errors and return max error only*/
RELERR = LAMBDA(ARR_1, ARR_2, [signed], [ignore_error], [max_only],
    LET(
        _signed, IF(ISOMITTED(signed),0,signed),
        _ie, IF(ISOMITTED(ignore_error), 0, ignore_error),
        _mo, IF(ISOMITTED(max_only),0,max_only),
        _err1, (ARR_2-ARR_1)/ARR_1,
        _err2, IF(_signed, _err1, abs(_err1)),
        _ret1, IF(_ie, IF(ISERROR(_err2),"",_err2),_err2),
        _ret2, IF(_mo,MAX(_ret1),_ret1),
        _ret2
    )
);

/**Returns root of f(x)-val=0 in bounded region [lbound, hbound] 
to the provided tolerance, function must be a LAMBDA*/
rootBisection = LAMBDA(fun, val, lbound, hbound, tol,
    LET(c, (lbound+hbound)/2,
        IF(abs(c-lbound)<tol, c,
            LET(fa, fun(lbound)-val,
                fb, fun(hbound)-val,
                fc, fun(c)-val,
                a, IF(SIGN(fa)=SIGN(fc), c, lbound),
                b, IF(SIGN(fb)=SIGN(fc), c, hbound),
                
                IF(SIGN(fa)=SIGN(fb), "#BoundsError",
                    rootBisection(fun, val, a, b, tol))
            )//_let
        )//_if
    )//_let
); //rootBisection

/**Quick Text Join - format codes: in (inches) int, dec1, dec2, dec3*/
QTJ = LAMBDA(tarr, fmt,
    LET(f, 
        IF(fmt="in","#-#/#''",
        IF(fmt="int","#,###",
        IF(fmt="dec1","#,###.0",
        IF(fmt="dec2","#,###.00",
        IF(fmt="dec3","#,###.000",
        ))))),
    TEXTJOIN(" ",,
        TEXT(tarr,f)))
);

/**Rounds input array to specified number of significant digits, 
give method of -1, 0 (default), 1 to round up, nearest, down*/
SIGROUND = LAMBDA(ARR, SD, [method],
    LET(
        _m, IF(ISOMITTED(method),0,method),
        fun, IF(_m=0, 
            LAMBDA(x, n, ROUND(x, n)), 
                IF(_m=-1, 
                LAMBDA(x, n, ROUNDDOWN(x,n)), 
                    IF(_m=1, 
                        LAMBDA(x,n, ROUNDUP(x,n))
                        )
                    )
                ),
        fun(ARR,SD-(1+INT(LOG10(ABS(ARR)))))
    )
);

/**Generates hard coded array in excel format from cell range*/
//Poorly Named, superceded by built-in ARRAYTOTEXT
T2ARR = LAMBDA(ARR,
    LET(
        rows, BYROW(ARR, LAMBDA(row, TEXTJOIN(",",,row))),
        cols, TEXTJOIN(";",,rows),
        "{"&cols&"}"
    )
);

/**Reverses the order of a column, or columns in an array*/
CREVERSE=LAMBDA(arr, SORTBY(arr, SEQUENCE(ROWS(arr), 1, ROWS(arr), -1)));

/**Reverses the order of a row, or rows in an array*/
RREVERSE=LAMBDA(arr, SORTBY(arr, SEQUENCE(1, COLUMNS(arr), COLUMNS(arr), -1)));

/** 2D wrapper for XLOOKUP - Maintains access to most of xlookup's optional inputs*/
XXLOOKUP = LAMBDA(xi,yi,xs,ys,zs,[match_mode_x],[match_mode_y],[search_mode],
    LET(
            step1, XLOOKUP(xi,xs,zs,,match_mode_x,search_mode),
            XLOOKUP(yi,ys,step1,,match_mode_y,search_mode)            
    )
);

Revised for new docstring format