Regression Analysis in Excel

You Don't Have to be a Statistician to Run 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.

Regression Analysis Example

What if we wanted to know if the salt concentration in runoff (dependent variable) is related to the percent of paved roadway area (independent variable). Use anova.xls/regression sample data:

  1. Enter your data into Excel with the independent variable in the left column and the dependent variable in the rignt column.
  2. Click and drag over your data to select it and then click on QI Macros, Statistical Tools and Regression.
  3. regression analysis excel using QI Macros

  4. The QI Macros will perform the regression analysis calculations for you.
  5. regression calculations in the QI Macros

Evaluate the R Square value (0.951)

regression analysis goodness of fit

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%.

Evaluate the p Value

regression analysis p value from QI Macros

Since the p value ( 0 < 0.05), we reject the null hypothesis that the two variables are unrelated. In other words, there is a relation between the two variables.

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 based on the percent of paved roadway. For example, if the % of paved roadway = 1% the Salt concentration could be estimated as 17.547* (1%) +2.6765 = 20.2235 mg/l.

Residuals Output and Probability Output

In addition to the Summary Output above, the QI Macros also calculates Residuals Data and Probability Data. See the residuals analysis page for instructions on how to plot this data.

regression analysis residuals data and probability data

Learn More...

Hypothesis Testing Quick Reference Card

To create a Regression in Excel using the QI Macros...
Try It Now!

How We Can Help Download Free QI Macros Trial Buy QI Macros for Excel Now