Excel Regression Functions

xl_regression.txt

/*
Append a vector of ones to x inputs.

For use in linear regression, The design matrix is an n by p matrix of 
explanatory variables, with the columns representing the constant term and the
covariates. Thus a typical case for a linear regression model fits
response to multiple inputs (explanatory variables). Users enter the input and
output data into an excel worksheet. The design matrix must also contain the
constants for the y-intercept. This function adds a vector of ones to the input
variables.

Args:
    X - An n by p matrix of explanatory variables
Return:
    The n by 1+p design matrix
*/
design_matrix=LAMBDA(X,
    LET(ones, SEQUENCE(ROWS(X), 1, 1, 0), HSTACK(ones, X))
);

/*
My replacement for the Excel LINEST function.

This:
    - orders the coefficients from low to high
    - outputs a column vector that can be used to calculate the y-hat values
    from input. Xb = y_hat

Args:
    D - an n by p design matrix
    y - an n by 1 vector of response values
Return:
    The regression coefficients as a p by 1 vector
*/
mylinest=LAMBDA(D,y,
    LET(
        Xt, TRANSPOSE(D),
        XXt, MMULT(Xt, D),
        XXtinv, MINVERSE(XXt),
        Xty, MMULT(Xt, y),
        b, MMULT(XXtinv, Xty),
        b
    )
);

/*
As for `mylinest`, accepting a matrix of inputs. Depends on the `design_matrix`
function defined above

Args:
    X - matrix of inputs
    y - the responses
*/
mylinest2=LAMBDA(X,y,
    LET(
        D, design_matrix(X),
        Dt, TRANSPOSE(D),
        DDt, MMULT(Dt, D),
        DDtinv, MINVERSE(DDt),
        Dty, MMULT(Dt, y),
        b, MMULT(DDtinv, Dty),
        b
    )
);