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.
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 T2, Q, and t2, all of the variables are already known.
1. Fill in the cells with the known values (A, U, T1, t1, 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で気軽に化学工学 」 伊東 章