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

data set.

✓
The second argument is a range or array containing the X values of the

data set.

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.