Physics 200 (Stapleton) Name
____________________
Directions – Trajectory with Drag Spreadsheet
Before you begin, copy the template. Leave the values in the yellow cells.
Aqua cells are meant to contain formulas that will be copied downward. Green, fuchsia, and orange cells should have formulas, but they are not meant to be copied dowward.
1.
To convert the initial angle to radians, apply the radians function to the
initial angle in degrees. The
spreadsheet function will be “=radians(B2)”
2. Initial X velocity -- use the initial angle in RADIANS, and the initial speed to find this. Make sure you're using the correct trig identity. The formula is "=cos(B3)*B4"
3. Initial Y velocity -- same as X velocity, but use a different trig identity. The formula is "=sin(B3)*B4"
4.
First row of calculations (row 14). ***Important note: any cell reference to a cell above row 13 can
(and usually should) 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 a downward force.
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…
“=if(B14>0,-0.5*B$10*B$9*B$8*B14^2,0.5*B$10*B$9*B$8*B14^2)”
f.
Y drag is the same as X drag, but relates to Vy, rather than Vx. So, your formula should be…
“=if(C14>0,-0.5*B$10*B$9*B$8*C14^2,0.5*B$10*B$9*B$8*C14^2)”
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. a= ΔV/Δt, so ΔV = at.
Multiply
the time increment (B12)
by current X 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 displacement 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 displacement during the time interval" is done the same way as X.
5.
Row 15 calculations
(aqua cells, using data from the green cells):
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.
9.
Time to do the orange cells.
a. Max height is an easy one. Y position (height) is in column L, so enter “=max(L14:L1000)”.
b.
Time aloft is the time when the rocket returns to
Earth (when y=0m). 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.
10. If you want a more responsive spreadsheet, for quick guess-and-check, download this as an Excel document.
11. 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.