How to multiply matrices in a spreadsheet
Grid by Zaweba multiplies matrices with MMULT, inverts them with MINVERSE and finds determinants with MDETERM, and the results spill into place.
MMULT is the function for it. In Grid by Zaweba, =MMULT(A1:C3,E1:G3) multiplies two matrices and the result spills into the cells below and to the right. MINVERSE returns the inverse of a square matrix and MDETERM its determinant, so the usual matrix arithmetic, including solving sets of simultaneous equations, can all be done in a sheet.
Setting out a matrix
A matrix in a spreadsheet is simply a rectangular block of numbers, one value per cell, with no gaps or text. Label the blocks with a heading above each one so you can tell them apart, and leave clear space around them, because results spill and need room.
Multiplying step by step
Matrix multiplication only works when the number of columns in the first matrix equals the number of rows in the second. A 2 by 3 matrix times a 3 by 4 matrix gives a 2 by 4 result. If the sizes don't fit, MMULT returns an error.
Click an empty cell where the top-left of the answer should go, type =MMULT( then select the first matrix, a comma, the second matrix, close the bracket and press Enter. Grid works out the size of the result and fills it, with a blue spill outline marking the area. If anything is in the way you'll see #SPILL!; clear those cells and the result appears.
Older workbooks may use the original method, where you select the whole output area first and enter the formula with Ctrl+Shift+Enter as an array formula. Grid supports that too, so sheets made that way open and calculate as before.
Inverses and determinants
MINVERSE takes a square matrix and returns its inverse, which spills into a block the same size. Multiply the original by its inverse with MMULT and you should get the identity matrix, ones down the diagonal and zeros elsewhere. Tiny values such as 1E-16 in place of zero are rounding at the 15-digit precision every spreadsheet uses; ROUND tidies them for display.
MDETERM returns the determinant as a single number. A determinant of zero means the matrix has no inverse, and MINVERSE will return #NUM! for it.
Solving simultaneous equations
This is the job most people meet matrices for. Write the coefficients of the unknowns as a square matrix A and the right-hand sides as a column B. The solution is the inverse of A multiplied by B, which in one formula is MMULT of MINVERSE of A with B. The answer spills as a column, one value for each unknown.
Check it by multiplying A by your answer with MMULT. You should get B back.
Related functions
Grid's Maths and Trig family has 80 functions, and a few others help with matrix work. TRANSPOSE flips rows and columns. SUMPRODUCT multiplies matching cells and adds the results, which is a dot product. MUNIT builds an identity matrix of a given size. And with LAMBDA and MAKEARRAY you can build matrices from a rule rather than typing them in.
Keeping the work readable
Give each matrix a name in Name Manager, such as Coefficients and Constants. A formula that reads MMULT(MINVERSE(Coefficients),Constants) explains itself, and if you move the blocks the names move with them. Trace precedents will draw arrows from the blocks to the result if you need to check what feeds what.
For students, engineers and anyone checking homework, Grid by Zaweba does this offline and free, and the workbook saves as an .xlsx that opens in other spreadsheet programs.
