excel & word
mwesigward
hw111.doc
All of the assigned problems shall be worked in the same Excel file, just on separate sheets within the file.
Problem 1 (4 points)
Create and Excel spreadsheet. Label Sheet 1 “Problem 1”. Do the following computation on sheet
“Problem 1.”
You are designing a right circular cylinder container for storm water treatment and you want to make it as economical as possible. The ideal volume is 15 ft3 (for treatment purposes), so you are tasked with finding the most efficient combination of cylinder height and radius to achieve that volume.
To get you started in the right spot, you will investigate cylinder heights between 1.75' and 3.25' tall, in 0.1' increments.
The determination of the most efficient geometry for the cylinder will be based on the combination of height (h) and radius (r) that results in the highest ratio of volume (V) compared to surface area (A).
To accomplish this analysis, show the following analysis in your Excel solution:
1. Choose a cell to contain the volume and enter in 15 ft3.
2. Create a row that contains all of the available cylinder heights, (h), from 1.75' to 3.25' (in 0.1' increments).
3. For each cylinder height:
a. Calculation the radius (r) required for the given volume.
i. Hint: Use the volume equation shown in the figure and rearrange it to solve for r.
b. Calculate the surface area (A) of the cylinder.
i. Hint: The equation for A is provided in the figure
c. Calculate the ratio of volume to area (V/A)
4. Identify the height and radius of the most efficient cylinder.
5. In the worksheet include a plot of radius versus the ratio V/A as a scatter plot. a. Hint: Insert/Charts/Scatter
Summarize your results (most efficient height and radius combination) in your Word memorandum including cutting and pasting the chart. In the Word document add a figure caption using “Insert Caption”. Make sure you reference the figure caption in your text.
Problem 2 (1 points, 0.1 points per answer)
In the same Excel spreadsheet create a new Sheet and label it “Problem 2”.
Convert the values below to the units. You must use the CONVERT() function in Excel. Make sure that you clearly identify the input value/units and the output value/units. Summarize your results in your Word memorandum.
a) 1 cm to m b) 20 m3 to ft3
c) 1900 N to lbf
d) 1.87 rad to decimal degrees e) 200 kPa to psi
f) 25 m/s to mph
g) 9.81 m/s2 to ft/s2
h) 1000 kg/m3 to lbm/ft3
i) 54 kip to lbf j) 1 acre to ft2
Problem 3 (1 points, 0.2 points per answer)
In the same Excel spreadsheet create a new Sheet and label it “Problem 3”.
Create a spreadsheet that calculates the volume of a rectangular prism based on its height, width, and length. Use the spreadsheet to calculate the volume (in m3) for the 5 prisms described in Table 1. Remember to use appropriate significant figures.
Table 1
|
Prism |
Height |
Width |
Length |
|
|
mm |
mm |
mm |
|
A |
1500 |
115 |
2500 |
|
B |
1000 |
500 |
2000 |
|
C |
250 |
200 |
4000 |
|
D |
2000 |
1000 |
50.0 |
|
E |
400 |
50 |
400 |
Follow formatting instructions and include final answers in your Word memorandum
Problem 4 (3 points, 0.5 points per answer)
In the same Excel spreadsheet create a new Sheet and label it “Problem 4”.
For the use by hydraulic engineers and other design professional, the City of Portland publishes rainfall data from rain gages throughout the city at: https://or.water.usgs.gov/non-usgs/bes/
Go to the City’s website and download the daily rainfall data at the following locations for October 1,
2017 to September 30, 2018:
• Children’s Museum Rain Gage 4015 SW Canyon Rd.
• Sauvie Island School Rain Gage 14445 NW Charlton Rd.
To download the data complete the following steps:
a) Click on the “Data table” for the first station.
b) In your Excel “Problem 4” cut and paste the data directly into Excel for 10/1/2107 to 09/30/2018 for your first station. If you copy the entire data table it will similar as shown below.
c) Delete the rows until the first date is 30-Sep-2018. Also delete rows so that the last row is 1-Oct-
2017. (Deleting the extra rows is not absolutely necessary, but it will make your life easier).
d) You will notice if you click on a cell that all the data (daily and hourly) is all in one cell. Select the A column, then DATA/TEXT TO COLUMNS. Choose FIXED WIDTH, etc. (Excel
automatically recognizes the date field. Use the following resource to learn about the function:
a. Office Online Help: https://support.office.microsoft.com/en- HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US us/article/Spli HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US t- HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US text HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US into HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US -
different HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US cells HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US 30b14928 HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US 5550 HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US 41f5 HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US 97ca HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US -
7a3e9c363ed7?CorrelationId=5d7ee3f0 HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US 5211 HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US 4852 HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US 9ccf HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US 21a40d5f51f1&ui=en HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US - HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US US&rs=en- HYPERLINK https://support.office.microsoft.com/en-us/article/Split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7?CorrelationId=5d7ee3f0-5211-4852-9ccf-21a40d5f51f1&ui=en-US&rs=en-US&ad=US US&ad=US
e) Since we are only interested in the daily amounts rather than hourly, you can delete all the other columns other than A and B.
f) Repeat steps a-e for the second station accept the daily total data from the second station should be in column C when you are finished. Provide descriptive column headers to distinguish
between the data from each station.
g) Unless you have already done so, delete the text describing the rain gage location and qualifying information.
With the rain gage data now in Excel form, do the following:
a) Create a stacked column chart with the following:
a. Data series (Make sure the data goes from 10/1/2107 to 09/30/2018 – not vice versa – you can change the direction of the axis in axis properties or reorder your data. How? Select all the data in your sheet. On the HOME tab choose FORMAT as a TABLE. Then on the first row of Column 1 sort by date from smallest to oldest and the table will be reformatted.)
i. 5 day running average rainfall at Children’s Museum Gage vs. Time ii. 5 day running average rainfall at Sauvie Island School Gage vs. Time
b. Add a Chart Title and add Axis Labels b) Create a line chart with the following
a. Data series
i. Cumulative rainfall at Children’s Museum Gage vs. Time ii. Cumulative rainfall at Sauvie Island School Gage vs. Time
b. Add a Chart Title, Axis Labels
c) Copy and paste the two charts into your Word memorandum. Add figure captions and reference them in your memo. Comment on which gage gets more rain in Portland.
Name
Grading Rubric
|
Criteria |
Points |
Sub-Total |
|
Problem 1 |
|
/3 |
|
Graph is correct |
/1 |
|
|
Correct Answer for V/A |
/1 |
- |
|
Relevant reflection memo |
/1 |
- |
|
Problem 2 |
|
/2 |
|
0.2 for each correct answer |
/1 |
|
|
Problem 3 |
|
/1 |
|
0.2 for each correct answer |
/1 |
|
|
Problem 4 |
|
/4 |
|
Correct data uploaded |
/1 |
|
|
Correct graph for part A |
/1 |
|
|
Correct graph for part B |
/1 |
- |
|
Relevant reflection memo |
/1 |
- |
Total /10
�
