Calculating Mean Pairwise Correlation from a Correlation Matrix

Summary

The document explains an Excel adjustment intended to recover average correlation from a square correlation matrix. The desired statistic is the mean of the off-diagonal pairwise correlations, counting each unique pair once. The spreadsheet’s rectangular average also includes diagonal entries, which are one for a standard correlation matrix, and duplicates symmetric off-diagonal values. The formula compensates for those inclusions through an adjustment and scaling factor tied to matrix size.

For a matrix with n assets, the answer describes scaling by the ratio of all matrix cells to the number of entries on or below the diagonal, after subtracting the diagonal’s contribution to the rectangular average. This is presented as a workaround for Excel’s rectangular averaging, rather than the clearest way to calculate the statistic. It relies on a square symmetric correlation matrix with unit diagonal and should not be applied blindly if those assumptions fail. The response recommends checking small matrices and deriving the general case; it does not discuss weighting pairs differently or handling missing correlations.

Key ideas

• Average pairwise correlation usually means averaging unique off-diagonal matrix entries.
• A rectangular spreadsheet average includes diagonal ones and counts mirrored correlations twice.
• The described correction adjusts for the diagonal contribution and scales according to matrix size.
• The shortcut assumes a symmetric square correlation matrix with ones on its diagonal.
• Testing small matrices and deriving the formula helps avoid opaque spreadsheet scaling.

Tags

# Average Correlation


# 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?

## Answer by Alex C (score 4, accepted)

https://quant.stackexchange.com/a/28416

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.

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.