Matrix Multiplication in Excel — Math Introduction

This post is part of the “Matrix Multiplication in Excel” series. It’s composed of a math introduction (you are here), a silly interlude and an interactive tutorial. By the end of the series, you’ll learn how to perform Markov Chain calculations, which are used in some damage calculations.

A matrix (plural matrices) is a rectangular array of numbers, symbols or expressions arranged in m rows and n columns. For matrix A below, the element at row i and column j is indexed as a_{i,j}.

mmFig1

A Few Matrix Operations

Matrices have lots of applications, but it’s probably best to start by thinking of them as an abstract entity with a set of rules to learn. For example:

  1. Multiplying a matrix by a constant multiplies each element of the matrix by that constant.
  2. Matrices can be added or subtracted from each other if they have the same dimensions. The operation is parsed entry by entry.
  3. Transposing a matrix exchanges every row with its column and vice-versa. This is indicated through a superscript capital T.

For instance, suppose you had two matrices, A and B with the following values:

mmFig2

What would be the value of:

mmFig3

If you don’t have experience with matrices and want to learn, I’d recommend working out the answer yourself before clicking here for the answer.

Dot Products

One last operation before we turn to matrix multiplication. Dot products are performed on two equal-length sequences of numbers. In Excel, it is performed through the =sumproduct() function. Dot products are equal to the sum of the products of each corresponding row. For instance:

mmFig5

mmFig6

Matrix Multiplication

With these lemmas out of the way, let’s learn about Matrix Multiplication.

When you multiply an m x p matrix by a p x n matrix, you produce an m x n matrix. The number of columns in the first matrix has to match the number of rows in the second matrix.

Unlike traditional multiplication, matrix multiplication isn’t commutative. Five times seven equals seven times five. However:

  • 5 x 1 matrix times a 1 x 5 matrix produces a 5 x 5 matrix.
  • 1 x 5 matrix times a 5 x 1 matrix produces a 1 x 1 matrix.
  • 2 x 4 matrix times a 4 x 3 matrix produces a 4 x 3 matrix.
  • 4 x 3 matrix times a 2 x 4 matrix produces an error.

Each entry a_{i,j} is equal to the dot product of row i in matrix A and column j in Matrix B.

An example is provided below. In it, a 4 x 3 matrix is multiplied by a 3 x 2 matrix to produce a 4 x 2 matrix. Element 3,2 is stepped out, and the final result is displayed.

mmFig7

At this point, this post may seem exceedingly esoteric. Pardon my platonism. For now, trust me when I say that matrix multiplication, and linear algebra as a whole, are far more useful than they immediately appear. After a brief interlude, I’ll show you how to perform these calculations in Excel and an important application for financial expert witnesses. Stay tuned.