02-Mar-2015: Using Microsoft Excel to numerically compute a non-constant acceleration problem.

     The purpose of this lab was to develop skills using Microsoft Excel to help solve difficult problems numerically instead of analytically. The problem given is a non-constant acceleration problem. A 5000-kg elephant is riding on frictionless roller skates with an initial velocity of 25 m/s. The elephant reaches the bottom of a hill and arrives on level ground. A rocket, which is strapped to its back, then produces a constant 8000 N thrust in the opposite direction. The mass of the rocket changes with time due to burning fuel, which is given by

     The questions asks to find the distance the elephant travels before it comes to rest. 

   
     Since the rocket is strapped to the elephant, they can be treated as one system. This gives a new function of

 

     The famous formula of force=mass*acceleration is given from Newton's second law of motion. In this problem, since acceleration is a function of time, it is rearranged so that

     Since the elephant is on level ground riding on frictionless skates, the net force would be -8000 N caused by the thrust of the rocket. Now that Fnet and m(t) are known they can be plugged in, which gives:

which simplifies to

     A normal analytical approach to this problem would be to integrate this function twice to derive an equation first for v(t) and then for x(t). Then solve for t using the velocity function by plugging in zero for final velocity. Doing that gives a time of 19.69075 seconds. Plug that time into the position function and solve. That gives a position of 248.7 meters. Although this problem can be solved analytically, it gets quite messy and requires more time than a person would want to spend on a problem. Below are the messy integrations we hope to avoid by using a numerical approach. (click the pictures to expand them for a closer look)

     

     Now take a look at the numerical approach. 

Microsoft Excel was used for this portion of the lab. After a new worksheet was opened, the first and second rows were filled in with the six variables and initial parameters from the problem, as shown below.

• Next, a time interval of 0.1 seconds was chosen and filled down to over 220 rows. 
• Then, the acceleration function from earlier was plugged into cell B2 and filled down to B3. This enables the user to find the acceleration at any point in time. 
• In cell C3 the average acceleration was found for that time interval. 
• In cell D3 the change in velocity was found for the first time interval. 
• In cell E3 the final velocity was calculated at the end of the time interval. 
• Finally, in cell F3 the position of the elephant was calculated by adding the average velocity times the change in time to the position at the start of the time interval. 

     After all formulas were plugged in, a simple fill down to the desired amount of rows was done (all formulas are shown below).

After "filling down", the point where velocity v is closest to zero was found. 

     As you can see from the above data, the numerical approach gives a very close approximation to the analytical approach. Changing the time interval to a smaller one to get a better approximation can be done, but there is a certain point where not much will change except in the thousandths place. For instance, below is the data for a time interval of 0.05 seconds.

     Although it does give a better approximation, unless you are overly concerned about the number of significant figures then a 0.1 second time interval is enough. Using a time interval of 1 second, however, is too big. The data given by that time interval is too far off from the results given by the analytical calculation (it gave a distance of 248.3 meters). By using Microsoft Excel to do this problem numerically, we were able to let the computer do the integration for us and give an extremely close approximation. This saves time and brain power!