LinEst using arrays

  • Thread starter Thread starter REM
  • Start date Start date
R

REM

Hi,
I am trying to get the following to work,

varr = Application.LinEst(ArrayY, Application.Power( _
ArrayX, Array(1, 2)), True, 0)

but I get a type mismatch error.

The arrays are dimensioned as Variant and both redimensioned with:
ReDim ArrayX(1 To NoofVals)

Also, can I assign the regression coeffficient,R2, to a variable using
something similar to the above?
Many thanks,
Rod
 
REM said:
I am trying to get the following to work,
varr = Application.LinEst(ArrayY, Application.Power( _
ArrayX, Array(1, 2)), True, 0)
but I get a type mismatch error.
The arrays are dimensioned as Variant and both redimensioned with:
ReDim ArrayX(1 To NoofVals)
Click to expand...

You are trying to mimick the Excel expression
LINEST(ArrayY,ArrayX^{1,2},TRUE,0). You cannot do that directly in VBA.
Try the following (untested):

ReDim ArrayX2(1 to NoofVals, 1 to 2)
for i = 1 to NoofVals: ArrayX2(i,1) = ArrayX(i): Next
for i = 1 to NoofVals: ArrayX2(i,2) = ArrayX(i)^2: Next
varr = Application.LinEst(ArrayY,ArrayX2,True,0)

PS: I think WorksheetFunction.LinEst is preferred. But Application.LinEst
might return errors that WorksheetFunction.LinEst does not(!). At least
that has been my experience with some other WorksheetFunction methods.
 
You are trying to mimick the Excel expression
LINEST(ArrayY,ArrayX^{1,2},TRUE,0).  You cannot do that directly in VBA..
Try the following (untested):

ReDim ArrayX2(1 to NoofVals, 1 to 2)
for i = 1 to NoofVals: ArrayX2(i,1) = ArrayX(i): Next
for i = 1 to NoofVals: ArrayX2(i,2) = ArrayX(i)^2: Next
varr = Application.LinEst(ArrayY,ArrayX2,True,0)

PS:  I think WorksheetFunction.LinEst is preferred.  But Application.LinEst
might return errors that WorksheetFunction.LinEst does not(!).  At least
that has been my experience with some other WorksheetFunction methods.
Click to expand...

Thanks for your reply but this also returns Type Mismatch.
 
REM said:
The arrays are dimensioned as Variant and both redimensioned with:
ReDim ArrayX(1 To NoofVals)
Click to expand...

You are trying to mimick the Excel expression
LINEST(ArrayY,ArrayX^{1,2},TRUE,0). [....]
Try the following (untested):

ReDim ArrayX2(1 to NoofVals, 1 to 2)
for i = 1 to NoofVals: ArrayX2(i,1) = ArrayX(i): Next
for i = 1 to NoofVals: ArrayX2(i,2) = ArrayX(i)^2: Next
varr = Application.LinEst(ArrayY,ArrayX2,True,0)
Click to expand...

Thanks for your reply but this also returns Type Mismatch.
Click to expand...

The arrays must be 2-dimensional, like Range variables would be.

See the example procedures below. Compare with the array formula
=LINEST(C6:C15,A6:A15^{1,2}), selecting 3 horizontal cells and pressing
ctrl+shift+Enter.

FYI, for demonstration purposes, I set up the worksheet as follows (using
copy and paste-special-value to "freeze" the values):

B1,"m1": =RAND()
B2,"m2": =RAND()
B3,"b": =RAND()*10

A6:A15,"x": 1 through 10
B6,"x^2": =A6^2
copy B6 down through B15
C6,"y": =($B$1*A6+$B$2*A6^2+$B$3)*(1+RANDBETWEEN(-10,10)/100)
copy C6 down through C15

Thus, the curve described by C6:C15 is +/-10% of the intended 2nd-order
power curve based on m1, m2 and b. However, that does not mean that the
actual trendline will use the coefficients m1, m2 and b. This is just a
means for ensuring that __some__ 2nd-order power trendline will fit the data
closely.

VBA procedures....


