- Nursing Writers For Hire
- support@nursingwriters.org

**HW11: Using Excel – Airplane Cargo Drop**

**This assignment is due at the beginning of next class. Excel is available in many computer labs across campus.**

**Use the following “videos” to help you complete the HW**

**Excel: Entering a formula – ****http://www.wisc-online.com/Objects/ViewObject.aspx?ID=ENG5403**

**Excel: Locking Cell References – ****http://www.wisc-online.com/Objects/ViewObject.aspx?ID=ENG5503**

**Excel: Spreadsheet Hints – ****http://www.wisc-online.com/Objects/ViewObject.aspx?ID=ENG6403**

**Video: Create an Excel table – ****http://office.microsoft.com/en-us/excel-help/video-create-an-excel-**

**table-VA101810216.aspx?CTT=1**

**Video: Create a chart in Excel – ****http://office.microsoft.com/en-us/excel-help/video-create-a-chart-in-****excel-VA102571932.aspx?pid=CH010369001&CTT=4&client=1**

**Solve this problem using Excel (suggestion: print this page and cross off the directions as you complete them).**

Supplies are dropped from an airplane to land at a certain point. The velocity of the package at release from the airplane is the speed of the airplane, **v****0x=200ft/s.** The acceleration of the package due to gravity is **a****y** = -32.2 ft/s^{2}. The displacement in the y direction can be found using the following equation: y – y0 = v0yt + ½ayt^{2}. The final position of the package (y) is on the ground, and y0 represents the height of the plane when the package is dropped. The initial velocity in the y direction (v0y) is zero. Therefore the equation becomes: y = y0 + ½ayt^{2}.

The distance the package travels in the x direction can be found from the equation: x – x0 = v0xt + ½axt^{2}. The final position of the package (x) is measured from the initial position (x0). If x0 is taken as zero, and if the plane is traveling at a constant speed (ax is zero), then the equation becomes: x = v0xt.

**Step-by-step Excel Instructions:**

- Create a table for the initial data:
- In cell B2 put the label “Plane height:”
- In cell C2 put the initial height of the plane in feet (ft): 300
- In cell B3 put the label “Acceleration (g):”
- In cell C3 put the acceleration of gravity in ft/s^2: -32.2
- In cell B4 put the label “Velocity:”
- In cell C4 put the velocity in ft/s: 200
- In cells D2, D3, and D4, place the appropriate units
- Create a table for the data and calculations: In cells B6, C6, and D6 respectively, put the labels “t”, “x”, and “y”.
- In cell B7, enter the initial time: 0
- Increment the time in ½-second intervals in column B, from B8 to B16.
- Center all of the labels and data in columns B, C, and D.
- Enter the formulas to calculate 1) x in cell C7 and 2) y in cell D7.
- Copy the formula from C7 to C8 through C16 and from D7 to D8 through D16.
- Format the numbers in the y column to one decimal point.

- Create separate graphs of 1) “x vs. t” and of 2) “y vs. t”. Using the “x vs. t” graph as an example:
- Select the x data (C7:C16), and then select the “
**line with markers**” plot type. - To add the time on the horizontal axis, right-click on the chart and chose “
**Select Data**…”. In the “**Select****Data Source**” dialog box, chose “**edit**” for the**Horizontal (Category)**. In the next dialog box, simply select (drag the mouse over) the time data (B7:B16).

- Add
**major vertical gridlines**,**chart titles**, and**axis labels**using the buttons under the “**Layout**” tab.

Include the correct units in the axis titles and rotate the vertical axis title to the horizontal.

- Remove the “series” labels from these two charts (Layout/legend).
- Repeat these steps for the “y vs. t” graph.
- Create a graph of “x and y vs. t”
- highlighting the x and y data before choosing the chart type or by copying and modifying one of the existing charts using the “
**Select Data…**” command. - Change the series labels to read “x” and “y” or “horizontal distance” and “vertical distance”.

- Add the appropriate chart title and axis labels.

- Use trial and error:
- Change the values in the “t” column to determine, by trial and error, the time at which the package hits the ground.
- Using the “Goal Seek” tool:
- Goal Seek can be found under the “
**Data**” tab, “**What-If Analysis**” button. - To use goal seek, open the Goal Seek dialog box:
**Set cell: D16**,**To Value: 0**,**By changing cell: B16**.

- Record your answer in B18, then return the spreadsheet to its initial configuration.

**What to Submit:**

Write a memorandum to the instructor that __explains the data tables and the graphs__. **DO NOT** refer to “cell B18” or any other cell (When looking at the data table in a Word document, how can you locate cell D9?).

- Copy and paste the graphs (3) and the data tables into a Word document. It is recommended that you
**paste the****data tables and each graph separately as a Picture (Windows or Enhanced Metafile). Do not**paste the data or the graphs as a Graphic Object, or as an Excel Object (use “**Paste Special**” found under “**Paste”**on the Ribbon).

__Use appropriate titles or captions.__

- Explain each graph and data table.
__In the text, refer to each graph and table using appropriate labels.__ - Explain the number (time) obtained by using the “Goal Seek” function.
- Carefully arrange the text, data tables, and charts to print correctly (use
**Print Preview**). - Insert page numbers and a header if you have multiple pages.