In this simple guide, we are going to run through the process for applying Matrix Multiplication in Excel, by using the built in MMULT formula.
We will cover:
- What is a matrix and how do we multiply them
- The MMULT Function
Let’s get started with a basic summary on matrices in general.
What is a Matrix
Put simply, Matrices are an array of numbers, organised into rows and columns. A really simple 3 x 3 Matrix might look like the below:
Matrices are used quite often in a range of mathematical concepts, but for the purposes of this article we are going to focus entirely on multiplying matrices.
When multiplying a matrix, the rules can be a bit more complicated than simply adding together equivalent locations. The link above sums it up really well, but let’s use the below example of two different matrices – one with 2 rows and 3 columns, and another with 3 rows and 2 columns.
Essentially the flow to fill out each value in the new calculation is as follows along the values and summing the results:
Matrix 1 First Row * Matrix 2 First Column
Matrix 1 First Row * Matrix 2 Second Column
Matrix 1 First Row * Matrix 2 Third Column
Then we move onto the next column in the second Matrix and repeat, and once we have made our way across the columns we move back to the first column, but down a row in Matrix 2.
Essentially, we are left with a third Matrix that has the same number of rows as Matrix 1, and the number of Columns of Matrix 2.
Let’s look at an example in Excel to make this more clear as it is easier to follow when visualised. But first the formula.
The Excel MMULT function
Excel makes matrix multiplication nice and easy to calculate, largely due to it requiring you to input the full range of both arrays in the formula. In newer versions of Excel it also auto populates into more cells which is super handy!
The formula is as follows:
=MMULT(array1, array2)
Thats it!
Using our example above, the formula would be as follows:
=MMULT(B3:D4,F3:G5)
When entering this into cell B9 in our workbook and pressing ENTER, it will not only fill in cell B9, but also automatically copy the formula across three more cells. When you click into the cell the formula itself will appear but in a grey colour indicating this:
The end result is a new matrice looking like the below:
As we can see, this has two rows in line with Matrix 1, and two columns in line with Matrix 2.
This may still look a little confusing, so let’s break down how we got to the value of 35 in the top left part of the array.
As we are working across Matrix 1 and down Matrix 2, the values calculated will be as follows:
1 X 3
4 X 6
4 X 2
This gives us values of 3, 24 and 8 – which when summed together is 35.
To get the value of 31 the exact same approach is taken, but with the values of 7, 4 and 2 in the second column of Matrix 2. 28 is calculated with the second row of Matrix 1 against the first column of Matrix 2, and finally the value of 40 is with the Second row of Matrix 1 against the second column of Matrix 2!
With our small datasets here of only a few rows and columns this wouldnt be too hard to calculate manually – but the real power of Excel here and the automation of extending the formula into the required cells really comes in handy when working with large sizes! Imagine trying to do something similar with 10+ rows and columns on each.
This sums up our guide on matrix multiplication in Excel. As we have shown, built in functions such as MMULT are quite powerful ways of using mathematical functions.
For more handy guides on working with Excel, be sure to check out our Excel Tips page.