Excel Tips: Solving Non Linear Simultaneous Equations and One Variable Non Linear Equations

What a long innocuous title!! No, buddy! This kind of equations appear in various cases near us, especially when you face a math textbooks =)

Anyway, for you who “forget” the definitions, Simultaneous equations are two or more equations with multiple variables. A solution of those equations is a set of variables that simultaneously satisfy all equations. The linear equations are generally given as “y = ax + b,” while non-linear equations can be any expressions not described as linear (e.g. “5X3-7Y2 = 21″). Does that ring a bell??Except you was sleeping during the classes, I’m sure that at least in your junior high school, you have learned about how to solve that kind of equations.

Now I wanna ask you how to solve the following equation?

 x + 2y = 1,x2 + y2 = 57

It’s easy, isn’t it? You can just take some piece of paper and pencil, then scratch like below

x + 2y = 1 ——–(1)

x2 + y2 = 57 ——–(2)

From (1): x= 1- 2y ——–(3)

Substitute (3) into (2): (1-2y)2 + y2 = 10

1- 4y + 4y2 +y2 = 10

5y2 – 4y -9 = 0

(5y – 9) (y +1)= 0

y = 9/5 or -1

Substitute the values of y into the equation (3) to find the value of x.

when y = 9/5             x = 1 -2(9/5)=-13/5

when y = -1               x = 1 -2(-1) = 3

the solution set is x = , y = 9/5

or x = 3, y =-1

By the way, the above method is called Substitution method.

However, the real life is not as simple as substitution method. For example, what do you think about the solution of problems below?

1. The boiling point of 10% mol ethanol aqueous solution (like brandy, whisky, etc) under atmospheric pressure which involve 1 variable: t

2.  Heat (Q) balance in heat exchange instrument, simultaneous non linear equations which involve 3 unknown quantities (T2,t2, Q):

I’m not sure you can easily solve that with only a piece of paper (but if you’re an Mathematics Olympiad Participant, a Math Professor, or just a Math Geek, perhaps I’m wrong.) And if you’re a programmer or using expensive softwares like Matlab, Mathematica, and so on, those problems are quite easy. But now, I gonna share to you how to solve it using your Microsoft Excel only. Because I have already written too much in this post, I will only solve the first problem.

First, open your Excel, then enter the equation into cell B2, just like picture below.

The step after that is the main point. If you’re using an Excel 2003, you could press [Tool]->[Goal Seek], and if you’re using an Excel 2007, you would press Data tab, then [What-If Analysys], then [Goal Seek], and the setting window would open like below.

Enter just like what excel requests us, then excel will calculate repeatedly until the value of our equation in cell B2 reach zero. See cell B1, and you have the solution for this equation, which t =359.6 Kelvin. This means that alcoholic drink would  boil at 86 ℃. This method is quite simple, isn’t it??

At last, please remember keyword of the day: GOAL SEEK..

Good luck!

Problems Source:「Excelで気軽に化学工学 」 伊東 章

Advertisements

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.

11 Responses to Excel Tips: Solving Non Linear Simultaneous Equations and One Variable Non Linear Equations

  1. Andy says:

    Thanks for all of this great information! You’ve giving us a very easy to follow tutorial. Keep up the awesome posts!

    And when you get a chance head over to http://www.facebook.com/office and tell us more about your use of Excel!

    Cheers,
    Andy
    MSFT Office Outreach

  2. chemieingenieur says:

    Wow, nice comment, and thanks for the information about that facebook link =)

    I can’t wait for the Office 2010 to be available.

    Regards

  3. fakhria says:

    wow.. kerenn (yang komentar pertama maksudnya) soalnya aku ga ngerti postingannya haha..

    Good Job Baharu!

    • chemieingenieur says:

      Waduuuuuuuuuuuh, berarti aku gagal sebagai writer ^^
      Coba dibaca pelan-pelan fa..ini cuma penyelesaian persamaan matematik biasa. Cuma aku kasih tips bagi pemakai Excel, itu aja…quite simple, isn’t it?

      Aku yakin suatu saat cara ini kepake…

  4. [Gm] says:

    “…or using expensive softwares like Matlab, Mathematica, and so on, those problems are quite easy. But now, I gonna share to you how to solve it using your Microsoft Excel only…”

    You don’t think MS-Office expensive? :D… I guess it isn’t that expensive for students, though :-).

    Btw, great tips! I normally don’t work with those kinds of equations anymore (most engineers I know are using either conservative or simplified calculations), but you have shown us what Excel can do.

    Great job! Thanks…

    • chemieingenieur says:

      Thanks for the polite comment, mas Goio…
      No, I don’t think excel is expensive either. In Titech, with “only” 1400 Yen, students are able to get Microsoft Office, and in ITB, it’s even cheaper: 10,000 Rupiahs for 1 year (legal) license..

      Cheers :=)

  5. Christoffer marxen says:

    Hello.

    Is it possible for you to show the example with the Heat balance, involving 3 equations with 3 unknowns nonlinear?
    I’ve got a similar problem and cant seem to wrap my head around it 😦

    Thanks.

    Christoffer.

  6. syaril says:

    it was a nice explanation but how to solve for three unknown of non-linear simulteneous equation just like your second example? hope you can help.

  7. jyn says:

    hello, is it possible to solve 2 complicated equations with two unknowns? i mean, for example, i have to compute wet bulb temperature with dry bulb temperature and vapor pressure given.. i’m really having a hard time finding out how..

    thanks for any help! :))

    jyn

  8. ANIKET HULAGE says:

    how to solve 2 nonlinear simultaneous equations in ms-excel using goal seek or by any method in ms-excel????????? please reply….

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