Physics 200 (Stapleton) Name ____________________

Directions – Trajectory with Drag Spreadsheet

Important Notes:

• Before you begin, copy the template. Leave the values in the yellow cells.

• **Make sure that your spreadsheet is "interactive," meaning that, if you enter a new number into any yellow cell, all of the other numbers should automatically adjust.  Yellow cells should not have formulas.

• 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.  Formula is "=B11"

b. X velocity = initial x velocity.  Formula is "=B5"

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 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. 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 = V_0xt + 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 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.   The best way to do this is to first copy the blue cells from row 14 into row 15.  Then you can copy all of row 15 down to row 20. 

8.    At this point you should confirm that your formulas are giving you the correct answers.  If they are, you can copy the entire row down to row 1000.   If you have left the initial yellow inputs, you should see some numbers in column Q at about row 772.  These are the times when the rocket has returned to within 0.5m from the ground.

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.