Compute Cook's distance in M$ Excel

excel mmult example excel計算距離矩陣excel矩陣運算

In statistics, the Cook's distance is a commonly used estimate of the influence of a data point when doing least squares regression. Cook's distance measures the effect of deleting a given observation. Data points with large residuals (outliers) and/or high leverage may distort the outcome and accuracy of a regression. Points with a Cook's distance of 1 or more are considered to merit closer examination in the analysis.

Sub cook()

Dim xArray As Variant, yArray As Variant, zArray As Variant

Dim Fn As Object

Set Fn = Application.WorksheetFunction

Range("A1:D26").Select

nrow = Selection.Rows.Count

ncol = Selection.Columns.Count

n = nrow - 1

p = ncol - 2

xArray = Range(Cells(2, 2), Cells(nrow, ncol)).Value

yArray = Range(Cells(2, 1), Cells(nrow, 1)).Value

xx = Fn.MMult(Fn.Transpose(xArray), xArray)

xy = Fn.MMult(Fn.Transpose(xArray), yArray)

xxinverse = Fn.MInverse(xx)

beta = Fn.MMult(xxinverse, xy)

tbeta = Fn.Transpose(beta)

yhat = Fn.MMult(xArray, beta)

hArray = Fn.MMult(Fn.MMult(xArray, xxinverse), Fn.Transpose(xArray))

SSE = Fn.Index(Fn.MMult(Fn.Transpose(yArray), yArray), 1, 1) -Fn.Index(Fn.MMult(Fn.MMult(Fn.Transpose(yArray), hArray), yArray), 1, 1)

MSE = SSE / (n - p - 1)

Cells(1, ncol + 1) = "yhat"

Cells(1, ncol + 2) = "residual"

Cells(1, ncol + 3) = "hii"

Cells(1, ncol + 4) = "sred"

Cells(1, ncol + 5) = "cook"

For i = 2 To nrow

residual = Fn.Index(yArray, i - 1, 1) - Fn.Index(yhat, i - 1, 1)

hii = Fn.Index(hArray, i - 1, i - 1)

sred = residual / (MSE * (1 - Fn.Index(hArray, i - 1, i - 1))) ^ 0.5

cook = sred ^ 2 / 3 * hii / (1 - hii)

Cells(i, ncol + 1) = Fn.Index(yhat, i - 1, 1)

Cells(i, ncol + 2) = residual

Cells(i, ncol + 3) = hii

Cells(i, ncol + 4) = sred

Cells(i, ncol + 5) = cook

Next

Range(Cells(1, ncol + 1), Cells(nrow, ncol + 5)).Select

Selection.NumberFormatLocal = "0.0000_ "

End Sub
excel mmu
lt example excel計算距離矩陣

Tags: Excel, SPSS, Teaching

• 編輯文章
• 此文章網址
• 轉寄此文章
• 目前有2 意見