yamdizzle
02-01-2013, 02:57 PM
I wanted to write a regression macro, but seems like I have gotten too rusty in VBA.
I get something wrong
Sub Regression()
Dim x As Range
Dim y As Range
On Error Resume Next
Set x = Application.InputBox(Prompt:="Select the range of independent variable matrix", _
Title:="Independent Variable", Type:=8)
Set y = Application.InputBox(Prompt:="Select the range of dependent variable vector", _
Title:="Dependent Variable", Type:=8)
On Error GoTo 0
Dim Destination As Range
Set Destination = ActiveCell.Offset(1, 0)
Set Destination = Destination.Resize(1, 2)
With WorksheetFunction
Destination.Value = .MMult(.MMult(.MInverse(.MMult(.Transpose(x), x)), .Transpose(x)), y)
End With
End Sub
so I get only the intercept at both cells not the whole result of (X' X)^-1 X' Y
:banghead:
I think it is something to do with storing x and y?:dunno
any help would be appreciated
I get something wrong
Sub Regression()
Dim x As Range
Dim y As Range
On Error Resume Next
Set x = Application.InputBox(Prompt:="Select the range of independent variable matrix", _
Title:="Independent Variable", Type:=8)
Set y = Application.InputBox(Prompt:="Select the range of dependent variable vector", _
Title:="Dependent Variable", Type:=8)
On Error GoTo 0
Dim Destination As Range
Set Destination = ActiveCell.Offset(1, 0)
Set Destination = Destination.Resize(1, 2)
With WorksheetFunction
Destination.Value = .MMult(.MMult(.MInverse(.MMult(.Transpose(x), x)), .Transpose(x)), y)
End With
End Sub
so I get only the intercept at both cells not the whole result of (X' X)^-1 X' Y
:banghead:
I think it is something to do with storing x and y?:dunno
any help would be appreciated