I recently created an add-in to create a variance/covariance matrix for Excel 2007. If you follow that link, you will be able to read about the add-in and how to use it. The purpose of this post is to announce that I have created the same add-in for prior versions of Excel.
Note that both versions of the add-in are now updated so that they will calculate either a population or sample variance/covariance matrix. They also now allow for the covariance matrix to be output to a worksheet that is different from the input range.
This version of the add-in works in Excel 2003. It should work fine in other recent versions, though I haven’t tested it in anything prior to 2003. It only differs from the Excel 2007 version in that it adds a Var/Cov Matrix menu item to the Data menu.
You can download the variance/covariance matrix add-in for Excel 2003, and also some test data. (Note: Internet Explorer users should right-click the link to the add-in and choose Save Target As. Then, change the file extension from .xls to .xla before saving.) As with the other version, the add-in can be used without charge for both individual and commercial purposes.
To install the add-in, go to Tools –> Add-Ins and then click the Browse button. Locate the cov-matrix.xla file and then click the OK button. If it installs successfully, you should now have the "Var/Cov Matrix” item on your Data menu.
The source code can be viewed in the VB Editor. Please let me know if you find any bugs, inconsistencies, or just things that irritate you. Of course, I’d like to know if you find it useful as well.
Known Limitations: Because this add-in creates an array formula that uses the matrix functions, it is limited to 73 variables at most.
Commenting is not available in this channel entry.