Excel：Calculate ACF and PACF in Excel

Function acf(data As Range, k As Integer) As Double
Dim n As Integer
n = data.Rows.Count
Dim xbar As Double
xbar = Application.Average(data)
Dim sum1 As Double
sum1 = Application.SumProduct(data, data) - n * xbar * xbar
Dim sum2 As Double
sum2 = 0
For i = 1 To n - k
    sum2 = sum2 + (data.Cells(i) - xbar) * (data.Cells(i + k) - xbar)
Next
acf = sum2 / sum1
End Function

Function pacf(data As Range, k As Integer) As Double
Dim xx() As Double
ReDim xx(1 To k, 1 To k) As Double
For i = 1 To k
    For j = 1 To k
        xx(i, j) = acf(data, Abs(j - i))
    Next
Next
Dim yy() As Double
ReDim yy(1 To k, 1 To 1) As Double
For i = 1 To k
    For j = 1 To 1
        yy(i, 1) = acf(data, Abs(i))
    Next
Next 
pacf = Application.Index(Application.MMult(Application.MInverse(xx), yy), k, 1)
End Function

Tags: Excel

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