EGN 100 Spreadsheets Tutorial 3 .maine.edu



EGN 100 Spreadsheets Tutorial 3

The physics and mathematics of the formulas in the following section will be discussed in class.

Application

Trajectory Spreadsheet: A baseball is hit by a bat and leaves the bat at a certain initial velocity and a certain initial angle with respect to the ground. Construct a spreadsheet to investigate how the trajectory changes as the initial velocity and/or the initial angle are changed.

The formula for the x component, i.e. the horizontal distance the ball travels, is

x = (v0*cos θ0)*t, where v0 equals the initial velocity, θ0 is the initial angle with respect to the horizontal, and t is time. The formula for the y component, or height of the ball above the ground, is given by

y = y0 + (v0*sin θ0)*t – ½ g*t2

where y0 is the initial height and g is the acceleration of gravity and equals 9.81 m/s/s.

1. Open a new spreadsheet. Type Baseball Problems in A1

2. Set up a spreadsheet as shown. Notice that column headings are centered. You don’t need to type the formulas shown on row 6, but you will want to reference them in order to write the code for them in the spreadsheet.

|  |A |B |C |D | |

|1 |Baseball Problems | | | |

|2 | | | | | |

|3 |Initial Velocity, m/s |35 | | | |

|4 |Initial Angle, deg |25 | | | |

|5 |Initial Height, m |1 | | | |

|6 |x = (vo*cos θo)*t y = yo + (vo*sin θo)*t – ½ g*t^2 |

|7 |Time, s |x, m |y, m | | |

|8 | | | | | |

|9 | | | | | |

|10 | | | | | |

3. What is the code for x = (v0*cos θ0)*t? Remember that the Location of a variable is used rather than the variable itself, if it is expected that the variable will change. Note that we want to calculate x and y for several time values, using fixed values of v0, θ0, and y0.

Excel works with angles in the form of radians, a form of angular measure. There are 2π (approximately 6.28) radians in a complete circle, corresponding to 360 degrees. Excel converts degrees to radians by the word radians just before the angle in degrees. See below.

Here is the code for cell B8:

=b$3*cos(radians(b$4))*a8

Look at this code and see how it relates to the formula for x above.

What is the code for y = y0 + (v0*sin θ0)*t – ½ g*t2 ?

In C8 type =b$5+b$3*sin(radians(b$4))*a8-0.5*9.81*a8^2

Look at this code and see how it relates to the formula for y.

Enter 0 in A8 and =a8+.2 in A9 Copy this formula into the A column cells down to A33 by selecting A9 and placing the cursor arrow in the little black square at the bottom right of the selected cell black outline, holding down the left mouse key and dragging down to A33. The maximum time should be 5 seconds.

Copy the formula in B8 into all cells down to B33.

Copy the formula in C9 into all cells down to C33.

For the x and y data, restrict the number of places after the decimal point to one.

4. Plot x(t) versus time for v0=35, θ0=25

Plot x(t) for v0=45, θ0=25

Notice that changing the value of v0 changes the slope and the distance. This might not be evident unless you examine the chart carefully for each value of v0.

How does x(t) change with v0?

5. Plot y(t) for v0=35, θ0=25

Plot y(t) for v0=45, θ0=25

How does y(t) change with v0?

6. Set the initial velocity to 45 m/s, initial angle to 25 degrees, and initial height to 1.2 m. Select all three data columns: t, x, and y. Plot x(t) and y(t) on the same chart, with t as the horizontal axis. In the second plot window, change series titles from Series 1 to x distance and Series 2 to y distance. Give the y(t) plot more prominence by using a secondary axis for y(t) as follows. When the plot is completed, right click on the y plot curve. Select Format Data Series, Axis, Secondary Axis, OK.

Change the scale on the y axis so that y doesn’t go negative as follows: Right click the y axis, Format Axis, Scale, Minimum 0, OK.

Add vertical grid lines: Right click chart area, Chart Options, Gridlines, Value x axis, Major grid lines.

Change the initial velocity or the initial angle. How do the x distance and the height change?

7. Set the initial velocity equal to 25 m/s and the initial angle to 30 degrees. What is the x distance at which the ball hits the ground (range)? Increase the initial angle in increments of 5 degrees to a maximum of 60 degrees and record the range for each initial angle. At what angle is the range the greatest? What is the maximum range?

The relationship of y to x is: y = y0 + (tan θ0)∗x – (g/(2*(v0*cos θ0)2))*x2. If the initial velocity value is in b2, the initial angle value is in b3, and the initial height is in b4, the code for y is as follows:

=$b$4+tan(radians($b$3))*a6-4.905*a6^2/($b$2*cos(radians($b$3)))^2

8. At the bottom left of the screen are tabs for Sheet 1, Sheet 2 and Sheet 3. (You can add another sheet by right clicking on one of the tabs and selecting Insert.) Select Sheet 2 and construct a new table in which x varies from 0 to 80 meters in increments of 5 meters and y is calculated using the formula above. Plot with x on the horizontal axis and y on the vertical axis. After entering the titles, select gridlines and set X major gridlines. Then select Legend and de-select Show Legend since there is only one plot and the rest of the information is sufficient to describe it.

On the completed chart, set the y axis so the minimum is zero. Right click on a vertical gridline and set the x axis major unit to 10.

Start with an initial height of 1.2 m, an initial velocity of 25 m/s and an initial angle of 30 degrees. Vary the initial angle in increments of 5 degrees to a maximum of 60 degrees. Record the range and maximum height for each initial angle. At what initial angle is the range the greatest? What is the maximum range? How does the maximum height change? What is the maximum height and at what angle?

For an initial height of zero, the range is given by r = (v02/g)*sin(2*θ0).

9. Construct a table in which θ0 varies from 0 to 90 degrees and r is given by the above formula, where the initial height is assumed to be zero. Plot in 5 degree increments with initial angle on the horizontal axis and range on the vertical axis. At what initial angle is the range the greatest? What is the maximum range? How do these results compare to those for parts 5 and 6 above? How does the range change if the initial angle is constant and the initial velocity is increased?

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download