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.

Search JabSto ::

Custom Search