Coefficients of trendline

  • Thread starter Phung Anh via OfficeKB.com
  • Start date

P

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
 
Ad

Advertisements

G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top