Data Analysis with Microsoft Excel: Updated for Office 2007

T

his chapter examines the relationship between two variables using

linear regression and correlation. Linear regression estimates a linear

equation that describes the relationship, whereas correlation measures

the strength of that linear relationship.

Simple Linear Regression

When you plot two variables against each other in a scatter plot, the values

usually don’t fall exactly in a perfectly straight line. When you perform a

linear regression analysis, you attempt to fi nd the line that best estimates

the relationship between two variables (the y, or dependent, variable, and

the x, or independent, variable). The line you fi nd is called the fi tted regres-

sion line, and the equation that specifi es the line is called the regression

equation.

The Regression Equation

If the data in a scatter plot fall approximately in a straight line, you can use

linear regression to fi nd an equation for the regression line drawn over the

data. Usually, you will not be able to fi t the data perfectly, so some points

will lie above and some below the fi tted regression line.

The regression line that Excel fits will have an equation of the form

y 5 a 1 bx. Here y is the dependent variable, the one you are trying to pre-

dict, and x is the independent, or predictor, variable, the one that is doing

the predicting. Finally, a and b are called coeffi cients. Figure 8-1 shows a

line with a 510 and b 5 2. The short vertical line segments represent the

errors, also called residuals, which are the gaps between the line and the

points. The residuals are the differences between the observed dependent

values and the predicted values. Because a is where the line intercepts

the vertical axis, a is sometimes called the intercept or constant term in

the model. Because b tells how steep the line is, b is called the slope. It

gives the ratio between the vertical change and the horizontal change along

the line. Here y increases from 10 to 30 when x increases from 0 to 10,

so the slope is

b 5

Vertical change

Horizontal change

5

30210

1020

52

314 Statistical Methods