=intro=
Hi. In this video I'll help you out with Major Assignment 3, on the calculation of the Durbin Watson serial correlation statistic, which requires calculating the sum of squared errors.
=spreadsheet=
As per your reading, the numerator of the DW statistic is the sum of squared differences between successive errors in the model fit. In the spreadsheet, I have already calculated the predicted values of the model fit, and the model errors are simply the difference between what we are predicting and the actual values.
So procedurally speaking, in order to find the square differences between successive model errors, we have to find the difference between this and this model error, this and this, and so on, square those differences, and add them up.
First, let's try the brute force approach. we are going to calculate the differences between successive model errors in this auxiliary column here, then square them in the next column, and finally add them up to get our result.
This is equal to this error minus successive error, fill it down. Let's scroll down and make sure we don't have a bogus difference, which of course we do, because here we have no data. delete that.
Let's scroll back up, and in the next column we will create the squares. Equals this, squared, fill it down.
Finally, we will create the sum of all of these numbers, equals sum, this all the way down to the last one, close parentheses, and there is our result, achieved with brute force.
The next approach we can use is array functions. we are going to sum the squared differences between successive errors, so let's do that. Sum, i8 to i331, minus i9 to i332, and we are going to square those differences, add opening parentheses for that. Square should be inside the sum because we need to square individual errors. Now hit ctl-shift-enter to execute the array function. And you can see we get exactly the same result. We are finding the difference between each error and its successor, squaring those, and adding them up.
The final option we have is to use the built in function, SUMXMY2, which means sum of x minus y squared. This is built in function specifically to do the sum of squared differences between two vectors. So let's give that a shot.
Equals SUMXMY2, I8 to I331 is our first vector, and i9 to i332 is the second one, and we just hit enter, and there we go, the same result again.
notice how in these two last approaches, we use the same data set twice, just shifted off by one, to provide the differences between successive errors in our desired calculation.
Now you can use the same approach to find the sum of squared errors, for the denominator of the DW statistic.
=end=
Good luck with the rest of the assignment!