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?


1 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.

  • $\begingroup$ An easier solution might just be to do (sum(square)-n)/n^2 - at least in terms of readability. $\endgroup$
    – will
    Aug 1, 2016 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.