Correlation in Excel with the QI Macros

When to Use Correlation

Imagine that you'd like to know if a person's weight is related to their systolic blood pressure. Does it increase or decrease with weight?

To understand and explore the linear relationships between two or more sets of numbers, Excel provides tools to analyze the variance (i.e., covariance) and relationships (i.e., co-relation) between two or more sets of numbers.

Here is an example of the correlation analysis in Excel using the QI Macros.

1. Select the data

Select two or more columns of data:

2. Click on the QI Macros menu and
choose Anova then Correlation:

3. Evaluate the Correlation Results:

Two variables can be positively correlated (more of one means more of another) or negatively correlated (more of one means less of another). In this case, Systolic vs systolic is 1 (perfectly correlated). Systolic vs Weight is 0.804464 (a strong positive correlation). If the correlation is greater than 0.80 (or less than -0.80), there is a strong relationship.

Correlation Results will always be between -1 and 1

1 = Positive Correlation
-1 = Negative Correlation
0 = No Correlation

Regression Analysis

If you'd like more information, run regression analysis on the data. Correlation is the "Multiple R" in the results. Excel will also calculate a p value for the null hypothesis (H₀ = no correlation.)

The QI Macros Statistical Process Control software takes two or more rows/columns of data and formats them in the ideal way for Excel to process them which saves you from having to figure out how to use Excel's Data Analysis Toolpak. Statistical functions in the QI Macros include:

• Single or Multiple Regression Example
• Correlation Example
• Covariance Example
• Other forms of hypothesis testing in Excel

Download the FREE 30-day Evaluation copy of the QI Macros Excel SPC Software for Six Sigma