## Solving Simultaneous Equations with Excel

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. 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: 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: Select the range G8:G9 and keep it selected then type the following =MMULT(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)

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: 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: 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

Manager … Excel Add Ins … Go

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: 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: 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: 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!

Duncan Williamson

### 7 Responses to “Solving Simultaneous Equations with Excel”

1. […] From figure 1, the intercept of y1 and y2 is approximately (2.4, 4.8). By using simultaneous equations, we can work out the exact value of the intercept, which is interesting as the intercept is the point at which the force applied to the materials results in them having the exact same deformation. However, using excel’s MMULT and MINVERSE matrix functions, we can easily calculate the values for X and Y using matrices, all inside the excel workbook. […]

2. […] Solving Simultaneous Equations with Excel […]

3. savvas Says:

i thought simultaneous equations was something like autoregression with more variables and more past observations
can you check if i am wrong?

• excel2007master Says:

No!

Simultaneous equations relate to the situation where we set up two or more equations in which all instances of the X variables have the same value as each other, all instances of the Y variables have the same values as each other and so on. That is why they re called simultaneous because everything is equal at the same time!

4. […] Excel with Excel Master. (2018). Solving Simultaneous Equations with Excel. [online] Available at: https://excelmaster.co/solving-simultaneous-equations-with-excel/ [Accessed 14 Nov. […]

5. […] Excel with Excel Master. (2019). Solving Simultaneous Equations with Excel. [online] Available at: https://excelmaster.co/solving-simultaneous-equations-with-excel/ [Accessed 8 Nov. […]

6. Latrisha Nistler Says:

I’m not that much of a internet reader to be honest but your sites really nice, keep it up! I’ll go ahead and bookmark your website to come back later on. Many thanks

This site uses Akismet to reduce spam. Learn how your comment data is processed.