# Linest()

#### ehcpieterse

##### Active Member
Hi guys,

How can I save the output (coefficients, R2, etc) of the linest function into an array in VBA?

I want to do all my calcs in VBA to speed things up. I will use up to 10 variables.

Thanks

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### pgc01

##### MrExcel MVP
Hi ehcpieterse

This is an example.

The dependent variable, Y, is in A2:A10

There are 5 independent variables, X1-X5, in B2:F10. X1 in column B, X2 in column C, etc.

Don't forget that Linest returns the coefficients in reverse order: the last coefficient is the independent term or intercept, the second last is the coefficient of the first variable, and so on.

Try:

Code:
``````Sub LinestTest()
Dim vCoeff As Variant, vY As Variant, vX As Variant

vY = Range("A2:A10").Value
vX = Range("B2:F10").Value
vCoeff = Application.WorksheetFunction.LinEst(vY, vX)

MsgBox "Y=C0+C1*X1+C2*X2+C3*X3+C4*X4+C5*X5" & vbCrLf & _
"C0= " & vCoeff(6) & vbCrLf & _
"C1= " & vCoeff(5) & vbCrLf & _
"C2= " & vCoeff(4) & vbCrLf & _
"C3= " & vCoeff(3) & vbCrLf & _
"C4= " & vCoeff(2) & vbCrLf & _
"C5= " & vCoeff(1)

End Sub``````

#### ehcpieterse

##### Active Member
Thanks, it works great!

How do I get the "other" rows in the linest output. Currently we are only grabbing the "top" row (coefficients).

#### pgc01

##### MrExcel MVP
To get the additional statistics you set the 4th parameter to True. In this case Linest() returns a 2D array with all the statistics. You can see in the help the table with their posittion.

In this example I'm also displaying the value if R2 (1st value in the 3rd row)

Code:
``````Sub LinestTest()
Dim vCoeff As Variant, vY As Variant, vX As Variant

vY = Range("A2:A10").Value
vX = Range("B2:F10").Value
vCoeff = Application.WorksheetFunction.LinEst(vY, vX, , True)

MsgBox "Y=C0+C1*X1+C2*X2+C3*X3+C4*X4+C5*X5" & vbCrLf & _
"C0= " & vCoeff(1, 6) & vbCrLf & _
"C1= " & vCoeff(1, 5) & vbCrLf & _
"C2= " & vCoeff(1, 4) & vbCrLf & _
"C3= " & vCoeff(1, 3) & vbCrLf & _
"C4= " & vCoeff(1, 2) & vbCrLf & _
"C5= " & vCoeff(1, 1) & vbCrLf & _
vbCrLf & _
"R2: " & vCoeff(3, 1)

End Sub``````

#### ehcpieterse

##### Active Member
You have saved me hours!

Thanks a lot!

#### ehcpieterse

##### Active Member
Does the input into Linest have to be ranges or could one use arrays from VB? If so, how would it change the code?

#### pgc01

##### MrExcel MVP
Does the input into Linest have to be ranges or could one use arrays from VB? If so, how would it change the code?

Well, in the examples I posted that's already what we are doing. The inputs of Linest() are vX and vY that are vba arrays inside variants.

We are using the ranges just to load the values into the arrays in the variants, but Linest() is seeing no ranges, just values in arrays.

You can also not use ranges to load the arrays. Is this what you mean?

In this example I defined and loaded the values without using ranges, I'm loading the values manually. Does this help?

Code:
``````Sub LinestTest()
Dim vCoeff As Variant, vY As Variant, vX As Variant

ReDim vY(1 To 3, 1 To 1) ' dependent variable with 3 values
ReDim vX(1 To 3, 1 To 2)  ' 2 independent variables with 3 values

' Load values of the dependent variable
vY(1, 1) = 4
vY(2, 1) = 9
vY(3, 1) = 9

' Load values of the first independent variable
vX(1, 1) = 1
vX(2, 1) = 2
vX(3, 1) = 4

' Load values of the second independent variable
vX(1, 2) = 1
vX(2, 2) = 3
vX(3, 2) = 2

vCoeff = Application.WorksheetFunction.LinEst(vY, vX, , True)

MsgBox "Y=C0+C1*X1+C2*X2" & vbCrLf & _
"C0= " & vCoeff(1, 3) & vbCrLf & _
"C1= " & vCoeff(1, 2) & vbCrLf & _
"C2= " & vCoeff(1, 1) & vbCrLf & _
vbCrLf & _
"R: " & vCoeff(3, 1)

End Sub``````

#### ehcpieterse

##### Active Member
Absolutely perfect!

Replies
5
Views
92
Replies
3
Views
293
Replies
5
Views
184
Replies
3
Views
2K
Replies
4
Views
88

1,191,686
Messages
5,988,068
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back