# Generating Correlation Coefficients Among 100 Columns

#### rwmill9716

##### Active Member
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.

ric

Excel 2003
BCDEFGHI
61234X1X2R
7AMC in CHMDACNin CHMDADN in CHMDBHMT in CHMD120.66
839.7178514.93756258.383413
940.8278910.95147240.337514
1041.8064310.24193240.645123
1142.6376810.4913241.310124
1243.4689210.74068241.9751340.04
1344.167269.034867380.69340.856763
1444.3001710.99005242.6401
1511.23943243.3051
1645.9626711.4888243.9701
1746.7939211.73818244.63510.962922
1847.6251711.98755245.30010.918866
1948.4564212.23693245.96510.87481
2049.2876712.4863246.63010.830754
2112.73567247.29510.786698
2251.1246913.26691246.62070.746907
2353.4759215.9711235.62040.74
2454.1914123.54412263.74210.773669
2555.1262127.1840897.016610.779891
2656.2820318.77976245.27770.743352
2756.4775912.93014468.70910.779917
Sheet4

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Replies
1
Views
458
Replies
1
Views
82
Replies
1
Views
728
Replies
1
Views
214
Replies
1
Views
154

1,203,646
Messages
6,056,531
Members
444,872
Latest member
agutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back