Automate regression analysis in MS Excel 1

[tractorvix]

I'm not sure if this is possible, but hope you lovely people will be able to help.
I need to be able to run a linear regression analysis to test the fit of a line to the data points.
I've tried recording a macro to capture the code that I need to run the regression, but helpfully only get:


Application.Run "ATPVBAEN.XLA!Regress", , , False, False, , "", False _
, False, False, False, , False

I want to be able to change the input values but this doesn't even show the selected input values for me to be able to manipulate.

Is there anyone out there who is able to help?

Thanks
TV

 

[Chance1234]

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$D$6:$D$12"), _
ActiveSheet.Range("$C$6:$C$12"), True, False, , "", False, False, False _
, False, , False

all those false are the values. If you go through and tick each box whilst recording your macro , you will see what argument takes what.

Chance,

F, G + 2MSTG

 

[tractorvix]

Thanks for this, I thought it would be something simple that I was missing!

TV

 

[electricpete]

Another way to tackle the same problem:
Let's say you have the following input data in the upper left hand corner of the spreadsheet (A1:B6):
1 13.6912068
2 24.29970419
3 35.05729507
4 45.72211782
5 56.34519629
6 63.4788306

Run the following code:

Option Explicit
Option Base 1

Sub test1()
Dim Xrange, Yrange As Range
Dim linestoutput(2) As Double
Dim myvariant As Variant

Set Yrange = Sheets("sheet1").Range("J14:J19")
Set Xrange = Sheets("sheet1").Range("G14:G19")
myvariant = Application.WorksheetFunction.LinEst(Yrange, Xrange, 1, 0)
linestoutput(1) = myvariant(1)
linestoutput(2) = myvariant(2)
Debug.Print ("Slope is " & linestoutput(1))
Debug.Print ("Y intercept is " & linestoutput(2))
End Sub

The results is
Slope is 10.1639833727204
Y intercept is 4.19178332521368
(as expected)

I'm not sure what the differences are, but I have gotten used to using linest. It can accomodate multiple linear regression as well.

Ummm. Does anyone know how I could have done the above without using a variant data type ? The problem is that the worksheet function is set up as an array function... I don't know of any other way to retrieve the results except returning the results of that array function to a variant.

 

[electricpete]

Whoops. Typo. Should've been:
Set Yrange = Sheets("sheet1").Range("B1:B6")
Set Xrange = Sheets("sheet1").Range("A1:A6")