Hi, I'm trying to obtain one coefficient and its error term out of a multiple regression (linest) starting from a single series of data and using only VBA. This is the code I've written and that doesn't work. I believe my problem is determining the range data on which the regression is to be performed. Any help is more than welcome!
Function MultipleRegression(Series As Range)
n = Series.Rows.Count
Dim y(1 To n), x(1 To n, 2) As Variant
For i = 1 To n
y(i) = Series(i + 1) - Series(i)
x(i, 1) = Series(i + 2) - Series(i + 1)
x(i, 2) = Series(i + 3) - Series(i + 2)
Next i
Set y = Range(y(1), y
)
Set x = Union(Range(x(1, 1), x(n, 1)), Range(x(1, 2), x(n, 2)))
Coefficient = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(y, x, True, True), 1, 1)
CoefficientError = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(y, x, True, True), 2, 1)
Result = Coefficient / CoefficientError
MultipleRegression = Result
End Function
Function MultipleRegression(Series As Range)
n = Series.Rows.Count
Dim y(1 To n), x(1 To n, 2) As Variant
For i = 1 To n
y(i) = Series(i + 1) - Series(i)
x(i, 1) = Series(i + 2) - Series(i + 1)
x(i, 2) = Series(i + 3) - Series(i + 2)
Next i
Set y = Range(y(1), y
)Set x = Union(Range(x(1, 1), x(n, 1)), Range(x(1, 2), x(n, 2)))
Coefficient = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(y, x, True, True), 1, 1)
CoefficientError = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(y, x, True, True), 2, 1)
Result = Coefficient / CoefficientError
MultipleRegression = Result
End Function
