Introduction

Simultaneous equations are usually a nightmare for the average secondary school student: they cannot or will not do them. I was no exception. I remember, however, realising what they were and how they worked years after leaving school and then I thought, why on earth does anyone have a problem with them.

If you know SEs you know there are several ways of solving them and I am not a mathematician so I am not going to explain Cramer’s rule, matrix algebra or even substitution … just show you how Excel can help.

What are Simultaneous Equations?

Simultaneous equations get their name from the fact that in two or three or more equations, X has the same value for each, Y has the same value for each and so on. For example, here are two simultaneous equations:

X + 3y = 8 … … … (1)

X – 2y = -3 … … … (2)

In this case I know that x = 5 and y = 1 and we an prove that by putting 5 where x is and 1 where 1 is in either of the two equations, such as in equation (1):

5 + 3*1 = 8 … 5 + 3 = 8 … 8 = 8 … QED!

Try it yourself for equation (2) if you wish.

The Methods

I am going to use three methods for solving simultaneous equations:

Indirect Method: Using the two array functions MINVERSE() and then MMULT() separately: this is essentially an entire problem or matrix solution that gives us the values of each variable

Direct Method Using the two array functions MINVERSE() and then MMULT() together in the form =MMULT(MINVERSE()) .. this is essentially a single cell solution per variable.

The names indirect and direct are my own and I am classifying them as different methods although in reality they are not!

SOLVER solution: using the SOLVER add in to provide the values for the variables.

Method 1: Indirect Method

Set up a worksheet as follows:

This is a two variable set of simultaneous equations and I have left column C blank to allow us to develop three variable examples. All you do is type in, hard code, the entries in the range A3:D5 in this example.

The range A8:E9 includes the solution using firstly the MINVERSE() array function and then the MMULT() array function.

simult_1

The range A12:D13 contains the Proof area in which we test the solution that x = 5 and y = 1.

The following screenshot shows you my formulas … again, just copy them as you see them here but remember, array functions are not ordinary functions. So for the MIVERSE() function entry you must do the following:

simult_3

Select the range A8:B9 and keep it selected then type =MINVERSE(A4:B5) then press Control and keep it pressed, press Shift and keep it pressed as well then press Enter, now let go of all three keys I will call this CSE from now on!). And you should get the inverse matrix that you see in the range A8:B9 … it if doesn’t work, do it again carefully and if it still doesn’t work, check your typing. If it still doesn’t work, download my example file!

For the MMULT() array function, do the following:

Select the range D8:D9 and keep it selected then type =MMULT(A8:B9,D4:D5) … CSE … if it doesn’t work … check your typing …

The MMULT() array function gives you the answer, as I have labelled here the first number, 5, is the value of x and the second number,1 , is the value of y.

Try This

Repeat the above as you try to solve these equations for x and y:

2 + 5 = 15 … … … (1)

-3 + 1 = 7 … … … (2)

Don’t worry about all of the decimal places you might see because Excel is doing all of the work for you; and your answers should be x = -1.176470588 and y = 3.470588235

Carry out the proof to make sure your answers are correct … don’t look at my solution in my Excel work book until you are happy with your own!

Method 2: Direct Method

Whilst this really is the same as the Indirect method, I am flagging it here so that those of you who are confident in your own abilities and/or who doesn’t need to see the inverse matrix, can go straight to the solution:

simult_2

Select the range G8:G9 and keep it selected then type the following =MULT(MINVERSE(A4:B5),D4:D5) … CSE …

There you are, straight to the answers, x = 5 and y = 1.

Repeat this for the equations

2 + 5 = 15 … … … (1)

-3 + 1 = 7 … … … (2)

And prove your method and answers!

You’ve now got a template for solving any two variable set of simultaneous equations where there is a solution.

Three Variable Simultaneous Equations

Many students can solve two variable simultaneous equations but then get stumped by three and more variables. If I were you and you need to learn and apply rules like Cramer’s rule in full, find the solutions here then work backwards to use the more formal methods … that’s a top tip for you!

