Abrar-ur-Rahman Niloy
A matrix is an important tool, used every day in statistics and scientific studies. Excel spreadsheets themselves are very large matrices containing 1,048,576 rows and 16,384 columns. It isn’t surprising that Excel provides some useful tools for matrix operations. In this article, we will focus on matrix multiplication in Excel with different examples of different scenarios.
How to Do Matrix Multiplication?
First, let us focus on how matrix multiplication actually works. If there are two matrices with dimensions i x j and j x k, each element of the first row will be multiplied by elements of their respective entry numbers from the first column of the second matrix. Then all the results added will indicate the value of the element of row one and column one of the result matrix, taking row number from the first matrix and column number from the second. This will go on for i x k times and result in an i x k matrix.
Let’s take an example where we are adding two matrices A and B.
Every entry from the first row of matrix A will multiply with respective entries from the first column of matrix B. Then the result will give us the 1×1 value of the multiplied matrix, let’s say C. In this example it will be 1*4+2*6+3*8=40.
The same process will repeat for 1st row from A and 2nd column from B, 2nd row from A and 1st column for B, 2nd row from A and 2nd column from B.
Finally, the result will look something like this.
This is the multiplied matrix of A and B.
Excel has a built-in MMULT function for Matrix Multiplication. This function takes two arrays as arguments. We can use matrices as argument arrays in this function to get the desired result.
1. Performing Matrix Multiplication of Two Arrays in Excel
Let’s take two individual matrices A and B. In Excel, we will treat them as arrays for matrix multiplication.
Steps:
- First, select the cells you want to put your matrix in.
- Then write in the following formula.
=MMULT(B5:D7,B10:D12)
- Now, on your keyboard, press Ctr+Shift+Enter. You will have the result of the AxB matrix.
You can do the same for the BxA matrix by entering matrix B as the first and matrix A as the second argument of the MMULT function.
Read More: How to Multiply Two Columns in Excel
2. Multiplying One Column with One Row Array
Let’s take the following dataset, with matrices containing only one column and one row.
The multiplied matrix AxB will be the result of the multiplication of one-column and one-row matrices.
Steps:
- First, select the range of cells for the multiplied matrix.
- Then write down the following formula.
=MMULT(B5:B7,B10:D10)
- Finally, press Ctrl+Shift+Enter on your keyboard. You will have the resultant matrix.
3. Conducting One Row and One Column Array Multiplication in Excel
For the same dataset used in the previous method, matrix multiplication of BxA will indicate the multiplication of one row and one column matrices.
Steps:
- First, select the cell. This multiplication will only yield one value, so select one cell here.
- Then type in the following formula.
=MMULT(B10:D10,B5:B7)
- Now, press Ctrl+Shift+Enter on your keyboard. You will have your desired result.
Similar Readings
- How to Multiply 3 Matrices in Excel
- Multiply by Percentage in ExcelÂ
- Multiply Two Columns and then Sum in Excel
4. Calculating Square of a Matrix from Matrix Multiplication
Let’s go back to the matrices on the examples used in the first example. We will use matrix multiplication here to determine the squares of matrices A and B.
Steps:
- Select the range of cells for your square matrix.
- Now write down the following formula.
=MMULT(B5:D7,B5:D7)
- Now, press Ctrl+Shift+Enter on your keyboard. You will have the square of the matrix A.
You can replace the range of matrix A with the range of matrix B (B10:D12) and get the square of matrix B too.
5. Doing Multiplication of a Matrix and a Scalar in Excel
When a matrix is multiplied by only a number, all the elements of the matrix are multiplied by that number. This can also be achieved in Excel.
For the demonstration, I am using the matrix A here and multiplying it by 7.
Steps:
- Select the range of cells for the multiplied matrix.
- Then type the following formula in the box.
=B5:D7*G7
- Press Ctrl+Shift+Enter on your keyboard.
Read More: How to Multiply a Column in Excel by a Constant
Errors While Doing Matrix Multiplication in Excel
There are several errors you can run into while performing matrix multiplication in Excel.
Of them, a #VALUE! error can occur if the number of columns in the first array and the number of rows in the second array doesn’t match.
You will have the same error if there is at least one non-numeric value in a cell within the array.
If you select more values than your supposed multiplied matrix looks like, you will have #N/A error, though only in the extra cells you have selected.
Limitation of Matrix Multiplication in Excel
If you are using Excel 2003 or an older version, there is a limit for matrix multiplication of 71×71 dimensions. But for the later versions, you can carry on the operation for as long as the spreadsheet allows, limited only by the RAM of your system.
Download Practice Workbook
You can download the workbook containing all the examples used in this article from the box below.
Conclusion
These were the different situations you can do matrix multiplication in Excel. Hope you have found this article helpful and informative. If you have any questions or suggestions for us, let us know below.
For more guides like these, visit Exceldemy.com.
