How to Use the Durbin-Watson Statistic in Excel
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.
Things You'll Need
- Excel spreadsheet with statistical data
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.
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)
Press the "Enter" key; Excel will calculate the answer.
Tips & Warnings
- Reserve the first cell in a column for column headers. This is usually listed as row "1" on your spreadsheet.