I try to find the trend-line coefficients for a 5th order polynomial in VBA, using two different methods to find it, but the results are totally different. I have verified the answer, method 1 is the correct one. Then how can I get the correct coefficients by method 2?
or are there any different method to get the answer?
y=a5*x^5+a4*x^4+a3*x^3+a2*x^2+a1*x+a0
method 1:
.Range("A4:F4").FormulaArray = _
"=LINEST(R[4]C[3]:R[416]C[3],R[4]C[2]:R[416]C[2]^{1,2,3,4,5})"
.Range("H4:M4").FormulaArray = _
"=LINEST(R[4]C[3]:R[392]C[3],R[4]C[2]:R[392]C[2]^{1,2,3,4,5})"
result 1:
a5=-7.18753E-10
a4=2.67996E-07
a3=-3.28137E-05
a2=0.001175343
a1=0.073740487
a0=-0.242915521
method 2:
a5 = WorksheetFunction.Index(WorksheetFunction.LinEst(.Range("d8:d400"), Application.Power(.Range("c8:c400").Value, Array(1, 5))), 1)
a4 = WorksheetFunction.Index(WorksheetFunction.LinEst(.Range("d8:d400"), Application.Power(.Range("c8:c400").Value, Array(1, 4))), 1)
a3 = WorksheetFunction.Index(WorksheetFunction.LinEst(.Range("d8:d400"), Application.Power(.Range("c8:c400").Value, Array(1, 3))), 1)
a2 = WorksheetFunction.Index(WorksheetFunction.LinEst(.Range("d8:d400"), Application.Power(.Range("c8:c400").Value, Array(1, 2))), 1)
a1= WorksheetFunction.Index(WorksheetFunction.LinEst(.Range("d8:d400"), .Range("c8:c400")), 1)
a0= WorksheetFunction.Index(WorksheetFunction.LinEst(.Range("d8:d400"), .Range("c8:c400")), 2)
result 2:
a5=-2.09188E-11
a4=-3.77071E-09
a3=-7.50941E-07
a2=-0.000194511
a1=0.049233564
a0=0.766998923
Thanks.
Leeann
Bookmarks