Numerically integrating in Excel.

We will illustrate the RK2 (Runge-Kutta 2nd order) numerical integration technique by doing the problem of an object falling subject to air resistance.  To understand the integration, we note that since v = dx/dt we can write  dx = vdt . This leads to

    D x @ v D t

Likewise from a = dv/dt we have dv = adt and then

    D v @ a D t .

We will numerically build up values of x and v by using the expressions for D x and D v above:

    D x = x2 - x1 = vmid D t        and

    D v = v2 - v1 = amid D t ,

where the 'mid' subscript refers to the middle of a time step D t.

Here is the scheme: evaluate x, v, and a at a half time step, then use the v and a values from the half time step to do a full time step. It is sketched out below:

At a full time step:                                                 At a half time step

    to                                                                     to+D t/2
    xo                                                                    xmid=xo+vo Dt/2
    vo                                                                    vmid=vo+ aoDt/2
    ao (from xo,vo, to)                                            amid (from xmid,vmid, tmid)

    to+Dt                                                             to+Dt+ Dt/2
    x1 = xo + vmidDt                                             etc.
    v1 =vo+ amid Dt
    a1 (from x1, v1, t1)
 

Here are instructions for the motion of a mass m with an initial velocity of voy subject to a drag force

    F = -Co v2

as it falls under gravity.

From the image, you can see the parameters are yo, delta_t, voy, g, m and Co. As a first step, type in the contents of cells A1..A7. Then put the numerical parameters in cells B2..B7.

Now you should 'name' each of the cells B2..B7. (Note in the image that cell B2 is selected, and in the white space in the upper left it has been given the name yo.)

To name cell B2, go to Insert/Name/Define. It should prompt you with 'yo', so all you have to do is click OK. (It picked the name up from cell A2). Now go ahead and name the other cells delta_t, voy, g, m and Co.

Next, type in the labels on lines 8 and 9.  In cell A10, enter '0'. In cell A11, enter '=A11+delta_t'. In entering this formula is is simplest to click on A11 instead of typing it, and clicking on delta_t (cell B3, and not cell A3!) instead of typing it.

In cell B10, enter '=yo'. The best way is to select B10, press '=', then click on cell B2. This is easier than typing B2 or typing yo. In cell C10 enter '=voy'. Then in cell D10 enter '=g + Co/m*C10^2'. In entering this formula, is is simplest to click on the cell referred to, rather than typing it in.  You now have the position, velocity, and acceleration a at time t=0.

In cell E10 you want the position at the half step. You use the velocity at the full step to do this, and you enter '=B10+C10*delta_t/2'. In cell F10 you want the velocity at the half step, so you enter '=C10+D10*delta_t/2'. For cell G10 we must have the acceleration at the half step, calculated from the velocity at the half step:  enter '=g+3/m*F10^2.

In cell B11, enter '=B10+F10*delta_t'. This brings the position forward one full step, based on the velocity at the half step. In cell C11, you enter '=C10+G10*delta_t', bringing the velocity forward one full time step.

To fill cells D11..G11, just copy down the cells D10..G10. This is done in two stages. First drag from the middle of D10 to the middle of G10 and release. This selects the cells to be copied. Now find the button at the lower right of cell G10. Hold the mouse button down, drag to G11 and release. This copies  the cells D10..G10 into cells D11..G11.

To give your calculation 500 rows, first select cells A11..G11.  Then copy them down to A510..G510.

To graph y vs t, first select cells A3..B503. { Start in the middle of cell A3 and drag down to cell B503. Or click cell A3, then to to cell B503, press Shift, and click on B503. } Then go to the graph wizard (This icon has colored vertical bars). Select the x-y scatter plot, and then click 'Finish'. This will bring up the graph.

You can adjust any of the parameters and see the effect on the graph:


Here is something I asked my students to do.  For homework, you are to work out the fall of a 50-kg mass subject to gravity and a drag force F = -0.05 v2, where v is the velocity. [This might correspond to a person jumping from an airplane before the parachute opened.] Work out the distance and speed for times of 1.0, 10.0, and 100.0 seconds.