Covariance in Google Sheets

Question

Say that I have the following data set where f (x,y) is the probability density function with 2 random variables, X,Y.

How would I get Google Sheets to calculate the covariance of X and Y with this function in mind?

Answer 1

Suppose the range shown on your sketch is A1:F6, so that X values are in row 1, Y values are in column A, the column sums of probabilities (marginals) are in row 6, and the row sums are in column F. It seems you calculated the row/column sums already, presumably with =sum(B2:E2) and such (note than your row sums are incorrect, they should be 0.3, 0.3, 0.25, 0.15).

Then the following calculates the covariance of X and Y:

=arrayformula(mmult(mmult(
  B1:E1 - sumproduct(B1:E1, B6:E6), B2:E5),
  A2:A5 - sumproduct(A2:A5, F2:F5)
))

Explanation:

• sumproduct(B1:E1, B6:E6) is the expected value of X, which is subtracted from X values.
• sumproduct(B1:E1, B6:E6) is the expected value of Y, which is subtracted from Y values.
• mmult is matrix multiplication. It has to be used twice because we need (X-EX)*P*(Y-EY) where P is the square matrix of probabilities. mmult multiplies two matrices at a time. So, the first multiplication is (X-EX)*P and the second is by (Y-EY).

Linebreaks are optional, for readability only. The result is 0.7275.

One can avoid computing the row/sums in B6:E6 and F2:F5, and do everything in one formula. It's not very complicated:

=arrayformula(mmult(mmult(
  B1:E1 - sumproduct(B1:E1, mmult(B1:E1^0, B2:E5)), B2:E5),
  A2:A5 - sumproduct(A2:A5, mmult(B2:E5, A2:A5^0))
))

The only difference is that the column sums, previously taken from B6:E6, are computed as mmult(B1:E1^0, B2:E5) - that is, multiplying the probability matrix P by a row of ones on the left. Similarly, mmult(B2:E5, A2:A5^0) multiplies P by a column of ones on the right, computing the row sums.