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
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:
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: