Optional Practical: Projectiles in excel

No air

In this exercise the way the presence of air changes the trajectory of a projectile will be investigated using a spreadsheet. This example will use sliders so you will need to make sure that you have the “developer tools” available. To do this, open excel and click file > options > customize ribbon > tick developer in the right hand box. You will now see the developer tab on the ribbon at the top.

The motion of a projectile can be split into two components x and y as shown below.

The horizontal component is constant so x = vcosϑ t and the vertical is affected by the acceleration due to gravity so y = vsinϑ t – ½ gt2 . The path of the projectile is found by plotting x vs y for a range of times.

Before starting to make the table of x and y we need to set up some sliders so we can change the initial velocity and angle. Label two cells velocity and angle, these need to be positioned to leave room for the table and graphs. I chose cells K17 and K18.

The values of velocity and angle are to be controlled with siders, to do this open the developer tab and insert slider (its actually called a scroll bar) from the active x options.

Place the slider from column M to Q so you leave a gap between the label and slider. To set the sliders properties right click it and select properties. If this doesn’t work you might need to click “design mode” in the developer tab.

For the velocity slider set the maximum value to be 50 and the linked cell to L17.
For the angle slider set the maximum at 90 and the linked cell to L18.
Try sliding the slider to see if the values change.

Now you need to set up the table to calculate the x and y displacements. Put headers in the first 3 columns as shown.

Fill in 100 time values from 0 – 10 in steps of 0.1. This is quickly done by filling in the first two (0 and 0.1) highlight them, click the bottom right hand corner and drag down to fill all the cells.

The displacements are calculated based on the initial velocity and angle for all the times in the first column. To do this use the following equations

For x =L$17*COS(L$18*PI()/180)*A3
and y =L$17*SIN(L$18*PI()/180)*A3-0.5*9.8*A3*A3

To view the path highlight the x and y columns and insert chart > scatter (I chose the one with the line but no points). Try varying the angle and velocity to see what happens.

To stop the graph autoscaling right click the axis and format axis. Choose x - max 200, min 0 and y – max 50, min 0.

Taking into account the air

The presence of air has two consequences

  • The downward acceleration is less because of buoyancy - this is easy to take care of since it simply means the acceleration due to gravity is less.
  • The speed slows down due to drag. This is more difficult since the drag force is dependent on the speed of the projectile. The way we are going to simulate this is by calculating a column of speeds, each speed is less than the previous one by an amount that is proportional to the previous speed.

Add two more sliders for the “buoyancy” (0 -10) and “drag” (0 – 1000)

Make a new column header “speed”. The first row of this column is simply =L17, the initial speed.
The second row is =D3-L$20*D3/10000 So it is the previous speed (D3) minus the drag constant /1000 multiplied by the previous speed. Copy this formula down (note the $, this stops L20 changing when you copy down).

The new x values are now calculated based on these velocities (instead of the initial one) using the formula =D3*COS(L$18*PI()/180)*A3

The new y values are also calculated using these velocities and the acceleration is also adjusted to take care of buoyancy in the following equation =D3*SIN(L$18*PI()/180)*A3-0.5*(9.8-L$19)*A3*A3

Plot a scatter graph based on these new values of x and y and compare it with the one for no air (set the x and y max and min the same as before). Try varying the drag and buoyancy.

Without air the maximum range is achieved at 45°, is this the same with air?

All materials on this website are for the exclusive use of teachers and students at subscribing schools for the period of their subscription. Any unauthorised copying or posting of materials on other websites is an infringement of our copyright and could result in your account being blocked and legal action being taken against you.