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 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 elapsed since the starting time.  To find this, we take the average of the values in column Q -- and subtract the starting time.  Column Q only produces values of total time when the rocket’s height is close to zero (i.e. at the end of the flight).  The formula can be “=average(Q14:1000)-B11”. 

c.       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.