Generating Correlation Coefficients Among 100 Columns

[rwmill9716]

As an example, here I have 4 columns of data (B through E). Row 6 has code numbers and 7 column headings. Ideally, I would like to tell the macro that I have 4 columns, then it evaluate the correlation coefficient of each possible pair in turn. For 4 columns, there would be the 6 shown in columns G and H and the appropriate correlation coefficients (found using Excel's Correlation function in its Data Analysis program). Since the correlation program needs these columns to be adjacent, I assume that the two columns will have to be pulled from the data, written temporarily somewhere to perform the calculation, then the matrix filled out.

Though this is a small data set, the real set will contain up to 100 columns, each housing 1,000 data. Ideally, I would tell the program that there are 100 columns, and it would then build the matrix of correlation coefficients. Note too, the data set will have missing values embedded as well. This doesn't seem to be a problem for the correlation program though it does the regression program.

Thanks for your help.

ric

Excel 2003
	B	C	D	E	F	G	H	I
6	1	2	3	4		X1	X2	R
7	AMC in CHMD	ACNin CHMD	ADN in CHMD	BHMT in CHMD		1	2	0.66
8	39.71785	14.93756	258.3834			1	3
9	40.82789	10.95147	240.3375			1	4
10	41.80643	10.24193	240.6451			2	3
11	42.63768	10.4913	241.3101			2	4
12	43.46892	10.74068	241.9751			3	4	0.04
13	44.16726	9.034867	380.6934	0.856763
14	44.30017	10.99005	242.6401
15		11.23943	243.3051
16	45.96267	11.4888	243.9701
17	46.79392	11.73818	244.6351	0.962922
18	47.62517	11.98755	245.3001	0.918866
19	48.45642	12.23693	245.9651	0.87481
20	49.28767	12.4863	246.6301	0.830754
21		12.73567	247.2951	0.786698
22	51.12469	13.26691	246.6207	0.746907
23	53.47592	15.9711	235.6204	0.74
24	54.19141	23.54412	263.7421	0.773669
25	55.12621	27.18408	97.01661	0.779891
26	56.28203	18.77976	245.2777	0.743352
27	56.47759	12.93014	468.7091	0.779917
Sheet4