(Part II) Excel Tips: Solving Non Linear Simultaneous Equations and One Variable Non Linear Equations

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:

  1. Input the data of matrix A and b
  2. 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

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

  4. 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で気軽に化学工学 」 伊東 章

About these ads

About chemieingenieur

Let me introduce myself. My fullname is Baharuddin Maghfuri. I was born in 1988 in the city of Magelang, Indonesia. I spent my early childhood until my highschool in that peaceful small city. After that I study in Bandung Institute of Technology for just one semester. I hope you don’t think I dropped out. Ministry of Education, Culture, Sport and Technology of Japan gave me a big opportunity to study in undergraduate program in japan. And now, I live in Japan, studying Chemical Engineering in Tokyo Institute of Technology. I hope everything will be alright and I’ll complete the undergraduate degree in 2011, then just continue onto Master Degree with qualification Chemical Engineering. Most of my interests are related with my study, photography, pop music and computers: - Chemical engineering in undergraduate level, such as Stoichiometry, Thermodynamics, Transport Phenomena etc. - Studying languages – I am able to communicate in English, Japanese, Indonesian. Now I’m trying to learn Germany. - I love to spend my free time hang out with my friends from Indonesia and taking pictures. - The C Programming and other codings also makes me happy and of course blogging at time to time. But nowdays, beside doing my interests, Indonesian community here asked me to work with them in some volunteer activities. The followings are some of them. You might find my name in their sites. 1. Kammi Jepang 2. PMIJ 3. PPI Tokodai 4. KMII Jepang and so on.
This entry was posted in Sci-Tech, Tips-Trick. Bookmark the permalink.

4 Responses to (Part II) Excel Tips: Solving Non Linear Simultaneous Equations and One Variable Non Linear Equations

  1. owk says:

    ini hasil coba2 po?

  2. meongijo says:

    gaaah… I’m too old to learn matrixes :P

    • chemieingenieur says:

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

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