Hello,
I have a matrix as shown below:
I would like to create a formula that shows the most correlated assets in the matrix. I'm not sure how to do this. I figured that perhaps using the "LARGE" function and excluding the two assets that are the same (Thus 100%) and making a column of the top x # of correlation #s and then trying to do an index or match with Vlookup based on that but everything I try fails. I know how to do a lookup if it were just 2 columns but in a matrix it is throwing me off. Maybe I'm missing something.
I have a matrix as shown below:
Asset A | Asset B | Asset C | Asset D | |
Asset A | 100% | 75% | 55% | 32% |
Asset B | 75% | 100% | 25% | 87% |
Asset C | 55% | 25% | 100% | 87% |
Asset D | 32% | 87% | 90% | 100% |
I would like to create a formula that shows the most correlated assets in the matrix. I'm not sure how to do this. I figured that perhaps using the "LARGE" function and excluding the two assets that are the same (Thus 100%) and making a column of the top x # of correlation #s and then trying to do an index or match with Vlookup based on that but everything I try fails. I know how to do a lookup if it were just 2 columns but in a matrix it is throwing me off. Maybe I'm missing something.