This is the continuation of my last post:” Excel Tips: Solving Non Linear Simultaneous Equations and One Variable Non Linear Equations” . I’ve discussed about how to solve the one variable non linear ones, I feel guilty because I haven’t told you how to solve the** non linear simultaneous **equations. If it is a linear one, then it’s easy because we can do it with matrices. Simply stated, just find the inverse matrix then the solution comes out. Even you can use Excel for this.

Suppose we want to solve simultaneous equations below.

We can convert the simultaneous equations above into matrix form **Ax = b **where

, , and

The solution of the simultaneous equations can be easily obtained using inverse matrix

**Ax = b**

On an Excel spreadsheet (I’ll show you the steps in Excel 2007), just follow the following step:

- Input the data of matrix
**A**and**b** -
On the [Formula] tab, click [Define name], then a defined-name dialog will appear. Define matrix
**A**, and with the similar way, define matrix**b** -
In a cell where do you want to put the inverse of matrix
**A**, you type**=MINVERSE(A****)**. It will give you only one number. Now using the mouse highlight the range where do you want to put the inverse of matrix**A**. Because you know that the size of inverse**A**is also 3 by 3 matrix, highlight only that range. While highlighting, press F2 and then Shift-Ctrl-Enter. You will get the inverse of matrix**A**.Then

Plassh! The inverse matrix appears

- Now you define name of inverse matrix and use matrix multiplication function to get the solution. To do that, in the cell where do you want to put the solution, type
**=MMULT(Inverse_A,b).**It will give you only one number. Now using the mouse highlight the range where do you want to put the solution. Because you know that the size of solution is a single column with 3 rows, highlight only that range. While highlighting, press F2 and then Shift-Ctrl-Enter and you will get the solution.

Now it’s time to solve the bothering one. That’s** Non** Linear Simultaneous Equations. Now I gonna solve the equation I proposed at the last post.

In this case, except T_{2}, Q, and t_{2}, all of the variables are already known.

1. Fill in the cells with the known values (A, U, T_{1}, t_{1}, W, Cp, w, cp).

2. After that, move the Q to the right side of the each of the equations respectively, then fill those in the designed cells as below.

3. Fill in the cells **B5~B7 **any numbers as the initial value. But remember not to put values that make cells **D5~D7 _{
}**become error. You know, that the third equation involve

*natural logarithm*which no

*ln*for 0 or minus value, and that equation also contain fractions, which the dominator can’t be zero. I just put some numbers like below.

4. And this is the main thing. We’re going to solve the equation using **SOLVER** tool. For Excel 2003, you can find that brilliant tool in [Tools]=>[Solver] and for 2007, you can find it in the [Data] tab , in [Analysis] column. But if you don’t find **solver**, that means you haven’t installed yet. You can see [HERE] to find out how to install it.

5. Click the solver icon and a dialogue window comes out. What Excel is going to do is to calculate the value of the formulas in cells **D5~D7**_{
using the initial value of cells B5~B7, and if the result is not zero (which mean the values don’t satisfy the equations), then Excel will change the initial value of T2, t2, and Q until D5~D7 contain only 0 (or near 0). So, in summary, we have to set D5~D7 to zero, and set B5~B7 to be the cells that their values might change. Fill in the cells just like below. }

_{ }

_{6. Click solve, then press OK if a dialogue window that tells you solver has found the solution comes out, And you’ll find the new contents of the cells. Confirm that D5~D7 are zero, and (HURRAY!!!!!) the B5~B7 would contain the solution we want to know. }

_{Remember The Keyword of the Day: SOLVER }

_{Have a nice day. }

_{Equations Resource : 「Excelで気軽に化学工学 」 伊東 章}

ini hasil coba2 po?

Ngga lah wek…hasil diajari orang, google sana-sini.

Btw, please comment in english ^_^

gaaah… I’m too old to learn matrixes 😛

Ohho…i’m sorry because i’m young, so i’m writing only “young” stuffs.

Please come back anytime you need a solution for matrix thing..

Cheers :=)