Covariance in Excel

When to use Covariance

Imagine that you'd like to know if variation in one variable is related to the variation in another. While statistical analysis cannot prove that one thing causes another, it can determine if there is a relationship between the variables which gives a direction to the analysis.

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

Systolic Blood Pressure vs Weight Covariance Example

Here is an example of the covariance 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 Covariance:

3. Evaluate the Covariance 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 Weight is 266.9 (a positive correlation).

Correlations
+ = Positive Correlation
- = Negative Correlation

If you'd like more information, run regression analysis on the data.

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.

• Single Regression Example
• 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