# Average Correlation

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?

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.
• An easier solution might just be to do (sum(square)-n)/n^2 - at least in terms of readability. – will Aug 1 '16 at 8:46