Solving Ordinal Differential Equation Using Ms. Excel

It’s been a while. I’m sorry for the absence. Actually I’m not so busy, just there came exam period which made my blogging mood decreased. Now I want to begin with what I had played recently: a game called Ms. Excel 🙂

—General form of ODE—

Initial value ordinary differential equations (ODEs) are among the most widely used forms of mathematics in science and engineering. However, most ODE models are complicated enough (e.g., sets of simultaneous nonlinear equations) to preclude analytical methods of solution; instead, numerical methods must be used, which I want to talk through this post.

The calculation of a numerical solution usually requires that numerical integration algorithms are implemented in library routines. The library routines in turn can be coded (programmed) in a variety of programming languages. Typically, for a scientist or engineer with an ODE – based mathematical model, finding routines written in a familiar language can be a demanding requirement, and perhaps even impossible (if such routines do not exist). However not all people understand or have time to code a program which calculates the solution of a set of ODEs.

That’s the reason I post this. I’d like to give you an useful tool to solve your ODEs with a software everyone can get easily : Microsoft Excel. The solution method is based on Runge-Kutta method, and  It’s been successful to solve many of my ODE problems especially problems related to my field, chemical engineering . I wish it could satisfy you too:-)

To use my tool, you can just download this file Runge-Kutta.xls (but remove extension .doc first)  , then open it, and you’d find a worksheet like this:

I assume that you’re using Ms. Excel 2007 or 2010 so I’ll explain you as you’re using one of them. And make sure that Macro in your Excel is enabled. Now, let’s work on one famous model: Damped Simple Harmonic Motion


For this phenomenon, we can model it into a “simple” equation of motion:

Which v equals to dx/dt, and M, D, k, f are constants. Hence, there will be two ODEs, they are:

Now, our task is to solve them with our Excel. Just follow simple procedure below:

  1. Because we have two equations, fill cell B1 with 2
  2. Because our variables are t, x, and v, change y into v, and v’ into y’. Our variable is v, not y.

    Note: If you have three equations or above, just fill B1 with 3, and you can just put another variable in D3

  3. Fill H3 and cells below it with our constants, and suppose we have a 0.5 kg burden, a spring with k = 2 kg/s2 and damper 0.3 kg/s, and a constant outer force 1 Newton, just like below.

  4. Fill in cells B5:B6 with our equations. I mean fill in B5 with =C3 , and B6 with =(-I3/I2)*C3+(-I4/I2)*B3+(I5/I2)
  5. We will try to integrate the ODEs we have from 0 to 20, so fill B7 with 0 and B8 with 20. Then determine a step size. Too big step size might cause some significant error, and too small step size make calculating time longer. We try to put 0.2 into B9.
  6. Now, put any initial value into cells A12:A14.
    For Instance, at t=0, x=1.0 m and v=0 m/s.
  7. After above steps, the worksheet must be like this

  8. Now press CALCULATE, and the VBA code inside our worksheet will integrate and display the results under cells filled with initial values.
  9. From result data, we can draw a graph x and y versus t like below

    We can see that it just resemble the typical graph of Damped Simple Harmonic Motion, but be careful that we have to compare it to analytical solutions of this model, then calculate the sum of the error. Try by your selves, but I’m pretty sure the error is small enough to be neglected 🙂

    Perhaps my next posts would be the application of this tool. I hope you can also share your ideas.

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 and tagged , , . Bookmark the permalink.

8 Responses to Solving Ordinal Differential Equation Using Ms. Excel

  1. senpaygalak says:

    baca judule thok, sudah ngelu saya ?*__*?

    ごめん、今回は諦めさせてもらう。。

    Semoga bermanfaat untuk yang mengerti 🙂

    • chemieingenieur says:

      Waah, ngga papa mbak.. Dcomment aja sy udah ngelunjak-lunjak (bo’ong ding 😀 )
      Makasih banget udah visit, lho mbak sempay galak..

  2. fakhria says:

    Wow!

    *tulisan ini membuat bahar nampak pintar, hehehehhe

    • chemieingenieur says:

      “nampak” aja ya….. mujinya keknya setengah hati neeh 😀

      Btw, fafa kan Biotek..tau mekanisme Michaelis-Menten kan ya? Dengan memakai tools ini, barusan aku nyoba-nyoba analisis apakah cocok kita pakai method of steady state ([ES]= constant) untuk penyelesaian persamaan. Setelah beberapa kali nyoba ternyata……….cocok 😦

      Sasuga mbah Michaelis, mbah Menten. Karena persamaan sederhana itu aja mereka dapat hadiah Nobel..

  3. fakhria says:

    Hoo.. baiklah, tapi kuliah2 yang make persamaan itu udah lewat, semester kemaren harusnya.hehe.

    OK, I will back to his page when I need it. Nice post!

  4. This post was very well written, and it also contains many useful facts. I enjoyed your professional way of writing the post. Thanks, you have made it easy for me to understand.

  5. ubotstudio says:

    Super information,I have bookmarked this site to my seo list for future and will keep a eye on your other postings.

  6. jose says:

    Hi, I would like to access the code b/c I’m interested in learning how to program in excel. Any change you could share a non-password protected version of the spreadsheet?
    Thanks

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