Coefficients of trendline

[Phung Anh via OfficeKB.com]

I have data obtained as following:
X: x1 x2 x3 x4 x5
Y: y1 y2 y3 y4 y5
(Five X Y values)
From abovementioned data, chart is made and equation of trendline
displayed. Question here.
I want to get coefficients of that equation of trendline (Using VBA)
without displaying it on chart (obtain local maximum values intentionally).
For equation of degree of 4 is easy but for degree of 3, 2 I can not obtain
by VBA programming
It is thankful of you for your comment

 

[Greg Wilson]

Assuming Y values are in A1:E1 and X values are in A2:E2 then:

Sub GetCoeffPower2()
Dim arr As Variant
Dim i As Long

arr = Evaluate("Linest(Transpose(A1:E1), Transpose(A2:E2)^{1, 2})")
For i = LBound(arr) To UBound(arr)
MsgBox arr(i)
Next
End Sub

Sub GetCoeffPower3()
Dim arr As Variant
Dim i As Long

arr = Evaluate("Linest(Transpose(A1:E1), Transpose(A2:E2)^{1, 2, 3})")
For i = LBound(arr) To UBound(arr)
MsgBox arr(i)
Next
End Sub

If the data are in columns then remove the Transpose function.

Regards,
Greg