Reply to thread

Message: [QUOTE="joeu2004, post: 7148135"] 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 [/QUOTE]

Verification