PolyPred

Description

PolyPred calculates prediction intervals (PIs) for polynomial regression, just as PolyConf calculates confidence intervals. It uses Excel’s LINEST to do the regression calculations but first filters out the non-numeric data from the input.

Syntax

PolyPred(Y, X, xu, [p=1], [S], [s_u]) where

• Y and X are the dependent and independent variables, as row or column vectors
• xu is the value at which to calculate the Y prediction interval
• p is the polynomial order, defaulting to 1 (ie fitting a line to the X/Y data)
• S is the vector of standard deviations of Y
• s_u is a scalar: the standard deviation of a future $y$ value measured at xu, in the same units as S

If S is provided then weighted least-squares is performed and the value of s_u is required.

The function returns five values as a row vector:

1. Predicted (fitted) value $\hat{y}_u$ at $x = x_u$
2. The standard error of the residuals on $y$ at $x = x_u$
3. The 95% prediction interval for future $y$ values at $x = x_u$
4. The lower limit of the 95% PI
5. The upper limit of the 95% PI

One can plot the last two values as a function of X to create a prediction band to a plot of the data. The example below will illustrate that use case.

Example

The prediction band contains future values of $y$ with the stated level of confidence (95% in this case). Another way of saying the same thing is that it is an interval for residuals for future values around the fitted polynomial. It is wider than the confidence band, which is a confidence interval for the true value of $\mu_y$ at $x = x_u$.

Download: PolyPred.xlsx

Source Code

Helpers used: _PolyFit

PolyPred = LAMBDA(
    Y, // dependent values as row or column vector
    X, // independent values as row or column vector
    // X and Y must be same length but can contain non-numbers and empty cells
    xu, // value at which to predict y value
    [p], // optional polynomial order (default p=1)
    [S], // optional std devs of Y for weighted LS
    [s_u], // std dev of a future y value at xu (required if S is given)
    LET(
        p, IF(ISOMITTED(p), 1, p), // default value
        fit, IF(ISOMITTED(S), _PolyFit(Y, X, p, 1), _PolyFit(Y, X, p, S)),
        b, TAKE(fit, 1),
        inner, CHOOSEROWS(fit, SEQUENCE(p + 1, , 2)),
        sres, INDEX(fit, p + 3, 1),
        DOF, INDEX(fit, p + 3, 2),
        // predict y value (yhat) and its uncertainty
        // (need to avoid error when xu=0)
        X0, IF(xu <> 0, TRANSPOSE(xu ^ SEQUENCE(, p + 1, 0)), EXPAND(1, p + 1, 1, 0)),
        yhat, SUMPRODUCT(TRANSPOSE(b), X0),
        // variance of a future y value, relative to unit weight
        var_u, IF(ISOMITTED(S), 1, s_u ^ 2),
        se_yhat, SQRT(var_u + MMULT(MMULT(TRANSPOSE(X0), inner), X0)) * sres,
        MOE, T.INV.2T(5%, DOF) * se_yhat,
        LL, yhat - MOE,
        UL, yhat + MOE,
        IF(
            AND(NOT(ISOMITTED(S)), ISOMITTED(s_u)),
            "ERROR: s_u is required when S is given",
            IF(ISTEXT(INDEX(fit, 1, 1)), INDEX(fit, 1, 1), HSTACK(yhat, se_yhat, MOE, LL, UL))
        )
    )
);