"Harlan Grove" wrote:

Will wrote...
....
With regards to the given solution, some steps are unclear, for example, why
a column of 1s is added and why the columns are switched around, hence the
explanation is not sufficiently clear in order to generalise. . . .

Let me try again. The explanation is clear to anyone who knows the
linear algebraic form of the least squares and maximum likelihood
estimators and how to implement them brute force in Excel. It can be
generalized, but you'd need to be very familiar with Excel array
formulas and matrix arithmetic functions. I'm not deliberately trying
to be derogatory (some would say it just comes naturally, but I
digress), but if you don't see how to generalize the approach in the
Microsoft KnowledgeBase, then you wouldn't be likely to be able to spot
problems in an Excel implementation of QR decomposition.

To answer my own question above, the reason for the column of 1s in the
knowledge base article is that the regression using QR decomposition is of
the form Rx = Q[T]b, so the regression is fitting a coefficient to a unit
value, which is equivalent to solving for the constant term based on the
usual construction of y=ax + b. A better description of what's going on
(which would have made the knowledge base article far more useful and
understandable) is available at
http://en.wikipedia.org/wiki/Linear_least_squares

You may need a fully constructed workbook implementation of QR
decomposition. I'm not aware of any available on the web, but others
may.

. . . I am trying to
generate an Excel-based solution for some econometrics tests (specifically,
the Augmented Dickey-Fuller test which is specifically designed to account
for correlations in the lagged variables, and hence is guaranteed to generate
problems with multi-collinearity) and I would rather not be forced down the
route of switching to using Matlab instead of Excel.

You shouldn't be using Excel for this. Even Excel 2003 has its
weaknesses, and rolling your own QR decomposition template would result
in a less efficient and less robust (in the software sense, not the
statistical sense) tool than most dedicated stats packages.

There are much cheaper alternatives than MATLAB. There are addon
packages for R and gretl that calculate augmented Dickey-Fuller tests,
and both R and gretl are GNU software packages. There may also be time
series add-ins for Excel that provide this test and cost less than
MATLAB. Dunno.