Physics 200 (Stapleton) Name ____________________
Directions – Trajectory with Drag Spreadsheet
Before you begin, copy the template. Leave the values in the yellow cells.
1. Initial angle in radians is the inverse tangent of Y velocity / X Velocity. The spreadsheet function will be “=atan(B6/B5)”
2. Initial direction in degrees is achieved by converting the radians angle to degrees. The formula is “=degrees(B3)”
3. Initial Speed – use Pythagorean theorem. Vy^2 + Vx^2 = initial speed^2
4. First row of calculations (row 14). ***Important note: any cell reference to a cell above row 13 can (and usually shoud) include dollar signs to keep the cell reference absolute rather than relative***.
a. Time = starting time
b. X velocity = initial x velocity
c. Y velocity = initial y velocity
d. Weight = -mg. Use dollar signs so that this formula can be copied downward without the values changing. It should look like “=B$7*B$1” Make sure it’s negative, because weight is downward.
e. X drag -- Use the drag formula, but there’s a tricky twist. Drag = 0.5*Cd*Air density*Cross-sectional Area*Velocity^2. For X drag, you will just be using the X velocity. Here’s the hard thing…
i. You will need a conditional formula, because X velocity may be in either direction. When Vx is positive, drag should be negative, and when Vx is negative, drag should be positive.
ii. A conditional formula works like this… “=if (condition, value if condition is met, value if condition is not met)”
iii. So, your formula should be…
f. Y drag is the same as X drag, but relates to Vy, rather than Vx. So, your formula should be…
g. X net force = the X drag from the current row (row 14). There’s no other X force.
h. Y net force = sum of the weight and the Y drag, both from the current row.
i. X acceleration – using Fnet=ma, a=Fxnet/m. Use the current (row 14) net force, but use the mass from above. Mass will need a dollar sign so that you can copy the formula downward later without the mass changing.
j. Y acceleration – same method as X.
k. X position = initial x position, from above.
l. Y position = initial y position, from above.
m. X change in velocity during the time interval. ΔV = at, so you multiply the time increment (B12) by current acceleration (I14). Since B12 comes from the top of the spreadsheet, it needs a dollar sign.
n. Y change in velocity during the time interval. Same method as X
o. X change in position during the time interval. ΔX = V0xt + 0.5aΔt^2. Vox is cell B14. t is the time interval, which is B12. B12 should be entered as B$12. X acceleration is I14.
p. Y change in position is done the same way as X.
5. 2nd row of calculations:
a. Time = previous time + time increment. Increment will need a dollar sign, but previous time won’t. Formula will be “=A14+B$12”
b. X velocity = previous x velocity plus previous change in X velocity. Formula is “=B14+M14”
c. Y velocity. Same method as X velocity
d. X position = previous X position plus previous change in X position. Formula is “=K14+O14”
e. Y position. Same method as X.
6. Cell Q20. Enter the formula, =if((abs(L20))<0.5,A20,""). This will return values for Time (A20) whenever the Y position is very close to zero. These are times that are very near the time when the rocket lands.
7. Now all of the blue cells can be copied downward. Copy them as far as they will go – probably to row 1,000.
8. Time to do the orange cells.
a. Max height is an easy one. Enter “=max(L14:L1000)”
b. Time aloft is the time when the rocket returns to Earth (when y=0m)
c. To find this, we take the average of the values in column Q, because column Q's values only exist when y is close to zero. The formula can be “=average(Q14:1000)”.
d. Terminal velocity occurs when drag = weight. Set drag equal to weight and solve for Velocity. To get you started, here’s drag = weight… 0.5*air density*drag coefficient*cross-sectional area*v^2 = mg. Again, rearrange this to solve for V.
9. If you want a more responsive spreadsheet, for quick guess-and-check, download this as an Excel document.
10. Below is a screen shot of what your outputs should look like if you left the data in the yellow cells. I realize that it is probably unreadable; there is a higher quality screenshot on my website.