Here is a screenshot of a three variable set of simultaneous equations:

simult_4

You can see I have set it up in full for you and have colour coded the solutions since that part of the work sheet is getting a little crowded now.

BEFORE you look at my solutions in my work book, try to work out how to solve a three variable simultaneous set of equations yourself.

You should have done this: indirect ad direct methods both shown here:

simult_6

Make it work, make sure it works before you move on. In my workbook there are additional questions for you to work on.

Method 3: SOLVER

SOLVER is a powerful add in that you might need to install before you read on

File

Options

Add Ins

Manager … Excel Add Ins … Go

Select SOLVER Add In

Go

The link to SOLVER will appear in the top right hand corner of the Data tab just click on it to start it.

Back to question 1

Set up the SOLVER area as you can see here in the range H3:I4 and J5:j6:

 

The entries in H3:I4 are just typed and you do NOT need to enter anything in cells H4:I4 since SOLVER will over write anything in there with the solutions to the problem. Alternatively, type in any numbers you like to test whether your entries in J5:J6 seem to work:

We do two things here: first the mathematics and then the Excel.

Mathematics: rearrange both equations to make them = 0, like this:

X + 3y = 8 … … … (1)

X – 2y = -3 … … … (2)

Becomes

X – x + 3y = 8 – x … … … (1)

X – x – 2y = 3 – x … … … (2)

Which becomes

0 = 8 – x – 3y … … … (1)

0 = 3 – x + 2y … … … (2)

You need to understand why I did what I did there … subtracting the Xs from the LHS AND the RHS to make the equations both = 0

Then in J5:J6 you can see what I did in the screenshot above. For example, in J5:

=8-3*I4-H4 … actually I have been a bit inconsistent there so feel free to reorganise that cell as you wish.

SOLVER will have found the answer to this question when it puts the correct values in cells H4 and I4 in this example and J5 = 0 and J6 = 0 … exactly in line with the work we have just done.

Set up SOLVER in this way:

Click Data tab … SOLVER

You will see this:

simult_8

Enter what you see there in the various boxes in case I emptied them before uploading the file! Ensure you understand what I have done because you are going to do it yourself in a minute for different and bigger questions: three and four variables.

Set Objective cell to J5 … SOLVER makes it read $J$5

To: value of 0 … this is vital

By changing variable cells, H4:I4 … again SOLVER adds the $$ and these are the cells that will give you your answer

Subject to the constraints:

In this case there is only one and that is J6 … click Add … and make it look like this:

simult_9

Click OK

Now click Solve and SOLVER should tell you it has found a solution, put the solution in H4:I4 AND shown that J5 and J6 are both = 0. THAT is your answer. QED x = 5 and y = 1.

Try This

Repeat the above as you try to solve these equations for x and y:

2 + 5 = 15 … … … (1)

-3 + 1 = 7 … … … (2)

You have already solved this problem but use SOLVER as well, for practice.

Three Variable Simultaneous Equations using SOLVER

Use SOLVER to solve the three variable problem we explored above and here is a screenshot of that three variable set of simultaneous equations:

simult_4

BEFORE you look at my solutions in my work book, try to work out how to solve tis problem using SOLVER yourself.

That’s it! Get this to work and it is a real time saver. If you can make the equations work but not SOLVER don’t worry, at least you’ve found one way that works. And vice versa: if you can use SOLVER but not the equations …

Download my Excel file from here fon_simult where you will see these questions and four variable simultaneous equations together with a problem that has no solution … so you can see what happens if the teacher or lecturer gives you a problem that cannot be solved.

I have also included in this file this formula that you might be interested in exploring:

=SUMPRODUCT(A4:D4,TRANSPOSE($E$9:$E$12)) … CSE

You will see that formula in the four variable questions but you an just as easily use it for the two and three variable questions as well.

That’s it! Hope this works for you!

Download the Excel file fon_simult

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: