How Do I create a Correlation Matrix in Excel Using SigmaXL?
The correlation matrix complements the scatterplot matrix by quantifying the degree of association. The following table shows the approximate relationship between r, R-squared, and degree of association:
|Pearson Correlation Coefficient (r)||R-Squared (%)||Degree of association|
|0.9 <= |r| <= 1||> 80%||Strong|
|0.7 <= |r| <= 0.9||50% to 80%||Moderate|
||r| < 0.7||< 50%||Weak|
|Pearson Probability, p > 0.05||None|
- Open Customer Data.xls. Click Sheet 1 tab (or press F4 to activate last worksheet). Click SigmaXL > Statistical Tools > Correlation Matrix. Ensure that entire data table is selected. If not, check Use Entire Data Table. Click Next.
- Select the variable Avg No. of orders per month; shift-click on
Staff Knowledge and click Numeric Data Variables (Y) >>
- Click OK. Resulting Correlation Matrix is shown:
Correlations highlighted in red are considered significant (p-values < .05). The corresponding correlation coefficients above the p-values are also highlighted in red. (Compare these to the Scatterplot Matrix.)
Note that Spearmanís Rank Correlation complements Pearsonís Correlation, in that it provides a robust measure of association. Spearmanís rank is based on correlated ranks, which are not sensitive to outliers.
Click here to download a Free 30-Day Trial.