Autocorrelation in Excel

You can probably find very good websites to perform regression analysis in excel. I believe that for economic variables it is important to understand when auto-correlation is present in the data. With auto-correlation, the variables move together, but the error term from the regression is highly correlated with the prior error, violating a fundamental necessity of a standard regression. I don’t think there is an automatic way to adjust for auto-correlation, so I demonstrate how to make this adjustment in the files below. The regression analysis also makes extensive use of the OFFSET function so you can make all of the analysis very flexible.


Step by step process:


1. Run Normal OLS (In excel, get the slope and the intercept)


2. Compute the residuals from the regression


3. Run a regression of the residual versus the lagged residual


4. Use the slope of the regression in step 3 for transposing the data


You can do this in logs or in absolute amounts.