Hi,
I have calculated a correlation matrix between a data set. What I need to do is for each given series in the results is identify the highest occurring correlation coefficient (other than the correlation between the series itself, which is always one) and then log the pair with such an occurrence to another location in another sheet.
For example in the attached sheet, for the first series in column B (XOM), the highest correlation of that series is with row 3 - CVX.
So I want to store XOM CVX say in cells B21 and C21 and correlation coefficient in D21. For the next series in column C (CVX), the highest positive correlation is with row 8 (DDA). I want to store this combination in B22 and C22 and the corresponsing correlation coefficient in D22.
For the attached example, it is quite easy to do manually for a few series but my final correlation matrix features over 200 variables.
Hope someone can help.
Thanks,
Nick