• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

How to Calculate Durbin-Watson Statistic in Excel

October 12, 2021 by Andrew Childress
Durbin watson statistic Excel

Linear regression is a commonly used tool in statistics. Let’s learn how to calculate the Durbin-Watson statistic in Microsoft Excel.

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.

Durbin watson statistic Excel

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. 

Durbin watson statistic in excel

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.

How to calculate durbin watson statistic in Excel

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.

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:How to use vintage analysis in Microsoft ExcelVintage Analysis in Excel
Next Post:How to Do Factor Analysis in ExcelHow to do factor analysis in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top