The first successful attempt to describe projectile motion quantitatively followed from Galileo's insight that the horizontal and vertical motions should be considered separately, then the projectile motion could be described by putting these together.

 Galileo argues that, if air resistance could be neglected, the horizontal motion was one at constant velocity, the vertical motion was one of uniform downward acceleration, identical to that of an object falling straight down.

 It's easy to reproduce this compound motion with a spreadsheet. Let's call it Projectile1, and write in A1 "Motion of a Projectile Under Gravity"

 There are three variables: the initial horizontal velocity, call it v_x_init, the initial vertical velocity v_y_init, and the acceleration due to gravity g. In contrast to our earlier spreadsheets on falling objects, we will now take the upward direction to be positive.

 Of course, we also need to specify the time interval used in our discretization of the motion, we'll call it delta_t as usual.

 Since we're interested in both velocity and position of the projectile as functions of time, we'll construct a spreadsheet with five columns: time, v_x, v_y, x, y. (Of course, v_x isn't going to change, but we're going to need that column when we include air resistance, so we might as well put it in now.)

 In A7,A8,A9 and A10 write respectively g= , v_x_init= , v_y_init= and delta_t= . Click on B7, click Insert/Name/Define, it will suggest name g, click OK. Put the appropriate names in B8, B9 and B10, and then enter some reasonable values, say, 10, 20, 30, 0.05, ready for when we construct the table.

 Now, in A14, B14, C14, D14 and E14 write time, v_x, v_y, x, y. Then select these cells, click Bold, and Center justify. (Also, Bold and Right Justify A7, A8, A9, A10.)

 In A15, B15, C15, D15 and E15 write 0, v_x_init, v_y_init, 0 and 0.

 In A16 write =A15+delta_t. In B16 write =B15. In C16 write =C15+g*delta_t. In D16 write =D15 + 0.5*(B15 + B16)*delta_t. In E16 write =E15+0.5*(C15 + C16)*delta_t.

 Now, select cells A16 through E16 and copy all five columns down through E214.

 Highlight cells D15 through E214, and click Chartwizard. Call the graph Projectile: Zero Air Resistance, label the axes "distance along ground" and "height above ground".

Having done that, save it again as Projectile2. We're going to do some more work on it, but don't want to lose what we've done so far.

One problem with this spreadsheet as it stands is that it doesn't know when to stop-the ball falls back to ground level, then continues right on into the ground. Assuming we're throwing a ball in a level field, this is an undesirable feature-we'd like it to stop when it gets to ground level.

We want to tell the spreadsheet that if it finds the ball will be underground on the next step, stop right there! (Of course, this means we'll stop the ball slightly above ground level, but if the step size is small, this won't be a big error, and we'll ignore it for now.)

Excel has an IF function. It's written IF(Statement, X,Y). "Statement" is some logical statement, such as E13<0. If the statement is true, Excel does X. If it's false, Excel does Y.

 Now, in E16, the y-coordinate of the ball, we have: =E15+0.5*(C15+C16)*delta_t

 We replace this with:

This means that as long as the ball will still be above ground after this step, do what you were doing. BUT if this step is going to get you below ground level, don't do it-stay where you are, just put E16 = E15. This stops the ball falling further, but if we want it to really stay where it is we must also stop the horizontal motion! So, in D16 we write: =IF(E15+0.5*(C15+C16)*delta_t>0,D15+0.5*(B15+B16)*delta_t,D15)

We then copy these formulas down to D214, E214.

To get a bit more practice with IF statements, let us find the range of the projectile, how far away it lands. This would be the maximum value of x it attains, except that if we choose delta_t too small, it might still be in the air at the end of the 200 rows of calculation, so then the value of x is not the true range. Of course, we could figure this out by looking at the graph, but it's nice to have it done automatically.

 In A11 we write range= , and in B11 we write:

=IF(D214=D213, D214,"still in air")

This translates as "If the last two horizontal positions of the particle in the table are the same-so it's come to rest-then range = the horizontal position, otherwise write "still in air".

Then resave it as Projectile3. The reason to keep resaving the spreadsheet under new names is that if it gets too messed up, you can always go back a few steps, not to the beginning!

We've so far followed Galileo's analysis, treating the horizontal and vertical motions separately. For a gun, of course, the more natural variables would be the initial speed of the bullet v and the angle q of the gun barrel to the horizontal. It's usually simpler to adjust the angle of the barrel than to adjust the bullet speed (although that may have been less the case for cannon in Galileo's day).

