Microsoft Office Tutorials and References

In Depth Information

**Working with Trendlines**

The following formula, in cell G3, calculates the value for
c:

=EXP(G2)

Column C shows the predicted
y values for each value of
using the calculated values for
b and

For example, the formula in cell C2 is as follows:
c.

=$G$3*EXP($F$2*A2)

Column D uses the GROWTH function in an array formula to generate predicted
y values. The

array formula, entered in D2:D11, appears like this:

{=GROWTH(B2:B11,A2:A11)}

Polynomial trendline

When you request a polynomial trendline, you also need to specify the order of the polynomial

(ranging from 2 through 6). The equation for a polynomial trendline depends on the order. The

following equation, for example, is for a third-order polynomial trendline:

y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b

Notice that there are three
c coefficients (one for each order).

Figure 17-30 shows a chart with a third-order polynomial trendline added. A four-element array

formula entered in F2:I2 calculates the values for each of three
c coefficients and the
b

coefficient. The formula is

{=LINEST(B2:B11,A2:A11^{1,2,3})}