The school computers have XP Prfessional running on macs, with mac keyboards. I need to use the LINEST function in excel, which requires an array for the full statistics to be displayed. When I try the normal method of creating an array (F2 followed by Ctrl+Shift+enter) on the mac keyboard it doesn't work.

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) 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.