Retrieving stats from LINEST
Here is a working example, with data in A2:E12 (headers in row 1):
Sub TryNow()
Dim myArr As Variant
Dim i As Integer
Dim j As Integer
myArr = Application.WorksheetFunction.LinEst(Range("E2:E12 "), _
Range("A2:D12"), True, True)
MsgBox "R2 stat is " & myArr(3, 1)
If MsgBox("Want other values?", vbYesNo) = vbYes Then
For i = LBound(myArr, 1) To UBound(myArr, 1)
For j = LBound(myArr, 2) To UBound(myArr, 2)
MsgBox "Row " & i & ", Column " & j & " of the returned array is " _
& IIf(IsError(myArr(i, j)), "an error", myArr(i, j))
Next j
Next i
End If
End Sub
HTH,
Bernie
MS Excel MVP
"jstansbury" (nonotspam) wrote in message
...
I'm trying to retrieve the r2 from LINESt results via VBA--I don't want to
create a chart and pull it from the chart's trendline. I can retrieve m and b
with no problem, but when I try the following I get an error.
r2 = Application.Evaluate("=index(linest(" & Selection.AddressLocal() &
",,,TRUE),3)")
Any and all help greatly appreciated.
Thanks,
j
|