Re: WLSRegression using Excel VBA
Yes, the desired outcome is to produce the 3 beta coefficients. One for the y-intercept, and for the 2 independent variables.

WLSRegression using Excel VBA
Hello, I'm using the following code to create a diagonal matrix and then to produce the Betas for a regression analysis. For some reason my output ends as 0, or I receive an #Value error stating one of my values is of the wrong data type. My analysis involves a y-intercept and 2 independent vari... Hello,

I'm using the following code to create a diagonal matrix and then to produce the Betas for a regression analysis. For some reason my output ends as 0, or I receive an #Value error stating one of my values is of the wrong data type. My analysis involves a y-intercept and 2 independent variables. To be honest, I really am using this as an ordinary least squares, so essentially my weights are all 1's, or an identity matrix. Below are the 2 functions:


Function Diagonal(W) As Variant
Dim n, i, j, k As Integer
Dim temp As Variant
n = W.Count
ReDim temp(1 To n, 1 To n)
For i = 1 To n
For j = 1 To n
If j = i Then temp(i, j) = W(i) Else temp(i, j) = 0
Next j
Next i
Diagonal = temp
End Function


&

Function WLSregression(y As Variant, X As Variant, W As Variant) As Variant
Wmat = Diagonal(W)
n = W.Count
Dim Xtrans, Xw, XwX, XxXinv, Xwy As Variant
Dim m1, m2, m3, m4 As Variant
Dim output() As Variant
Xtrans = Application.WorksheetFunction.Transpose(X)
Xw = Application.WorksheetFunction.MMult(Xtrans, Wmat)
XwX = Application.WorksheetFunction.MMult(Xw, X)
XwXinv = Application.WorksheetFunction.MInverse(XwX)
Xwy = Application.WorksheetFunction.MMult(Xw, y)
b = Application.WorksheetFunction.MMult(XwXinv, Xwy)
k = Application.WorksheetFunction.Count(b)
ReDim output(k) As Variant
For bcnt = 1 To k
output(bcnt) = b(bcnt, 1)
Next bcnt
WLSregression = Application.WorksheetFunction.Transpose(output)
End Function Read More