太阳帆数据 Sunfine Data

Estimation of confidence interval and predicted values of the dependent variable in a linear equation

By Zhanshan Dong on August 27th, 2009

Here provides a user-defined function to estimate predicted values of the dependent variable and corresponding confidence interval for a given dataset with linear relationship. The function definition is the following:

yhat(Y, X, XNew, CI, alpha)

Where, Y includes values of the dependent variable; X includes values of independent variables (can includes multiple columns (variables)); XNew includes new values of independent variables which are used to estimate predicted values of dependent variable; CI is a boolean variable, default value TRUE for estimating confidence interval and FALSE for not estimating confidence interval; alpha is the statistical level for estimating confidence interval with a default value of 0.05.

The function return a matrix, at least including one column and multiple rows. If CI is set to TRUE, it will return values in at least three columns.

Here is the VBA source code.

option Explicit
Option Base 1

Function yhat(y, X, XNew, Optional CI As Boolean = True, _
   Optional alpha As Single = 0.05)
' this function can be called from worksheet
' Y - the dependent variable
' X - the independent variable
' XNew - the x values for estiamting y values
' CI - a boolean variable, true for calculating confidence interval of Y
' alpha - a single real, defaut value is 0.05, for T test
'
' Output
'   if calculate the confidence interval, it is a n-by-3 matrix
'      first column is y hat values
'      second column is the lower CI
'      third column is the upper CI
'   otherwise it is n row vector including yhat values
'
    Dim YPred, m, b, n, df, sres, sxx, xbar, t, i, temp
    With WorksheetFunction
        m = .Index(.LinEst(y, X, True, True), 1, 1)
        b = .Index(.LinEst(y, X, True, True), 1, 2)
        n = .Count(X)
        df = .Index(.LinEst(y, X, True, True), 4, 2)
        sres = .Index(.LinEst(y, X, True, True), 3, 2)
        sxx = .DevSq(X)
        xbar = .Average(X)
        t = .TInv(alpha, df)
    End With
    n = WorksheetFunction.Count(XNew)
    If CI Then
        ReDim YPred(n, 3)
        For i = 1 To n
            YPred(i, 1) = m * XNew(i) + b
            temp = t * sres * Sqr(1 / n + (XNew(i) - xbar) ^ 2 / sxx)
            YPred(i, 2) = YPred(i, 1) + temp
            YPred(i, 3) = YPred(i, 1) - temp
        Next i
    Else
        ReDim YPred(n)
        For i = 1 To n
            YPred(i) = m * XNew(i) + b
        Next i
    End If
    yhat = YPred
End Function

2 Responses to “Estimation of confidence interval and predicted values of the dependent variable in a linear equation”

1. Brit Amateur says:

   2009/10/17 at 3:07 am

   mm… informative )

   Log in to Reply
2. Amateur Liberal Pics says:

   2009/10/28 at 3:03 am

   ehh… good one

   Log in to Reply