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…

“=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 F_{net}=ma,
a=F_{xnet}/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 = V_{0x}t +
0.5aΔt^2. V_{ox} 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.
2^{nd} 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.