Sub doit()
Dim y(1 To 10, 1 To 1) As Double 'or Variant
Dim x(1 To 10, 1 To 2) As Double 'or Variant
Dim i As Long, s As String, v As Variant
' x = Range("a6:a15")
' y = Range("c6:c15")
For i = 1 To 10
y(i, 1) = Range("c6").Cells(i)
x(i, 1) = Range("a6").Cells(i)
x(i, 2) = Range("a6").Cells(i) ^ 2
Next
v = WorksheetFunction.LinEst(y, x)
s = v(1)
For i = 2 To 3
s = s & " " & v(i)
Next
MsgBox LBound(v, 1) & ":" & UBound(v, 1) & _
vbNewLine & s
End Sub


Sub doit2()
Dim s As String, v As Variant
' x = Range("a6:a15")
' y = Range("c6:c15")
' Range("b6:b15").formula = "=a6^2"
v = WorksheetFunction.LinEst(Range("c6:c15"), Range("a6:b15"))
s = v(1)
For i = 2 To 3
s = s & " " & v(i)
Next
MsgBox LBound(v, 1) & ":" & UBound(v, 1) & _
vbNewLine & s
End Sub
 
That works! My mistake was not to correctly dimension ArrayY as a 2
dimensional array.
Very many thanks for your help.
Rod

REM said:
The arrays are dimensioned as Variant and both redimensioned with:
ReDim ArrayX(1 To NoofVals)
You are trying to mimick the Excel expression
LINEST(ArrayY,ArrayX^{1,2},TRUE,0). [....]
Try the following (untested):
ReDim ArrayX2(1 to NoofVals, 1 to 2)
for i = 1 to NoofVals: ArrayX2(i,1) = ArrayX(i): Next
for i = 1 to NoofVals: ArrayX2(i,2) = ArrayX(i)^2: Next
varr = Application.LinEst(ArrayY,ArrayX2,True,0)
Click to expand...
Click to expand...
Thanks for your reply but this also returns Type Mismatch.
Click to expand...

The arrays must be 2-dimensional, like Range variables would be.

See the example procedures below.  Compare with the array formula
=LINEST(C6:C15,A6:A15^{1,2}), selecting 3 horizontal cells and pressing
ctrl+shift+Enter.

FYI, for demonstration purposes, I set up the worksheet as follows (using
copy and paste-special-value to "freeze" the values):

B1,"m1":      =RAND()
B2,"m2":      =RAND()
B3,"b":       =RAND()*10

A6:A15,"x":   1 through 10
B6,"x^2":     =A6^2
copy B6 down through B15
C6,"y":       =($B$1*A6+$B$2*A6^2+$B$3)*(1+RANDBETWEEN(-10,10)/100)
copy C6 down through C15

Thus, the curve described by C6:C15 is +/-10% of the intended 2nd-order
power curve based on m1, m2 and b.  However, that does not mean that the
actual trendline will use the coefficients m1, m2 and b.  This is just a
means for ensuring that __some__ 2nd-order power trendline will fit the data
closely.

VBA procedures....

Sub doit()
Dim y(1 To 10, 1 To 1) As Double  'or Variant
Dim x(1 To 10, 1 To 2) As Double  'or Variant
Dim i As Long, s As String, v As Variant
' x = Range("a6:a15")
' y = Range("c6:c15")
For i = 1 To 10
    y(i, 1) = Range("c6").Cells(i)
    x(i, 1) = Range("a6").Cells(i)
    x(i, 2) = Range("a6").Cells(i) ^ 2
Next
v = WorksheetFunction.LinEst(y, x)
s = v(1)
For i = 2 To 3
   s = s & "    " & v(i)
Next
MsgBox LBound(v, 1) & ":" & UBound(v, 1) & _
    vbNewLine & s
End Sub

Sub doit2()
Dim s As String, v As Variant
' x = Range("a6:a15")
' y = Range("c6:c15")
' Range("b6:b15").formula = "=a6^2"
v = WorksheetFunction.LinEst(Range("c6:c15"), Range("a6:b15"))
s = v(1)
For i = 2 To 3
   s = s & "    " & v(i)
Next
MsgBox LBound(v, 1) & ":" & UBound(v, 1) & _
    vbNewLine & s
End Sub
Click to expand...
 
Back
Top