Linest()

[ehcpieterse]

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

 

[pgc01]

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:

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]

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]

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)

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]

You have saved me hours!

Thanks a lot!

 

[ehcpieterse]

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]

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?

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]

Absolutely perfect!