This is a Civil Engineering class that require us to build what attached below in an Excel attached below!!!! please build these in excel
cce_102_optional_computational_exercise_9___2017.docx
Unformatted Attachment Preview
Optional Computational Exercise 9
Due Sunday March 19 at 5pm
Submit via TEACH
1. Create a new worksheet called “Making charts”. Prepare a scatter chart for the following data:
Time, t (s)
1
5
16
37
110
300
560
Flowrate, Q (cfs)
50
45
30
28
15
5
2
Pressure, P (kPa)
150
180
200
220
240
260
295
Put time on the horizontal axis, flowrate on the left vertical axis, and pressure on the right vertical axis. Format
the horizontal axis as logarithmic. Include axis titles identical to the column headings above. Format your
data series for Q with black-filled circular markers and solid black lines. Format your data series for P with
red-filled square markers with solid red lines. Format axis numbers as Times New Roman bold 16 pt and axis
titles as Times New Roman bold 18 pt font. Add exponential trend lines, equations, and R2 values for Q and
P. Format the trend line for Q as a black dashed line. Format the trend line for P as a dashed red line. Format
trend line equations and R2 values as Times New Roman bold 12pt font; use black font for Q and red font for
P.
2. Create a new worksheet called “Solving equations”. Use Excel and solver to find a positive and negative
value of x that makes y = 0 in the following equation:
1
3
= + 2 −
3
3. Create a new worksheet called “Interpolation”. Given the following data:
Temperature (oC)
2
6
15
21
28
Drag coefficient (dimensionless)
1.60
1.58
1.53
1.45
1.12
Use linear interpolation to complete the following table (show all your work on the Excel worksheet):
Temperature (oC)
Drag coefficient (dimensionless)
1.55
1.37
4. Create a new worksheet called “Numerical integration”. Use numerical integration with the trapezoidal
rule to compute the total flowrate, Q at a location in the Columbia River. The river is 1200 ft wide and 24 ft
deep. Report your answer in cfs and acre-ft/year.
Distance from East Bank of river
Average velocity
(ft)
(ft/s)
0
9.5
50
8.7
240
8.5
260
10.2
415
12.8
550
18.0
625
16.7
717
15.2
985
12.1
1200
10.0
5. Create a new worksheet called “Working with data”. Go to this website and download the 2005 data for
“Corvallis Muni”.
http://rredc.nrel.gov/solar/old_data/nsrdb/1991-2010/hourly/siteonthefly.cgi?id=726945
a. Delete all columns except for these three:
YYYY-MM-DD
HH:MM (LST) ETR (Wh/m^2)
The first column contains the year, month, and day. The second column contains the hour of day. The third
column contains the solar radiation data (the units for ETR should be read as “Watt hours per meter squared’).
b. Prepare a vertical bar chart that shows the average ETR for each hour of the day for all days in January
Your bar chart will have hour of day on the horizontal axis; you will have 24 vertical bars, one for each hour
of the day. The height of the first bar (vertical axis) should show the average ETR for hour 0 for all days in
January, etc.). Format your chart in the usual way.
c. Prepare a vertical bar chart that shows the average ETR for each hour of the day for all days in July. Your
bar chart will have hour of day on the horizontal axis; you will have 24 vertical bars, one for each hour of
the day. The height of the first bar (vertical axis) should show the average ETR for hour 0 for all days in
July, etc.). Format your chart in the usual way.
d. Find the ten days in the entire record with the ten largest ETR values at 12:00 (noon).
Rubric:
Problem 1 (4 points): 3 points will be awarded for plotting the graph as guided. 1 point will be awarded for
correct formatting. -1.5 point will be awarded if graph is incomplete. Zero grade will be awarded if not
attempted.
Problem 2 (4 points): Use solver to summarize the exact values of ‘x’ for which ‘y=0’. Cells that are used to run
solver must contain relevant formulae. Full credit will be given for correct output. -2 points will be awarded for
incomplete work. Zero grade will be awarded if not attempted.
Problem 3 (4 points): Build the linear interpolation calculator. Full credit will be given for correct values of
temperature. -2 points will be awarded for incomplete work. Zero grade will be awarded if not attempted.
Problem 4 (4 points): Compute the total flow rate as guided. Full credit will be given for correct value of flow
rate. -2 points will be awarded for incomplete work. Zero grade will be awarded if not attempted.
Problem 5 (4 points): 1.5 point will be awarded for the right plotting of each vertical bar chart. 1 point will be
awarded for summarizing the correct data set of 10 days with largest ETR values.
…
Purchase answer to see full
attachment
Recent Comments