# How to Use the Durbin-Watson Statistic in Excel

computer image by blaine stiger from Fotolia.com

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.

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.

- The Durbin-Watson is a test that statisticians use to see whether data are correlated.

Click on a blank cell in your Excel document.

Type the following formula into a cell:

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

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).

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)

- Click on a blank cell in your Excel document.
- Type the following formula into a cell: \=SUMXMY2(C2:C50,C1:C49)/SUMSQ(C1:C50) Replace the range in the last set of parentheses with the range of your Y-values in your spreadsheet.

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

References

Tips

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

Writer Bio

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.