Regression Analysis

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar
2253 S. Oneida
Ste 3D
Denver, CO 80224

We work with companies
that want to fire up their profits using
Lean Six Sigma

Copyright © 2011

When to use regression analysis

The purpose of regression analysis is to evaluate the effects of one or more independent variables on a single dependent variable.

Regression arrives at an equation to predict performance based on each of the inputs.

To run Regression Analysis using the QI Macros

1. Select the labels and data.
2. Select Anova and Analysis Tools then Regression Analysis.
3. QI Macros will generate results.
4. Evaluate the Rsquared (>0.80 is a good fit).
5. Evaluate the F and p values
6. Get the equation for the fitted data.
7. Create residuals and normal probability charts.

Watch this Regression Analysis in Excel video

Here is an example of the results of running regression analysis in Excel using the QI Macros.

1. Select the data

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

3. The QI Macros will generate the results.

4. Evaluate the R Square value (0.951).

Analysis: If R Square is greater than 0.80, as it is in this case, there is a good fit to the data. Some statistics references recommend using the Adjust R Square value.

Interpretation: R Square of .951 means that 95.1% of the variation in salt concentration can be explained by roadway area. The adjusted R Square of .949 means 94.9%.

5. Evaluate the p Value

The p value for % Paved Roadway, 3E-13 < 0.05, rejects the null hypothesis that they are unrelated.

6. Use the equation for prediction and estimation.

Using the equation, y = Salt concentration = 2.677 + 17.547*(% paved roadway area), you could predict the salt concentration if the percent of roadway was 1% as Salt concentration = 17.547* (1) +2.6765 = 20.2235 mg/l.

7. Analyze residuals and normal probability plots.

To draw a residuals plot using the residuals data, select the data and then insert a scatter plot using Excel's chart tools:

Residuals Line Fit Plot - select the Predicted Y value and the X values and draw a scatter plot with the QI Macros:

Normal Probability Plot - Select the probability data and draw a scatter with QI Macros:

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. Here are some of the statistical analyses you can run.

• Regression Analysis in Excel Video
• 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

Download
QI Macros
30-Day Trial Now!

Or Buy It Now!