Microsoft Office Tutorials and References
In Depth Information
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data
Getting It Straight: Using SLOPE and
INTERCEPT to Describe Linear Data
As I discussed earlier, many data sets can be modeled by a straight line — in
other words, the data is linear. The line that models the data, known as the
linear regression line, is characterized by its slope and its Y intercept. Excel
provides the SLOPE and INTERCEPT functions to calculate the slope and Y
intercept of the linear regression line for a set of data pairs.
The SLOPE and INTERCEPT functions both take the same two arguments:
✓ The first argument is a range or array containing the Y values of the
✓ The second argument is a range or array containing the X values of the
The two ranges must contain the same number of values; otherwise, an error
occurs. Follow these steps to use either of these functions:
1. In a blank worksheet cell, enter =SLOPE( or =INTERCEPT( to start the
function entry .
2. Drag the mouse over the range containing the Y data values, or enter
the range address.
3. Enter a comma ( ,).
4. Drag the mouse over the range containing the X data values, or enter
the range address.
5. Type a ) and press Enter.
When you know the slope and Y intercept of a linear regression line, you can
calculate predicted values of Y for any X using the formula Y = mX + b where m
is the slope and b is the Y intercept. But Excel’s FORECAST and TREND
functions can do this for you.
Knowing the slope and intercept of a linear regression line is one thing, but
what can you do with this information? One very useful thing is to actually
draw the linear regression line along with the data points. This method of
graphical presentation is commonly used; it lets the viewer see how well the
data fits the model.
To see how this is done, look at the worksheet in Figure 11-2. Columns A and
B contain the X and Y data, and the chart shows a scatter plot of this data.