Data Analysis with Microsoft Excel: Updated for Office 2007

Chapter 8 Regression and Correlation 341

Figure 8-23
Create
Correlation
dialog box

4 Click the OK button.

Excel generates the matrix of correlations as displayed in Figure 8-24.

correlation

matrix

matrix of

correlation

probabilities

Figure 8-24

Correlation

matrix

Figure 8-24 shows two matrices. The fi rst, in cells A1:H9, is the correla-

tion matrix, which shows the Pearson correlations. The second, the matrix of

probabilities in cells A11:H19, gives the corresponding two-sided p values.

P values less than 0.05 are highlighted in red.

The most interesting numbers here are the correlations with the calculus

score, because the object of the study was to predict this score. The highest

correlation appears in cell E4 (0.491), with Alg Place, the algebra placement

test score. The other correlations, ACT Math, HS Rank, and Calc HS, are not

impressive predictors when you consider that the squared correlation gives R^2 ,

the percentage of variance explained by the variable as a regression predictor.

For example, the correlation between Calc and HS Rank is 0.324 (cell

H6); the square of this is 0.105, so regression on HS Rank would account for