Question

How can I calculate a covariance matrix using Excel functions and is there a step-by-step guide on how to do it?

Answer 1

I'ts easy to crosntruct a covariance matrix:. Let X be your data set.. then make y=X-M where M is the vector of means.. Then the covariance matrix is given by C=1/n(y'y). where n is the number of observations

Answer 2

Although Excel may not come with all the matrix functions you desire, you could make your own one with VisualBasic that comes with it. What I find particularly useful is the A(m,n) array (with Dim command) and the For-Next sequence. You will find yourself desolate without For-Next.. . Note that a 2-dimensional array is tantamount to a matrix.