Linest()

E

ehcpieterse

Active Member
Joined
Nov 16, 2006
Messages
278
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
 
Sort by date Sort by votes
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
 
Upvote 0
Thanks, it works great!

How do I get the "other" rows in the linest output. Currently we are only grabbing the "top" row (coefficients).
 
Upvote 0
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
 
Upvote 0
Does the input into Linest have to be ranges or could one use arrays from VB? If so, how would it change the code?
 
Upvote 0
Does the input into Linest have to be ranges or could one use arrays from VB? If so, how would it change the code?
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
LINEST calculations
Replies
5
Views
270
myall_blues
myall_blues
S
  • Question Question
Trendline values using Dynamic Range in Excel - comparing with LINEST function
Replies
1
Views
1K
Domenic
D
L
  • Question Question
User Defined Formula to Generate Polynomial Regression Equation
Replies
1
Views
859
Dave Patton
D
C
  • Question Question
Copy, paste, change name of file.
Replies
2
Views
277
Skyybot
Skyybot
L
  • Solved
copy and pasting rows from 1 sheet to another
Replies
3
Views
69
saboh12617
S

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top