Macro Based on Correlation Matrix

[LactoseO.D.'d]

Is there any where I can get the code for the correlation matrix in Analysis Toolpak? I am trying to code something with a similar function, but outputs everything from LINEST into separate tables that are laid out in the same manner.

I tried doing it myself, but I have trouble making the output framework and having it cycle through all the possible combinations of variables.

 

[LactoseO.D.'d]

Update: I found this: http://www.tvmcalcs.com/blog/comments/VarianceCovariance_Matrix_Add-in_for_Excel_2007/

But I am still trying to adapt his code.

 

[shg]

How is your data arranged, and what are you trying to do?

 

[shg]

Here's the example at http://www.itl.nist.gov/div898/handbook/pmc/section5/pmc541.htm converted to Excel:

      ------B------ --C--- --D-- --E--- F ---G--- ---H--- ---I--- ---J---
  2                 length width height         n       5                
  3   Observation 1   4.00  2.00   0.60                                  
  4   Observation 2   4.20  2.10   0.59   row\col      1       2       3 
  5   Observation 3   3.90  2.00   0.58        1  0.02500 0.00750 0.00175
  6   Observation 4   4.30  2.10   0.62        2  0.00750 0.00700 0.00135
  7   Observation 5   4.10  2.20   0.63        3  0.00175 0.00135 0.00043

The formula in H5 and copied across and down is

= COVAR(INDEX(tbl, 0, $G5), INDEX(tbl, 0, H$4)) * n/(n-1)

... where tbl refers to the data ($C$3:$E$7) and n is the number of observations.

 

[LactoseO.D.'d]

My data is arranged in a worksheet called Dataset with the first row and column hidden:

(all of this is dummy data/variables right now)

What I want to do is create an output like this on a new worksheet (this output is from the correlation tool of the Analysis toolpak):

Only instead of correlation, I want a sheet for R-squared, and a few other functions derived from LINEST that use standard error (linest is the only way to get it).

The process involves:
Creating dummy linest sheet
Creating output sheets
Running Linest on 2 variables
Outputing Linest data to respective sheets
Repeat
Delete Linest sheet

I've got the sheets and all that general stuff created, but I'm having problems with the actual computation/creating the loop and defining the variables to cycle through every pair of regressable items to create the output in the linest sheet.

 

[LactoseO.D.'d]

I should probably mention the variables are arranged by column.

 

[shg]

If you put a workbook on box.net and post a link, I'll take a look.

 

[LactoseO.D.'d]

Will do later this evening.

 

[LactoseO.D.'d]

http://www.box.net/shared/5o61iyq6nojo5j9narvg

 

[shg]

Now we just need a lucid explanation of what you're trying to do so I don't have to divine your intent or reverse engineer your code ...