O.K. first post here.
I was finally upset enough with Excel's 16 variable limit that I have attempted to create a formula that lets one use more than 16 variables. Here it is. Please feel free to suggest improvements to my code.
I need help with r-squared, and standard errors.
So if anyone else has already created formulas for r-squared or standard errors for each beta, it would be great if you shared some tips for that code.
> Formula brings back an array. Variables in different columns.
> Example: Y variable in cells A11:A562; X variables in cells B11:BL562 (1st X variable has to be all ones (1) to calculate the intercept, b0).
Visually: (notice the column of 1s):
Y X variables
54 1 12 16...
12 1 80 10
15 1 79 8
18 1 10 71
84 1 15 56
64 1 13 32
...
> In cells B1 to BL1, one would have the following array based on the formula below: {=regression(A11:A562,B11:BL562)}
################
Function regression(Yvar As Object, Xvar As Object) As Variant
Dim n, i, j As Integer
Dim a() As Double
n = Yvar.Rows.Count
ReDim a(1 To n, 1)
For i = 1 To n
a(i, 1) = Yvar.Cells(i, 1).Value
Next i
Dim nn, ii, jj, oo As Integer
Dim aa() As Double
nn = Xvar.Rows.Count
oo = Xvar.Columns.Count
ReDim aa(1 To nn, 1 To oo)
For ii = 1 To nn
For jj = 1 To oo
aa(ii, jj) = Xvar.Cells(ii, jj).Value
Next jj
Next ii
regression = WorksheetFunction.Transpose(WorksheetFunction.MMult(WorksheetFunction.MInverse(WorksheetFunction.MMult(WorksheetFunction.Transpose(Xvar), Xvar)), WorksheetFunction.MMult(WorksheetFunction.Transpose(Xvar), Yvar)))
End Function
################
Bookmarks