Excel Everest

View Original

Matrix Multiplication. And All That Jazz.

Rarely is it said: "Excel can do stuff you didn’t know existed."

Well, we don’t know if that quote has actually been made, but anybody who is even fleetingly familiar with Microsoft Excel can look you in the eye and give a cool assertive nod – because it’s true!

For example, did you know that Excel can do matrix multiplication? Matrices are those slightly confusing, bemusing arrays of rows and columns that reprise bad dreams about math exams where most of us are concerned. For people in academia or research, they are the very bedrock of almost all things math. So in case you need to deal with matrices, there is no need to fire up an advanced stats or math program – good old Excel will do just fine. 

And in this Excel tutorial, we will show you how to do just that. So here goes!

Imagine (if you will) that you have a matrix sitting in a sheet.

This is, in mathematical parlance, a 3X3 matrix. In plain simple English, this is an array with three rows, and three columns. Now we need to invert it. So how does one go about doing this in Excel? Well, here’s how.

Choose, for the first part, an array of exactly the same dimension (3 by 3 in this case)

Now make your way to the formula bar, and type in the following command:

The cells in the formula bar are where your original matrix is located, of course.

Now remember (and this is very important!) – simple hitting the ENTER key will not work, because you are working with a rather special creature in Excel called array formulas. For the result to be shown as an array, you need to tell Excel that the expected answer is not in a single cell, but rather, across an array of cells. And just how do you tell Excel this? By pressing CTRL-SHIFT-ENTER, rather than just ENTER itself.

So go ahead and do that, and here’s what Excel should show:

So the matrix has been inverted. But how do you know if this has been done for sure?

As it turns out, multiplying the original matrix by the inverted matrix throws up an identity matrix (which is a special kind of matrix in which the diagonal from the top left to the bottom right is all 1′s, while every other cell is 0). Can Excel multiply matrices?

You bet it can. Let’s get to it.

Follow the same principle – choose a new, blank array with 3X3 dimensions. Enter this formula within:

... and hit CTRL-SHIFT-ENTER. Voila!

There you go! Simplicity itself, ain’t it?

Folks in college/school – you can use this Excel tutorial to help you verify your math homework and/or assignments. But hey, do yourself a favor and use this for validation only!