Finding column and row number for the nth largest value in an array

[brocoe]

Hello,

I am having a problem getting the row and column for a specific value in an array. I have about 200 data sets that each contain 400 observations. I need to correlate all of these data sets with each other and find the 100 most highly correlated pairs. Finding the 100 highest is simply a matter of using the large function but finding the pairs that give me that correlation is where I am running into some problems. Below is a simplified example of what I need to happen.

My array looks similar to this:

	A	B	C	D	E
E	.85	.42	.56	.24
D	.32	.91	.44
C	.76	-.15
B	-.20
A

<tbody>
</tbody>

The results page should look something like this.

	Correlation	Row	Column
1st largest	.91	D	B
2nd largest	.85	E	A
3rd largest	.76	C	A

<tbody>
</tbody>

Given the large amount of data number of data points per data set (~400) all the correlations in the table will be unique. I am looking for a solution that DOESN'T require VBA but I would be amenable to using a custom function from VBA. So if anyone can provide me with a solution to getting the bolded results as show above it would be greatly appreciated.

 

[platonic567]

Given your data setup as below:


Formula in B9 copy down:

=LARGE($B$2:$F$6,A9)

Formula in C9 copy down:

=INDEX($A$2:$A$6,SUMPRODUCT(--($B$2:$F$6=B9)*ROW($A$2:$A$6))-1)

Formula in D9 copy down:

=INDEX($B$1:$F$1,SUMPRODUCT(--($B$2:$F$6=B9)*COLUMN($B$1:$F$1))-1)

	A	B	C	D	E	F
1		A	B	C	D	E
2	E	0.85	0.42	0.56	0.24
3	D	0.32	0.91	0.44
4	C	0.76	-0.15
5	B	-0.2
6	A
7
8	Rank	Correlation	Row	Column
9	1	0.91	D	B
10	2	0.85	E	A
11	3	0.76	C	A

<TBODY>
</TBODY>

 

[brocoe]

Unbelievable. It works perfectly! Thank you so much.

 

[platonic567]

You're welcome!

 

[brocoe]

I've been trying to find a solution to this for two days. It's amazing that you solved it in 15 minutes.