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)