Microsoft Office Tutorials and References

In Depth Information

**Chapter 14: Data Analysis—Multi-Regression**

CHAPTER
14

Data Analysis—Multi-Regression

Chapter 12 discussed in detail simple linear regression using only one independent

variable where

Y

¼

a

þ

bX

With Multi-Regression we want to use more than one independent variable X.

To predict or estimate Y, the dependent variable, we use

¼

þ

þ

þþ

Y

a

b
1
X
1

b
2
X
2

b
n
X
n

In Multi-Regression, multiple variables are used to predict the corresponding

value. For example, a real estate agent wanting to predict an apartment

’

s selling price

may use three variables based on historical data

—

size, location, and the number of

bathrooms

to predict that price. The prediction method will be developed based on

recently sold apartments. Predicted values from multiple regressions are linear

combinations of the input variables

—

—

that is, the independent variables (X).

Looking at the previous formula:

Y

¼

a

þ

b
1
X
1
þ

b
2
X
2
þ ::: þ

b
n
X
n
,

Where:

Y is the predicted value

a is the Y intercept

X
1
is the score on the first input variable (historical data), X
2
the score on the

second, etc. . . .

The regression coefficients (b
1
,b
2
, etc.) are equivalent to the slope in a simple

regression.

This chapter will describe how to use, read, and interpret the Data Analysis

ToolPak in Excel for Multi-Regressions. Unfortunately, Excel for Mac does not have

the Data Analysis ToolPak. For Mac Excel 2011 use the free download StatPlus:Mac

LE, the statistical tool for your Mac, at
www.analystsoft.com/en/products/stat-

plusmacle/.
This chapter does not cover the StatPlus product.

SIMPLE OR SINGLE VARIABLE REGRESSION

Consider the following example, found in the Chapter 14 web workbook in the sheet

Apartments Sales Data. In this initial example, we have two sets of information about