Wednesday, 8 February 2017

Regression Modeling Spreadsheet (automatic stepwise multiple linear regression)

This spreadsheet does:
  • Multiple linear regression.
  • Can use linear, square and interaction terms for all the variables you enter.
  • Stepwise multiple linear regression to find the best model automatically.
  • Provides results and many useful diagnostic plots.
  • Provides a suggestion of which independent terms may be confounded (useful for regression of experimental design data).

Download a copy of version 1.0 here.

Why build a spreadsheet to do regression modeling when this ability already exists in Excel? The built in function in Excel is powerful, but not easy to use. There is a lot of manual work if you are changing the independent variables. If you have a lot of independent variables and are testing multiple models, the Excel function is extremely time consuming. The data analysis add-in is easier to use and provides results and plots, but is still slow for doing multiples tests of models and does not do automatic stepwise multiple regression.

This spreadsheet is easy to use!

Enter your data for the dependent and independent variables on the sheet named Data. Place the name of the variables in the first row.

In the Inputs sheet, press the "New Data" button. This sets up some of the formulas. 
Select which are the dependent and independent variables. Decide if you want to include square and interaction terms. Select a p-value criteria - only terms with a p-value less than this will be added to the model by the automatic stepwise macro.

Press the Auto10 button to automatically do 10 steps of stepwise multiple linear regression. Press it again until no more terms are added.

The results are also shown on the Inputs sheet.

The Plots sheet has many result and diagnostic plots so you can validate your regression model.

You can also add 1 term at a time by pressing the Fwd1 button or removing terms above the criteria p-value by pressing the Back1 button.

Instead of using the Auto button you can manually add terms to the model 1 at a time by placing a 1 next to the Term name in the "in Model" column.

More detailed instructions are contained on the Instructions sheet within the spreadsheet. 

No comments:

Post a Comment