Microsoft Office Tutorials and References

In Depth Information

The formula that follows is a multicell array formula that displays its result (the slope and
y
-intercept) in two

cells:

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

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. Note

that these are the same values displayed in the chart for the linear trendline.

Calculating predicted values

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

ure 17-26 shows the result. Cell E2 contains the following formula, which is copied down the column:

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

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

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

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: