Having trouble using LINEST in Excel 2007 VBA

C

Chef Scottie

I'm trying to run regressions in VBA using the code

ols = Application.WorksheetFunction.LinEst(Y, x, True, True)

As I understand it the second TRUE generates the stats for the regression,
but I only get the coefficients. (It shows NA for the stats).

Help?!
 
P

Peter T

In this usage of Linest I get the same in Excel and VBA, eg

Sub test2()
Dim i As Long
Dim Y(1 To 5) As Double
Dim x(1 To 5) As Double
Dim ols

For i = 1 To 5
Y(i) = 2 * (i ^ 2) + (3 * i) + 4
x(i) = i
Next

ols = Application.WorksheetFunction.LinEst(Y, x, True, True)
Range("G1:H5") = ols

Range("A1:A5") = Application.Transpose(x)
Range("B1:B5") = Application.Transpose(Y)
Range("D1:E5").FormulaArray = "=LINEST($B$1:$B$5,$A$1:$A$5,TRUE,TRUE)"

End Sub

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top