Microsoft Office Tutorials and References

In Depth Information

**Working with Trendlines**

To enter this formula, start by selecting two cells (in this example, G2:H2). Then type the formula

(without the curly brackets) and press Ctrl+Shift+Enter. Cell G2 displays the slope; cell H2

displays the
y-intercept.

Calculating predicted values

After you know the values for the slope and
y-intercept, you can calculate the predicted
y value

for each
Figure 17-25 shows the result. Cell E2 contains the following formula, which is copied
x.

down the column:

=(B2*$G$2)+$H$2

Figure 17-25:
Column E contains formulas that calculate the predicted values for
y.

The calculated values in column E represent the values used to plot the linear trendline.

You can also calculate predicted values of
y without first computing the slope and
y-intercept.

You do so with an array formula that uses the TREND function. Select D2:D11, type the following

formula (without the curly brackets), and press Ctrl+Shift+Enter:

{=TREND(C2:C11,B2:B11)}

Linear forecasting

When your chart contains a trendline, you can instruct Excel to forecast and plot additional

values. You do this on the Trendline Options tab in the Format Trendline dialog box. Just specify the

number of periods to forecast. Figure 17-26 shows a chart that forecasts results for two

subsequent periods.