Microsoft Office Tutorials and References

In Depth Information

**Linear Regression—Using Excel Functions**

LINEAR REGRESSION—USING EXCEL FUNCTIONS

In addition to the chart feature that allowed you to add the equation and R
2
, you can

calculate the following three values using Excel conventional functions: the slope, the

intercept, and R
2
. We can use the functions Intercept, Slope, and RSQ (for R
2

—

R-squared) to obtain the same values created on the chart. In addition you can use

the Forecast function to predict y values based on given x

’

s. In other words, it allows

sales forecasting based on a time value.

In order to save time calculating these functions, you may want to
name
some of

the cell ranges that will be used in these calculations. It will make it easier to use the

various functions without selecting the ranges again every time you want to calculate

one of the parameters.

Select cell range B2:B11 and name it x. Select cells C2:C11 and name them y. I

wrote, in cells E4, E5, and E6, the function names I am about to calculate on the

regression sheet as shown in Figure 12.3.

You can use the function f
x
.

Statistical

.

Intercept to calculate the intercept

point in cell F4. Use the function f
x
.

Statistical

.

Slope to calculate the slope in cell

RSQ to calculate the R
2
value in cell F6.

F5; and use the function f
x
.

Statistical

.

See Figure 12.4.

Notice that we used x and y for our Function Arguments. It saves us the trouble

of selecting the ranges of cells again and again.

The three values are the same values you obtained on the chart when you used

the Add Trend line menu. You can use the results obtained to forecast y values.

For example, you may want to use them to forecast month 13: y

¼

4.345

(Intercept)

13, or use the Forecast function for the following

months, as shown in Figure 12.5.

Use: f
x
.

þ

4.8379 (Slope)

×

FORECAST

The aim is to forecast sales in month 13; therefore in the x field of the menu, we

enter the data point we want to forecast the value for; in this case the value in cell

B12, for the thirteenth month. (For easier differentiation, the font formatting is dif-

ferent for the x values to be forecasted.)

Statistical

.

FIGURE 12.3
Calculating Regression Parameters