Microsoft Office Tutorials and References
In Depth Information
HIGHER-ORDER POLYNOMIAL TRENDLINES
Figure 5-14: A chart displaying a second-polynomial trendline
Column C shows the predicted y values for each value of x, using the calculated
values for b and the three c coefficients. For example, the formula in cell C2 is
=($E$2*A2^2)+($F$2*A2^1)+$G$2
HIGHER-ORDER POLYNOMIAL TRENDLINES
A polynomial trendline can use between two and six coefficients. Higher-order
trendlines are often able to describe data sets that have complex or multiple curves.
Figure 5-15 shows a chart with a third-order polynomial trendline. The equation
for this trendline is similar to the second-order polynomial trendline equation, but
with an additional coefficient:
y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
Formulas in E2:H2 calculate the values for each of c coefficients and the b
constant. The formulas are as follows:
E2:
=INDEX(LINEST(B2:B11,A2:A11^{1,2,3}),1)
F2:
=INDEX(LINEST(B2:B11,A2:A11^{1,2,3}),1,2)
G2:
=INDEX(LINEST(B2:B11,A2:A11^{1,2,3}),1,3)
H2:
=INDEX(LINEST(B2:B11,A2:A11^{1,2,3}),1,4)
Search JabSto ::




Custom Search