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 .
Intercept to calculate the intercept
point in cell F4. Use the function f x .
Slope to calculate the slope in cell
RSQ to calculate the R 2 value in cell F6.
F5; and use the function f x .
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
13, or use the Forecast function for the following
months, as shown in Figure 12.5.
Use: f x .
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.)
FIGURE 12.3 Calculating Regression Parameters