The speed can be analyzed as two separate components: horizontal and vertical. The only time that they interact is when the object hits the ground. The initial horizontal speed is speed*cos(angle). The initial vertical speed is speed*sin(angle).
Each line of your spreadsheet represents one tenth of a second. If the vertical position is or becomes zero or negative, set both speeds to zero. Otherwise, in that tenth of a second, the horizontal position changes by a tenth of the horizontal speed in metres per second; similarly the vertical position changes by a tenth of the vertical speed in metres per second. The horizontal speed does not change (so long as the altitude is positive), meaning that we are ignoring air resistance and any other such factors. The vertical speed changes based on acceleration due to gravity, again ignoring air resistance. In a tenth of a second, if we are on Earth (and near sea level), the vertical speed will decrease in the upwards direction (or increase in the downwards direction) by 0.98 metres per second.
The user of your spreadsheet will be able to experiment with different speeds, heights, and angles. Which angle seems to be the best for making the projectile travel the farthest? (It is neither horizontal (0) nor vertical (90).)
Let's take an initial speed of 1 m/s, an initial height of 2 m, and various initial angles. The initial horizontal and vertical speed components will be calculated by the formulas speed*cos(radians(angle)) and speed*sin(radians(angle)), and you can believe my numbers for this below (and check it in gnumeric yourself). The other calculations below you should be able to follow on this web page.
You should consider the initial horizontal position to be 0. We have to call it something... 0 is a good number for it.
The initial vertical position, however, will be what the user specified as the height. This way, when the vertical position becomes zero, then you know that the projectile has hit the ground and will stop.
Since each step is a tenth of a second, each of the speed components has an effect calculated by dividing it by ten. For example, if we are moving at 30 m/s in the horizontal component, and the horizontal position is 45 m in one row, then it will be 48 m in the next, because we add 30/10 which is 3.
Actually, the speed of 1 m/s used here is really feeble, because gravity will counteract that really quickly. 10 m/s makes a better example on a spreadsheet but makes the problems too long to be interesting on the chalkboard in tutorial, or, to some extent, on this web page. I suggest 10 as a better initial speed for your playing around in your own spreadsheet.
First example: 45 degrees. (Still an initial speed of 1 and an initial height of 2.)
The sin and cos of 45 degrees are both about 0.71. So the initial horizontal and vertical speed components are this value times the overall initial speed, which we're setting to 1.
So at time 0, we have a horizontal position of 0, a vertical position of 2 (that's the initial height parameter), and horizontal and vertical speeds of 0.71.
At time 0.1 (the second row), we add these speed components to the position components to find the new position. But only a tenth of the actual speed numbers, remember, because those are the speeds in metres per second and this represents only 0.1 seconds elapsing. I'll round everything to two decimal places here (don't do that in your spreadsheet), so in dividing 0.71 by ten I'll make it 0.07. So the new position is horizontal 0.07, vertical 2.07. This means that it's going upwards.
We also need to compute the new speed components at this time 0.1. The horizontal speed remains the same until it hits the ground. The vertical speed decreases by 9.8 m/s every second, which is 0.98 m/s in a tenth of a second, so it is now 0.71 minus 0.98, which is -0.27. So now it's starting to go down already. That's awfully quick, isn't it! This is what I meant above by saying that an initial speed of 1 m/s is too wimpy. Try a higher initial speed number in your actual spreadsheet.
So at time 0 we had:
The next row will be time 0.2. We add the speed components (divided by ten) to the position components to find the new position. The horizontal position will be 0.07 + (0.71/10), which is about 0.14. The vertical position will be 2.07 + (-0.27/10), or about 2.07 - 0.03, which is 2.04.
But it is accelerating downward. The horizontal speed is still 0.71, but the vertical speed is -0.27 - 0.98, which is -1.25. So the vertical position at time 0.3 will be only 1.92 metres off the ground.
Altogether we get the following data, where I've used abbreviated names for the columns to make it fit nicely.
| time | hpos | vpos | hspeed | vspeed |
|---|---|---|---|---|
| 0 | 0 | 2 | 0.71 | 0.71 |
| 0.1 | 0.07 | 2.07 | 0.71 | -0.27 |
| 0.2 | 0.14 | 2.04 | 0.71 | -1.25 |
| 0.3 | 0.21 | 1.92 | 0.71 | -2.23 |
| 0.4 | 0.28 | 1.7 | 0.71 | -3.21 |
| 0.5 | 0.35 | 1.38 | 0.71 | -4.19 |
| 0.6 | 0.42 | 0.96 | 0.71 | -5.17 |
| 0.7 | 0.49 | 0.44 | 0.71 | -6.15 |
| 0.8 | 0.56 | 0 | 0 | 0 |
| 0.9 | 0.56 | 0 | 0 | 0 |
| 1 | 0.56 | 0 | 0 | 0 |
| 1.1 | 0.56 | 0 | 0 | 0 |
| 1.2 | 0.56 | 0 | 0 | 0 |
(Of course, all of those cells above will be formulas, except for the initial time and initial horizontal position. Above this table in your spreadsheet you should have labelled cells for the user to type in the initial speed, height, and angle, as requested on the assignment handout.)
Your numbers in your spreadsheet won't be exactly the same as above because they will be more accurate, because you won't round them off.
Here is a calculation for 70 degrees. You should be able to duplicate this calculation as well, after believing me about the initial horizontal and vertical speed components.
| time | hpos | vpos | hspeed | vspeed |
|---|---|---|---|---|
| 0 | 0 | 2 | 0.34 | 0.94 |
| 0.1 | 0.03 | 2.09 | 0.34 | -0.04 |
| 0.2 | 0.06 | 2.09 | 0.34 | -1.02 |
| 0.3 | 0.09 | 1.99 | 0.34 | -2 |
| 0.4 | 0.12 | 1.79 | 0.34 | -2.98 |
| 0.5 | 0.15 | 1.49 | 0.34 | -3.96 |
| 0.6 | 0.18 | 1.09 | 0.34 | -4.94 |
| 0.7 | 0.21 | 0.6 | 0.34 | -5.92 |
| 0.8 | 0.24 | 0.01 | 0.34 | -6.9 |
| 0.9 | 0.27 | 0 | 0 | 0 |
| 1 | 0.27 | 0 | 0 | 0 |
| 1.1 | 0.27 | 0 | 0 | 0 |
| 1.2 | 0.27 | 0 | 0 | 0 |
Your spreadsheet numbers will look a bit different than this. The way I'm doing it above, I'm dividing 0.34 by ten to get 0.034, then rounding that to two decimal places to get 0.03, which is pretty far off and the error accumulates from line to line. If you do the calculation without using ROUND() (and in your spreadsheet you should indeed not use ROUND()) then you will get an answer more like 0.307818... .