Excel Assignment Question
Excel modeling: use Excel to implement and then draw appropriate conclusions from a mathematical model and create a specified type of graphical representation of data, which includes a title and axis labels with units
Description and procedure:
Open a new Excel spreadsheet and complete the following steps:
- In cell A1 type “y-launch (meters)”, in cell A2 type “launch angle (degrees)”, and in cell D1 type “V0= launch velocity (m/sec)”.
- The values of y-launch, launch angle, and launch velocity are the three values in the spreadsheet you can manipulate. Enter the launch height (1) in cell B1 and enter a launch angle less than 90 degrees in cell B2.
- Calculate (in m/sec) the maximum theoretical launch velocity V0 under the 50 Joule energy storage constraint. (This is the same value from the Energy and Trajectories practice assignment.) Use the formula below for your calculation and assume a t-ball has a mass of 0.13 kg.
Ek = 1/2 mVo2
- Enter the maximum theoretical launch velocity in cell E1.
- In cell D2 type “ 𝑉)0x(m/sec) ” and in cell D3 type “ 𝑉0y(m/sec) ”.
- Enter the formulas shown below for the x and y components of the theoretical launch velocity in cells E2 and E3, respectively. (hint: the COS and SIN functions require an angle in radians, therefore change the angle from degrees to radians using the RADIANS function).
V0x = V0cos (𝜃0)
V0y = V0sin (𝜃0)
- In cell G1 type “time(sec)”, in cell H1 type “x(m)”, and in cell I1 type “y(m)”.
- In cell G2 type a 0, and increment each cell in column G by 0.1 until G202 is 20. (hint: cell G3 is “=G2+.1”).
- In cell H2 type a 0, and in cell I2 type the formula “=B1”.
- In cell H3 use an IF statement to calculate the x-distance using the equation below. (hint: x0 is 0, and freeze cell E2 with $E$2). The x-distance should stop when the y-distance is equal to 0, this is where the t-ball hits the ground and does not roll. Copy this formula down to H202.
𝑥(t) = 𝑥0 + V0𝑥t
- In cell I3 use an IF statement to calculate the y-distance using the equation below. (hint: g = 9.81m/s% , and lock cells B1 and E3 in the formula). The y-distance should stop at 0 and not have negative values. Copy this formula down to I202.
y(t)= y0+V0yt - 1/2gt2
- Create an XY scatter graph showing the trajectory of the t-ball until it reaches the ground. On the graph, the x-axis should display the x-distance in meters, and the y-axis should display the y- distance in meters. In order to demonstrate the competency, the graph must have a title, and both of the axes labeled with appropriate units.
If you scroll down the spreadsheet the rows showing the x-distance and y-distance values until they are stabilized (when the y-distance is zero), the x-distance gives the final launch distance for the provided combination of input parameters.
- Complete the following model analysis in a separate document:
- With the launch height set to 1m,
- try 5 different launch angles less than 90 degrees.
- Record each of the five launch angles and resulting launch distance(x-distance)inthe.doc or .pdf document.
- In the spreadsheet, keep the value of Vo the same and set the launch angle to 55 degrees.
- Try 5 different launch heights less than 20m.
- Record each of the five launch heights and resulting launch distance (x-distance) in the .doc or .pdf.
- What set of input parameters (launch height and launch angle) do you recommend to maximize the distance you launch your projectile? Record the answer (in complete sentences) to this question in the .doc or .pdf document.
After the steps above are completed this is what is been requested.
- A single Excel spreadsheet that computes and plots the t-ball trajectory.
- A document (*.doc, *.docx) or .pdf document containing the model analysis information.