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" wrote:
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)

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.

On Oct 16, 5:58*pm, "joeu2004" wrote:
"REM" wrote:
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)

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.

Thanks for your reply but this also returns Type Mismatch.

"REM" wrote:
On Oct 16, 5:58 pm, "joeu2004" wrote:
"REM" wrote:
[....]
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)

Thanks for your reply but this also returns Type Mismatch.

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

On Oct 17, 12:45*am, "joeu2004" wrote:
"REM" wrote:
On Oct 16, 5:58 pm, "joeu2004" wrote:
"REM" wrote:
[....]
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)

Thanks for your reply but this also returns Type Mismatch.

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