How to Use the MMULT and MINVERSE Functions in Microsoft Excel to Solve Matrix Operations

Matrix algebra is a foundational tool in financial portfolio optimisation, economic input-output modelling, structural engineering, and modern data science. Rather than manually computing complex dot products or implementing external scripting routines, Microsoft Excel includes two native mathematical array functions: MMULT (Matrix Multiplication) and MINVERSE (Matrix Inverse). Combining these functions allows you to solve systems of simultaneous linear equations directly within your worksheets with dynamic array spilling.

Understanding MMULT and MINVERSE Syntax

Both functions operate on two-dimensional numerical grids:

1. MMULT (Matrix Product)

=MMULT(array1, array2)

• Calculates the matrix product of two arrays.
• Mathematical Rule: The number of columns in array1 must strictly equal the number of rows in array2.
• The resulting output matrix has dimensions equal to the rows of array1 and columns of array2.

2. MINVERSE (Matrix Inversion)

=MINVERSE(array)

• Computes the inverse matrix of a square matrix (equal number of rows and columns, e.g., 2×2, 3×3, or 4×4).
• The matrix determinant must not equal zero; singular matrices with a determinant of zero will return a #NUM! error.

Example 1: Multiplying Two Matrices with MMULT

Suppose you wish to multiply a 2×3 matrix by a 3×2 matrix:

1. Enter Matrix A in cells A1:C2:

   | 1 | 2 | 3 |
   | 4 | 5 | 6 |

2. Enter Matrix B in cells E1:F3:

   | 7  | 8  |
   | 9  | 10 |
   | 11 | 12 |

3. Click on cell A5 and enter:

   =MMULT(A1:C2, E1:F3)

4. Press Enter. In modern Excel (Microsoft 365 and Excel 2021+), the resulting 2×2 matrix spills automatically across cells A5:B6:

   | 58  | 64  |
   | 139 | 154 |

Example 2: Inverting a Matrix with MINVERSE

Inverting a square matrix is essential for isolating variables in linear algebra:

1. Enter a 2×2 square matrix in cells A1:B2:

   | 4 | 7 |
   | 2 | 6 |

2. Click into cell D1 and type:

   =MINVERSE(A1:B2)

3. Press Enter. The inverse matrix instantly spills across D1:E2:

   |  0.6 | -0.7 |
   | -0.2 |  0.4 |

4. To verify mathematical correctness, multiplying the original matrix by its inverse using =MMULT(A1:B2, D1:E2) yields the identity matrix [[1, 0], [0, 1]].

Solving Systems of Linear Equations Using MINVERSE and MMULT

Consider the system of three simultaneous linear equations with three unknown variables (x, y, z):

3x + 2y - z = 1
2x - 2y + 4z = -2
-x + 0.5y - z = 0

In matrix notation: A · X = B, where A is the coefficient matrix, X is the vector of unknowns, and B is the vector of constants. Multiplying both sides by the inverse of A gives: X = A⁻¹ · B.

1. Enter coefficient Matrix A into cells A1:C3:

   |  3 |  2   | -1 |
   |  2 | -2   |  4 |
   | -1 |  0.5 | -1 |

2. Enter the constants Vector B into cells E1:E3:

   |  1 |
   | -2 |
   |  0 |

3. In an empty cell (e.g., G1), enter the unified nested formula:

   =MMULT(MINVERSE(A1:C3), E1:E3)

4. Press Enter. The solution vector spills vertically down G1:G3, revealing:
   • Cell G1 (x): 1
   • Cell G2 (y): -2
   • Cell G3 (z): -2

Troubleshooting Common Matrix Errors

Identify and resolve matrix calculation errors with this reference:

Error Code	Underlying Cause	Correction
#VALUE!	Incompatible matrix dimensions in MMULT.	Ensure columns of array1 match rows of array2, and all cells contain numbers.
#NUM!	Matrix is non-invertible (determinant is 0).	Verify matrix coefficients; singular or linearly dependent rows cannot be inverted.
#SPILL!	Target spill cells are blocked by existing data.	Clear surrounding cells below and to the right of the formula cell.