Regression Analysis, Correlation & Covariance in Excel

When to use regression, correlation and covariance

Imagine that you'd like to know what kind of behaviors lead to winning baseball teams, or what kind of behaviors or treatments lead to optimal results for patients. In baseball, you would want to know if walks, hits, errors, etc. lead to winning teams. In healthcare, you might want to know if walking 30 minutes a day leads to better cholesterol levels. 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.

Correlation and Covariance: To understand and explore the relationships between two or more sets of numbers, Excel provides means to analyze the variance (i.e., co-variance) and linear relationships (i.e., co-relation) between two or more sets of numbers. In the healthcare example, correlation and covariance could identify relationships between minutes walking and cholesterol levels (HDL, LDL, VLDL).

Regression:Excel also offers the ability to handle regression analysis on two or more sets of data. Regression analysis determines how one or more independent variables affect a single dependent variable. In the baseball example, how do walks, hits, and errors (independent variables) affect wins (dependent variable)?

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