[ExcelBanter AI]

Calculating the Durbin-Watson statistic involves a few steps, but it's not too difficult. Here's how you can do it:

1. Step 1: Organize your data
   First, you need to organize your data in two columns in Excel. The first column should be your independent variable (X), and the second column should be your dependent variable (Y).
   
2. Step 2: Run a regression analysis
   Next, you need to run a regression analysis on your data. To do this, go to the "Data" tab in Excel and click on "Data Analysis" (if you don't see this option, you may need to install the Analysis ToolPak add-in). Select "Regression" from the list of options and click "OK". In the "Regression" dialog box, enter your data range for the "Input Y Range" and "Input X Range" fields. Make sure to check the "Labels" box if you have column headers. Then, select an output range for the regression results (e.g. a new sheet or a range of cells). Click "OK" to run the regression.
   
3. Step 3: Find the residuals
   Once the regression is complete, you need to find the residuals (the differences between the predicted values and the actual values). To do this, subtract the predicted values (which are in the "Y" column of your regression output) from the actual values (which are in the original "Y" column of your data). Place the residuals in a new column next to your original data.
   
4. Step 4: Calculate the Durbin-Watson statistic
   Finally, you can calculate the Durbin-Watson statistic using the following formula:
   
   
   Formula:

   DW = SUM[(e_i - e_{i-1})^2] / SUM[e_i^2] 

   where e_i is the ith residual and e_{i-1} is the (i-1)th residual. The SUM notation means to add up the values for all i. You can do this calculation manually using Excel formulas, or you can use a built-in function like DURBINWATSON. Just make sure to select the range of residuals as the input.
   
   That's it! The Durbin-Watson statistic will give you a value between 0 and 4. A value of 2 indicates no autocorrelation (i.e. the residuals are independent), while values closer to 0 or 4 indicate positive or negative autocorrelation, respectively.

EKGILL -

Start by computing residuals (e.g., using the Regression tool).

An example in my book "Data Analysis Using Microsoft Excel: Updated for
Office XP," has residuals in cells F25:F42.

Then the Durbin-Watson statistic is calculated by

=SUMXMY2(F26:F42,F25:F41)/SUMSQ(F25:F42)

- Mike

www.mikemiddleton.com