A. Matrix Functions in Excel
- MMULT(): returns the matrix product of 2 arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2. Matrix A multiply Matrix B doesn’t equal to Matrix B multiply Matrix A. The order matters. The multiplication can be done as long as the number of columns of the first matrix equals to the number of rows in the second matrix. To make matrix smaller is to multiply a wider matrix to a long matrix, which returns the matrix with the shorter side of both matrices; and to make a matrix big is to multiple a longer matrix to a wider matrix, which returns the matrix with the longer side of both matrices.
- MUNIT(): returns the identity matrix for the specified dimension.
- MINVERSE(): returns the inverse matrix of a given matrix. The product of a matrix and its inverse matrix is the identity matrix. The inverse of a matrix will exist only if the determinant is not zero. The inverse of 2×2 [a, b; c, d] matrix can be calculated as:
- Calculate the determinant;
- take the inverse of the determinant;
- multiple the inverse of the determinant to the [d, -b; -c, a] matrix.
- MDETERM(): returns the determinant of the square matrix. If matrix determinant is 0, then matrix is sigular. For a 2×2 matrix, the determinant is equal to (ad-bc).
B. Excel Examples
- Define matrix
- Transpose Matrix (X’)
- Matrix multiplication
- Inverse Matrix (RULE: multiplication of a matrix and its inverse matrix equals to the identity matrix)
- Combination of Matrices Operations
C. Understand the matrix operation in solving regression equations
- The matrix multiplication of X’X is to take the sum of product for each combination of all the Xs.
- The inverse of X’X is converting the X’X into weights for Xs.
- The matrix multiplication of X’Y is to take the sum of product for each combination of X and Y. Visually, it is like expand the space of X matrix to Y scale.
- The (X’X)^(-1)X’Y is the estimation of the intercept and coefficient(s).
D. Reference
