We Value Your Privacy

We and our partners use technology such as cookies on our site to personalise content and ads, provide social media features, and analyse our traffic. Click below to consent to the use of this technology across the web. You can change your mind and change your consent choices at anytime by returning to this site.

Update Consent
Loading ...

How to Use the Durbin-Watson Statistic in Excel

Updated February 21, 2017

The Durbin-Watson is a test that statisticians use to see whether data are correlated. In other words, you might want to find out whether a particular event was caused by another event. The test was created by statisticians James Watson and Geoffrey Durbin in the late 1940s. Although the actual statistic can seem complicated to use, Microsoft Excel can perform the calculation for you with a single formula. To use the Durbin-Watson test, you must already have results from an experiment or a study.

Loading ...
  1. Ensure that the results from your study are in a standard two-column format in Excel. A standard format for statistical results has your X-values in one column and your Y-values in another column.

  2. Click on a blank cell in your Excel document.

  3. Type the following formula into a cell:

  4. \=SUMXMY2(C2:C50,C1:C49)/SUMSQ(C1:C50)

  5. Replace the range in the last set of parentheses with the range of your Y-values in your spreadsheet. For example, if your Y data are in cells B2 through B10, your last set of parentheses would read (B2:B10).

  6. Change the first set of parentheses to fit the location of your X-values. There are four parts to the formula. The first part of the formula is the location of your second X-value; the second part of the formula is the location of your last X-value; the third part of the formula is the location of your first X-value; the last part of the formula is the location of your second to last X-value. For example, if you have a column of X-values in cells A2 through A20, then this part of the formula would be (A3:A20;A2:A19)

  7. Press the "Enter" key; Excel will calculate the answer.

  8. Tip

    Reserve the first cell in a column for column headers. This is usually listed as row "1" on your spreadsheet.

Loading ...

Things You'll Need

  • Excel spreadsheet with statistical data

About the Author

Stephanie Ellen teaches mathematics and statistics at the university and college level. She coauthored a statistics textbook published by Houghton-Mifflin. She has been writing professionally since 2008. Ellen holds a Bachelor of Science in health science from State University New York, a master's degree in math education from Jacksonville University and a Master of Arts in creative writing from National University.

Loading ...