Microsoft Office Tutorials and References

In Depth Information

Fig. 7.6
Dialogue Box for SAT vs. FROSH GPA Correlations

7.4 Using Excel to Create a Correlation Matrix

in Multiple Regression

The ﬁnal step in multiple regression is to ﬁnd the correlation between all of the

variables that appear in the regression equation.

In our example, this means that we need to ﬁnd the correlation between each of

the six pairs of variables:

To do this, we need to use Excel to create a “correlation matrix.” This matrix

summarizes the correlations between all of the variables in the problem.

Objective
: To use Excel to create a correlation matrix between

the four variables in this example.

To use Excel to do this, use these steps:

Data (top of screen under “Home” at the top left of screen)

Data Analysis

Correlation (scroll
up
to highlight this formula; see Fig.
7.6
)

OK

Input range: A6: D17

(Note that this input range includes the labels at the top of the FOUR variables

(FROSH GPA, READING SCORE, WRITING SCORE, MATH SCORE) as

well as all of the ﬁgures in the original data set.)

Grouped by: Columns

Put a check in the box for: Labels in the First Row (since you included the labels at

the top of the columns in your input range of data above)

Output range (click on the button to its left, and enter): A42 (see Fig.
7.7
)

OK

Search JabSto ::

Custom Search