# COVARIANCE.S to build a covariance matrix

1. ## COVARIANCE.S to build a covariance matrix

Hi all

This is probably down to my theoretical knowledge not being up to snuff, but since I'm trying to implement this in Excel I'm hoping someone where will be able to help me out.

I need to create an N x N covariance matrix from N columns of data. There are a large number of rows in the data (many more than N)

How can I take N columns of X rows of data and generate an N x N covariance matrix?

Thanks
Steve

2. ## Re: COVARIANCE.S to build a covariance matrix

With difficulty, what do you have so far?

3. ## Re: COVARIANCE.S to build a covariance matrix

Very little I'm afraid... in fact, nothing!

I know that you can build up a covariance matrix from a diagonal standard deviation matrix and a correlation matrix, by going =MMULT(MMULT(diag_sd, correl), diag_sd)

However, this is going the other way.

I am trying to build a covariance matrix from random data. Within the random data will be the correlations etc. We then do spectral analysis or principal component analysis to break up the covariance matrix into it's constituent eigenvalues and eigenvectors.

Clearly I can't even start thinking about this without being able to generate the covariance matrix, and I don't even know where to start for that! (I've asked my maths lecturer for help, but haven't had a response yet. Hopefully someone on here can help me out instead.)

If I get an answer from my lecturer I'll put the answer up here for future reference.

TIA
Steve

4. ## Re: COVARIANCE.S to build a covariance matrix

Very little I'm afraid... in fact, nothing!

I know that you can build up a covariance matrix from a diagonal standard deviation matrix and a correlation matrix, by going =MMULT(MMULT(diag_sd, correl), diag_sd)

However, this is going the other way.

I am trying to build a covariance matrix from random data. Within the random data will be the correlations etc. We then do spectral analysis or principal component analysis to break up the covariance matrix into it's constituent eigenvalues and eigenvectors.

Clearly I can't even start thinking about this without being able to generate the covariance matrix, and I don't even know where to start for that! (I've asked my maths lecturer for help, but haven't had a response yet. Hopefully someone on here can help me out instead.)

If I get an answer from my lecturer I'll put the answer up here for future reference.

TIA
Steve

5. ## Re: COVARIANCE.S to build a covariance matrix

Hmm?

Try googling
covariance matrix excel

This might give you a start
Variance-Covariance Matrix

6. ## Re: COVARIANCE.S to build a covariance matrix

I have found the answers here: http://en.wikipedia.org/wiki/Princip...onent_analysis

Thanks for the help.

7. ## Re: COVARIANCE.S to build a covariance matrix

PS: Can't edit the original post for some reason, which means I can't set it to SOLVED.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1