Solving Simultaneous Linear Equations with Matrices

We have various ways of solving simultaneous linear equations. In this post, we will look at a couple of other methods, and then how we can do it with matrices in Excel.  We first consider two variables, and then more.

Let’s be clear before we start: What are linear equations?  They are of the form y= ax + b or z= ax + by + c where, as is conventional, x, y, and z are variables and a, b, and c are constants.  The important part here is that there are no powers of x, y, or z.  If we had x², y², or z² we would have a quadratic equation.  If we had x³, y³, or z³ we would have a cubic equation.

Linear equations, as the name indicates, plot as straight lines.  We will see this in the next section.

And what do we mean by “simultaneous”?  It means that we have several equations, all of which must be true at the same time.  Graphically, with two simultaneous linear equations, we would find where the lines intersect.

To solve simultaneous equations, we need as many equations as we have variables: Two equations for simple x and y, three for x, y, and z, and so on.

We’ll start simply, with just two:

1.     Solving Two Simultaneous Linear Equations Graphically

Say we have
y = 2x – 1 and
y = -x/2 + 4

We can use Excel to generate some values for y, given values of x:

We can already see, on row 6, that the two y values are equal when x=2 and y=3.

The solution will not always be as obvious, so let’s use a chart:

We can see the lines intersect at x =2, y = 3.

2.     Solving Two Simultaneous Linear Equations Using Algebra

We solve manually as follows

Equation 1: y = 2x – 1
Equation 2: y = -x/2 + 4

Putting y = y from both Equations:
2x – 1 = -x/2 + 4

Move x to one side of the equation, and simplify:
2x + x/2 = 1 + 4
2.5x = 5
x = 5 / 2.5 = 2

Substitute x=2 into Equation 1:
y = 2x – 1 = 2 * 2 – 1 = 4 – 1 = 3

-Check with Equation 2: y = -x/2 + 4 = -2/2 + 4 = -1 + 4 = 3

Thus x =2, y = 3.

3.     Solving Two Simultaneous Linear Equations Using Matrices

To solve these using matrices, we have to rewrite the equations from the y = mx + c format to the mx – y = -c format, i.e.

y=2x-1    becomes     2x – y= 1
y=-x/2+4 becomes     x/2 + y = 4

In matrix format, we have a matrix of the coefficients multiplied by an xy matrix producing a constants matrix:

The xy matrix is then the inverse of the coefficients matrix times the constants matrix:

If your matrix maths is good, you might be able to solve that directly, but I’m going to use Excel:

4.     Solving Two Simultaneous Linear Equations Using Matrices in Excel

The solution looks very similar to the previous section.

We construct the matrix of coefficients in the range O7:P8 and the constants in W7:W8, repeated in W11:W12.

We invert the coefficients matrix using {=MINVERSE(O7:P8)}.  We enter this as an array formula (a hint is the braces, {} ): select the range T11:U12.  Type the formula =MINVERSE(O7:P8) without the braces, and press Ctrl+Shift+Enter to enter the array formula into the selected four cells.

Finally, in the range T15:T16, we multiply the inverted coefficients matrix with the constants matrix.  Again, this is an array formula: select the range T15:T16 first.  Type the formula =MMULT(T11:U12, W11:W12) without braces, and press Ctrl+Shift+Enter to enter the formula into the selected two cells.

The top cell, T15, gives us the result for x (2) and the lower, T16, the result for y (3).

Of course, it is a relief to see that these results agree with the graphical and algebraic solutions!

5.     Solving Three Simultaneous Linear Equations Using Matrices in Excel

Here we have three variables to solve for, x, y, and z.  We need three simultaneous linear equations for the solution.

Graphically, each equation would represent a plane in three-dimensional space.  As long as none are parallel, the planes would intersect at a point, which gives us the solution.  We won’t try to do that here.  Nor will we do an algebraic solution, but go straight to matrices.

The equations we have chosen, are:
2x – y – z = -4
4x + 0.5y + 2z = 13
x + y +2z = 11

Here, we have already written them in the required form with the variables and their coefficients on one side of the “equals” sign, and the constants on the other side.

In matrix format, we have a matrix of the coefficients multiplied by an xyz matrix producing a constants matrix:

The xyz matrix is then the inverse of the coefficients matrix times the constants matrix:

Then, using Excel:

This gives the result x = 1, y = 2, z = 4.

Is this correct?  Let’s check by plugging in the x,y,z values into the original matrices and multiplying out:

Yes!  The coefficient matrix times the xyz matrix (using MMULT) gives us the original constants.

6.     Solving Four Simultaneous Linear Equations Using Matrices in Excel

We can solve any number of simultaneous linear equations for the same number of variables in the same way.  Let’s do one more example, for four equations and four variables w, x, y, and z:

Is this correct?  Let’s again check by plugging the results into the original matrices and multiplying out:

Again, the coefficient matrix times the wxyz matrix gives us the original constants.

We documented the formulas using the =FORMULATEXT() function.

Reminder: Formulas in braces {} are array formulas.  To enter an array formula: select the boxed range first.  Type the formula without braces, and press Ctrl+Shift+Enter to enter it into the selected cells.

7.     Conclusion

In this post, we have looked at the solution of two, three, and four simultaneous linear equations for the same number of variables.  You can do this for any number of equations.  We have not looked at quadratic or cubic equations.

If you would like to save some typing by getting a copy of the spreadsheets used above, click here and send us the resulting email.