# 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:

- Enter your data into Excel with the independent variable in the left column and the dependent variable in the rignt column.
- Click and drag over your data to select it and then click on QI Macros, Statistical Tools and Regression.
- The QI Macros will perform the regression analysis calculations for you.

### 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 Adjusted 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

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, Probability Output and Charts

In addition to the Summary Output above, the QI Macros also calculates Residuals and Probability Data and creates scatter plots in Excel for you.

### Learn More...

- Perform Multiple Regression Analysis Using the QI Macros
- Plot residuals
- Other Statistical Tools for Excel

Hypothesis Testing Quick Reference Card