Introduction

Imagine you were given the job of solving these simultaneous equations:

3x + 8y + 0z = -10 … … (1)
3x + 4y - 4z = 1 … … (2)
6x + 7y + z = 8 … … (3)

Or even one that was presented like this:

a	b	c	d	Y
1	-0.25	-0.15	-0.05	20000
-0.05	1	-0.1	-0.25	20000
-0.2	-0.1	1	-0.1	15000
-0.15	-0.15	-0.25	1	25000

Now we know you could solve them, of course you could! Why spend ages working step by step when you can program Excel to solve them for you?

Of course the answer to the method we choose to use depends on our circumstances: students often have no choice but to work manually! Anyone without a computer has a bit of a problem too!

So, we are going to use the MMULT and MINVERSE functions from Excel to solve our equations.

MMULT and MINVERSE are Array Functions

The MOST IMPORTANT point to appreciate as far as these two functions are concerned is that they are ARRAY functions. What this means is that when they are entered into our spreadsheet, we MUST press CTRL+SHIFT+ENTER all at the same time instead of just ENTER ... we call this ARRAY ENTER.

To solve our simultaneous equations we first find the inverse of the matrix of the x variables, a, b, c and d in the example we are going to work through. Then we multiply the inverse of the matrix by the Y vector to get our solution. Let's go:

=MINVERSE(array)

Let's work on the following example, assuming that the data points for a, b, c and d are entered in the array A2:D5 and the Y data are entered in the array E2:E5.

a	b	c	d	Y	Equation No
1	-0.25	-0.15	-0.05	20000	… (1)
-0.05	1	-0.1	-0.25	20000	… (2)
-0.2	-0.1	1	-0.1	15000	… (3)
-0.15	-0.15	-0.25	1	25000	… (4)

Let's just make is clear that equation (1) really means:

a - 0.25b - 0.15c - 0.05 = 20,000 … (1)

and equations (2) to (4) have the same format.

In a cell away from the data input range, array enter the following function as follows:

Highlight a 4 x 4 range (the same size as the range of X variable data points). Let's assume, we're array entering the function in cells G10:J13.

With the cursor in G10, select the entire range G10:J13 and whilst it is still selected just type

=MINVERSE(A2:D5) then press CTRL+SHIFT+ENTER together

You should get this in your new range:

1.0864	0.3186	0.2342
0.142	1.1017	0.2073
0.2563	0.2002	1.1017
0.2483	0.2631	0.3417

We'll use this result with MMULT.

Now, put your cursor in, say, K14, select the range K14:K17 and whilst it is still selected just type

=MMULT(G10:J13,E2:E5) then press CTRL+SHIFT+ENTER together

and you should get:

35547.02

35566.22

29982.28

43162.56

Which we can interpret to mean

a =	35547.02
b =	35566.22
c =	29982.28
d =	43162.56

For you to do: why not have a go at the first set of simultaneous equations we gave at the top of this page?

You should find

x =	4.867
y =	-3.075
z =	0.325

Bit of a time saver, eh?

© Duncan Williamson
3 March 2002