Average Correlation

Question

We're given a spreadsheet with a correlation matrix for four stocks.

Then there is a calculation for average correlation, but I don't know how it's derived.

$$=\left(\operatorname{Average}(C14:F17)-\frac 14\right)\times\frac{16}{10}$$

I want to extend this calculation to six stocks. Can someone explain or point me to an explanation for how average correlations are calculated rather than some arbitrary scaling factors?

Answer 1

He is forced to use some tricks because Excel can only take average of a rectangular area, but he wants the avg of upper non-diagonal elements of the matrix only. So he subtracts $\frac{1}{n}$ (the average of the 1's on the diagonal), then scales the result by $\frac{n^2}{n(n+1)/2}$ which is the number of total elements divided by on-or-below-diagonal elements. Of course he is using $n=4$ since he has a 4 by 4 matrix.

These tricks are clever but they detract from the readability of the program (they also will not work if the matrix does not have 1's on the diagonal, etc.).

You should try simple cases like 2 by 2 or 3 by 3 to see how it works and then try to prove it for the general n by n case.