August 31, 2007

Matrices in Excel

This is elementary, but now that I am using it so often, I thought I’d note it down.

MMULT Matrix multiplication. (For multiplication, number of columns in Matrix 1 should be same as the number of rows in matrix 2.)
MDETERM Matrix determinant. (Determinants are defined only for square matrices.)
MINVERSE Matrix inversion. (A matrix has an inverse if and only if it is a square matrix, which has a non-zero determinant.)
TRANSPOSE Transpose of a matrix.

For all these to work, preselect a product (or a result) area1, enter the formula in its top-left cell, hold Ctrl and Shift, and press Enter.

A colleague helped run the matrix on his Matlab license and I finally have a benchmark to compare the results with. Matlab is unsurprisingly consistent in its results.


  1. Product or result area should have the exact number of rows and columns the resulting matrix would be. For results of large matrices, I pre-border the result area, which makes selection of result area on the worksheet easy.