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]

End product:

I have a bunch of data on a sheet called Dataset.

I want to run regression statistics on ALL the data in the columns. I run my macro and input the data range.

To get the statistics I want, I need to use the LINEST function. I create this in an array on a new sheet, and will delete it when all the output finishes.

I want to output each of the statistics into tables on new worksheets, similar to the sample output from the Analysis toolpak that I posted.

Current product:
The code is an attempt to output to a sheet for just one of the LINEST outputs (R-squared, which is cell A3 of the array). Once the output part works (this is the part I am struggling with), I can organize everything in to the table format I want instead of the pairwise comparisons it currently does. So really, the part that I need help with is a) referencing the data from the LINEST sheet and plugging it in to the correct cell on a table, b) writing a loop that will run LINEST for all the pairs.

 

[shg]

Can't you just do this?

      ---B--- --C--- ---D--- --E--- --F--- --G--- --H--- --I--- J K --L--- --M--- --N--- --O--- --P--- --Q--- R
  2           ssssaf asdfwer sadf   a      sdff   aa     aa              1      2      3      4      5      6 7
  3   ssssaf  0.1167  0.6037 0.0254 0.3316 0.5565 0.8259 0.0758   1      1                                     
  4   asdfwer 0.4590  0.8518 0.0282 0.9176 0.2935 0.7354 0.1469   2 0.1099      1                              
  5   sadf    0.9430  0.7263 0.8504 0.8939 0.1513 0.9556 0.6673   3 0.4126 0.0820      1                       
  6   a       0.2299  0.0441 0.0288 0.4131 0.2366 0.0522 0.5117   4 0.4674 0.1882 0.4047      1                
  7   sdff    0.6257  0.3506 0.8600 0.8825 0.7169 0.6030 0.0871   5 0.1341 0.1077 0.0091 0.0007      1         
  8   aa      0.4754  0.9921 0.9382 0.7911 0.2391 0.4002 0.1246   6 0.1444 0.2458 0.0343 0.1477 0.0437      1  
  9   aa      0.2519  0.4528 0.6636 0.8713 0.6963 0.7221 0.5634   7 0.0694 0.1076 0.0213 0.0156 0.0852 0.0004 1

The formula in L3 and copied throughout the lower triangle is

=INDEX(LINEST(INDEX($C$3:$I$9, 0, $K3), INDEX($C$3:$I$9, 0, L$2),, TRUE), 3, 1)

 

[LactoseO.D.'d]

You're an attractive man sir. Seriously, look in the mirror some time.

Thank you very much.

 

[shg]

OK, it's true, I'm a handsome devil ...

You're welcome, glad it worked for you.

 

[shg]

Well, maybe not quite so handsome.

You could more simple compute R2 without LINEST as

=RSQ(INDEX($C$3:$I$9, 0, $K3), INDEX($C$3:$I$9, 0, L$2))

... in L3 and copied as before.

 

[LactoseO.D.'d]

I know, but for other things that involve the standard error, I need LINEST. Might as well just build the framework with that because it outputs everything.

 

[LactoseO.D.'d]

How would you write the formula to return the Standard Error from LINEST? I would like to return a single value like you did with R2, but the microsoft documentation on the function is poor.

edit: I think you are using index to reference the output matrix

edit edit: Got it :D