Linear regression is a commonly used tool in statistics. It’s based on a series of assumptions, one of which is that residual values aren’t correlated. To test this, one can use the Durbin-Watson test. Fortunately, this is quite simple in Excel. Let’s learn how to calculate the Durbin-Watson statistic in Microsoft Excel.
How to Calculate the Durbin-Watson Statistic in Excel
Calculating the Durbin-Watson statistic is simple, but it requires a bit of initial work. Since the statistic tests linear regressions, you’ll first need to have a linear regression in place inside Excel. Linear regressions like this need X1, X2, and Y values to work properly. Input the values into three columns, and create a regression with residuals using the Data Analysis feature on the Data tab.
You’ll see a regression output like the one above. As you can see, Excel displays a summary output, along with myriad other data about the regression calculation. From this, you can calculate the Durbin-Watson statistic. The section you’ll need to work in is the Residual Output data, beginning in row 26.
To calculate the Durbin-Watson statistic, click into an empty cell. Then, input the formula to calculate the statistic. In this example, the formula is:
=SUMXMY2(C27:C32,C26:C31)/SUMSQ(C26:C32)
Hit Enter, and Excel will return the Durbin-Watson statistic for your data: 2.939147276.
If your regression residual output is in a different range of cells, simply adjust the values to match. The first range is the second through final residual. The second range is the first through the penultimate residual. The third range, in the SUMSQ section of the formula, always encompasses the complete range of residual outputs.
As you can see, Excel makes it easy to calculate the Durbin-Watson statistic. This helps you analyze and verify that the assumptions you’ve made about your residual linear regression calculations are correct, accurate, and valid.
Vintage Analysis in Excel