In terms of these variables, v_x_init = vcosq and v_y_init = vsinq . In Excel, the sin and cos function take arguments in radians, where 360 degrees = 2p radians. Excel writes p as PI().

 So the formulas we want are: v_x_init = v*cos(theta*PI()/180) ,

v_y_init = v*sin(theta*PI()/180).

In A3 write: Projectile fired at speed v at angle theta degrees to horizontal.

 In A5 write v= , in A6 write theta= . Insert the corresponding names in B5 and B6.

 Now, in B8 and B9 write =v*cos(theta*PI()/180) and =v*sin(theta*PI()/180) respectively.

This spreadsheet should now be ready for action!

Exercise: Fix some value for firing speed v, and vary theta to find the maximum range. For this zero-air-resistance case, the maximum range occurs at theta equal to forty-five degrees. There will be some small error in the spreadsheet-we have replaces a smooth flight be a series of steps. Furthermore, the end of the path is a bit untidy as we've set it up (there is room to improve this spreadsheet!). Still, the accuracy is within one percent, and that's ample to give a clear picture of the physics, not to mention that real life variables, such as wind, etc., are such that further accuracy is probably meaningless.

 See what kind of accuracy you get for different v's and delta_t's, and think about why the accuracy varies.

As usual, resave the spreadsheet as Projectile4. We are about to add air resistance.

Let's assume that the air resistance is proportional to the square of the velocity, and, of course is directed backwards, so it has magnitude -bv², where b is a coefficient and v is the speed of the projectile.

It's useful to keep track of the speed of the projectile, so we'll put in an extra column for that. Click on the D above the cell D1. This will select the whole column which tracks the x-position. Now click Insert/Columns and a new blank column will appear. The new column will be D, the x-positions are now in E. (Excel will automatically adjust formulas.) Write "v" in D14.

 Enter in the new D15: =SQRT(B15*B15+C15*C15)

 Copy this formula down to the end of the table.

 We're now ready to include the drag force in the equations of motion. These, of course, give the rate of change of the horizontal and vertical components of the velocity, in other words the vector equation F = ma is split into components F_x = ma_x, F_y = ma_y. Up to this point, we have had F_x = 0 and F_y = -mg. We must now add the appropriate components of the drag force. It is a vector of magnitude bv², and direction antiparallel to v. Hence its x- and y- components are in the same ratio to its total length as the corresponding components of v. So, the components of drag force felt by the projectile are -(v_x/v)bv² and -(v_y/v)bv².

 The full equations of motion are:

ma_x = - (v_x/v)bv², ma_y = -mg - (v_y/v)bv², (we'll take m = 1 for now, though.)

The spreadsheet will as usual calculate the change in velocity components from one row to the next using

v_x(t+delta_t) = v_x(t) + a_x(t)delta_t.

To include the drag force in this computation, we might write in B16:

=B15 - b*(B15/D15)*D15*D15*delta_t

and in C16:

=C15-g*delta_t - b*(C15/D15)*D15*D15*delta_t

Actually, we are wasting the computer's time here! We could cancel the D15 in the denominator against one of the D15's in the numerator, to give in B16:

and in C16:

These formulas are now ready to copy down to fill the table.

Exercise: You should spend some time playing with this spreadsheet! Find out how including air resistance affects the maximum range, and the best angle to shoot at for maximum range. Do you think it's greater than or less than 45 degrees? Find out. Also, see how the shape of the path is altered. Here's an example:

Incidentally, the pre-Galilean medieval theory of projectiles was that they went pretty much in a straight line until they'd "used up" their initial momentum, whereupon they dropped right down. It's easy to see this theory looks a lot more plausible with high air resistance. (It's also of course a picture many people still have -- when roadrunner runs off the edge of a cliff, he goes in a straight line until he looks down, realizes where he is, then drops!)