22.5 Using Matrices to Solve Simultaneous Equations

You’ll recall that in a previous section, we demonstrated the use of circular references to solve algebra equations. Excel iteratively plugged in different numbers to work through potential answers until it found a solution to the equations involved. In this section, we will demonstrate the use of matrix math within Excel to solve simultaneous equations. This may be something familiar to those who are currently studying applied math or some other type of statistics.

In the video, you’ll see two examples. The first example demonstrates solving two equations simultaneously by building a group of matrices, including the coefficients, constants, and inverse of coefficients, as well as a matrix for the solution. In the second example, we see a solution given for solving three simultaneous equations by increasing the size of our matrices. In this second example, you will also experiment with creating a Megaformula to solve the equations, rather than the method used in the first example. All right, let’s jump in.

Two Simultaneous Equations

In performing matrix math within Excel, we can follow a set of general steps to prepare our worksheet. First, begin by expressing the equations in standard form. If you are given an equation such as 6x - 7 = 2y, convert it to standard form by getting all the variables on the left-hand side of the equal sign and the constant on the right-hand side, as shown below in cells B6:G6. You will do this for all of your equations, listing each on a separate line.

Next, place the coefficients in an n × n range of cells, n being the number of equations, as illustrated in cells J6:K7. Then, place the constants in a vertical range of cells, as shown in cells M6:M7.

The next step involves the use of the MINVERSE formula to calculate the inverse of the coefficient matrix. MINVERSE stands for “matrix inverse.” The formula is an array formula, applied to the Coefficients range of cells, and should look similar to the following where the Coefficients range of cells is located in the array J6:K7. This array formula is placed in cells J10:K11.

{=MINVERSE(J6:K7)}

Finally, use the MMULT function to multiply the Inverse of Coefficients matrix by the Constants matrix. The formula should look similar to the formula below—the range J10:K11 being the Inverse of Coefficients matrix and the range M6:M7 being the Constants matrix. This array formula is saved into the array range of K14:K15.

{=MMULT(J10:K11,M6:M7)}

The resulting values within this final matrix are values for x and y that will satisfy both original equations.

Three Simultaneous Equations

In this example, we solve three equations simultaneously, rather than just two. In order to accomplish this, we begin by increasing the size of our matrix to reflect the additional equation to solve. Recall that a matrix is an n × n range of cells, with n being the number of equations. So, our matrices expand to 3 × 3 in order to solve the third equation.

With this setup, we can accomplish the desired final results in exactly the same method as seen in the previous example. As a bit of an additional challenge, however, let’s see if we can eliminate one step of the process by creating a Megaformula in our results matrix. It could look something like this:

{=MMULT(MINVERSE(K5:M7),O5:O7)}

In this Megaformula, we effectively eliminate the need for the creation of the Inverse of Coefficients matrix by using the MINVERSE formula as the first argument in the MMULT formula. Go ahead and follow along with the video to get a closer look.