Solve a System of Equations in Excel (3 Examples)

Solving a system of equations using spreadsheet software like Excel is a powerful application of computational math. To efficiently determine the values of unknown variables, we rely heavily on two specialized array functions: the MMULT function and the MINVERSE function.

This guide provides a detailed, step-by-step walkthrough demonstrating how to utilize these functions to solve systems involving two, three, and four variables, providing clear visual examples for successful implementation in Excel.

The Mathematical Foundation: Matrix Algebra

Before diving into Excel, it is essential to understand the underlying mathematical principle. Solving systems of linear equations in this manner is rooted in Matrix Algebra. Any system of linear equations can be represented in the form of a matrix equation: AX = B.

In this matrix equation:

• A represents the coefficient matrix (the numbers multiplying the variables).
• X represents the variable matrix (the unknown values we are trying to solve for, such as x, y, and z).
• B represents the constant matrix (the numbers on the right side of the equals sign).

To solve for the variable matrix X, we must multiply the inverse of the coefficient matrix A (written as A⁻¹) by the constant matrix B. This results in the formula: X = A⁻¹B. This is exactly what the combined Excel functions—MINVERSE for A⁻¹ and MMULT for the multiplication—achieve.

Key Excel Functions for Matrix Operations

Excel streamlines the process of finding matrix inverses and performing matrix multiplication through dedicated array formulas. These functions are crucial for our solution method.

The MINVERSE function calculates the inverse matrix of a square matrix (a coefficient matrix where the number of rows equals the number of columns). If the matrix is singular (non-invertible), the function will return a #VALUE! error.

The MMULT function returns the matrix product of two arrays. It takes the inverse matrix generated by MINVERSE and multiplies it by the constant matrix (B), yielding the solution matrix (X). Since these are array formulas, they must be entered correctly by pressing CTRL + SHIFT + ENTER after typing the formula, rather than just ENTER.

Example 1: Solving a 2×2 System of Equations

Consider the following basic system of two linear equations, where our goal is to solve for the values of x and y:

5x + 4y = 35

2x + 6y = 36

To begin the solution process, the first step is to accurately input the coefficients (Matrix A) and the constants (Matrix B) into separate columns in your Excel worksheet, setting up the foundation for the calculation.

Once the data is structured, we use the combined formula X = MMULT(MINVERSE(A), B). In this case, A is the range of coefficients (A1:B2) and B is the range of constants (C1:C2).

The complete formula required to solve for the values of x and y is:

=MMULT(MINVERSE(A1:B2),C1:C2)

Since this is an array formula that returns multiple values (x and y), you must select the destination range (e.g., cells E1 and E2), type the formula into cell E1, and then finalize it by pressing CTRL + SHIFT + ENTER simultaneously.

The output confirms that the solution for the system is reliable: the value for x is 3 and the value for y is 5.

Example 2: Solving a 3×3 System of Equations

The methodology remains identical when scaling up to systems with three variables (x, y, and z). This requires setting up a 3×3 coefficient matrix. Suppose we need to solve the following system:

4x + 2y + 1z = 34

3x + 5y – 2z = 41

1x + 1y + 1z = 13 (Assuming a third equation was intended for a 3×3 system, typical of such examples)

To solve this system, we must structure the coefficients and constants into their respective 3×3 and 3×1 matrices within Excel, ensuring all coefficients, including those implied as 1 or -1, are explicitly entered.

We will again apply the MMULT and MINVERSE functions, adapting the range references to accommodate the larger matrices. The coefficient matrix now spans A1:C3, and the constant matrix is D1:D3.

The array formula to solve for x, y, and z is:

=MMULT(MINVERSE(A1:C3),D1:D3)

Select the destination cells (F1 through F3), input the formula into F1, and press CTRL + SHIFT + ENTER. The result will populate the three cells simultaneously.

The resulting solution indicates that the value for x is 5, the value for y is 6, and the value for z is 2.

Example 3: Solving a 4×4 System of Equations

For complex scenarios involving four variables (w, x, y, and z), the matrix method remains the most straightforward computational approach in Excel. This requires constructing a 4×4 coefficient matrix.

We aim to solve the following system:

6w + 2x + 2y + 1z = 37

2w + 1x + 1y + 0z = 14

3w + 2x + 2y + 4z = 28

2w + 0x + 5y + 5z = 28

The process requires careful entry of the 16 coefficients (A1:D4) and the four constants (E1:E4) into the spreadsheet. Remember to input ‘0’ for any variables missing from an equation (e.g., the 0x in the last equation).

We utilize the same structure as before, ensuring the arrays in the formula correspond to the 4×4 coefficient matrix and the 4×1 constant matrix.

The formula for solving w, x, y, and z is:

=MMULT(MINVERSE(A1:D4),E1:E4)

Select four output cells (G1 through G4), enter the formula into the first cell (G1), and confirm the array formula using CTRL + SHIFT + ENTER.

The final solution set is displayed in the output cells, confirming that the value for w is 4, x is 3, y is 3, and z is 1.

Important Considerations for Array Formulas

When implementing this method, always remember that both MINVERSE and MMULT are array functions. If you forget to use CTRL + SHIFT + ENTER, Excel will only calculate the first value of the solution matrix and will not correctly populate the remaining cells. Furthermore, if you need to edit the formula, you must do so in the formula bar and re-confirm with CTRL + SHIFT + ENTER.

Additional Resources

For users interested in expanding their knowledge of computational methods in Excel, the following tutorials explain how to perform other common statistical and mathematical operations: