CCE 102

CCE 102 – Homework 6

Due Monday March 4 at midnight in Mailboxes on first floor of Kearney Hall

No late homework accepted (0 points for late assignments).

All problems for this assignment must be completed on scanned engineering paper.

1. Given: The long-term record of daily precipitation for Sydney Australia contained in the file ”Copy of Sydney_daily_precip” posted on Canvas.

Find: Use Excel for the following

a. Sort the annual precipitation amount in inches for the entire record from largest to smallest. Prepare a table in Excel showing the year and precipitation amount in inches for the ten years with the largest annual precipitation amount. Paste the table into your solution.

b. Sort the daily precipitation amount in inches for the entire record from largest to smallest. Prepare a table in Excel showing the year, month, day and precipitation amount in inches for the twenty days with the largest daily precipitation amount. Paste the table into your solution.

c. How many days in the entire record had less than 0.15 inch of precipitation ?

d. How many days in the entire record has more than 0.98 inches of precipitation ?

e. How many times in the entire record had more than 30 consecutive days with no precipitation ?

For c, d, and e copy your Excel formula and results into your solution using the snipping tool.

2. Given: The long-term record of daily precipitation for Sydney Australia contained in the file ”Copy of Sydney_daily_precip” posted on Canvas.

Find:

a. Compute the total rainfall in mm for the month of June for each year in the record separately. Then compute and plot a histogram of that data. Use 10-20 bins of equal size. Format your chart in the usual way and copy it into your solution.

3. Given: The elevation data contained in the file ”Topographic data for CE6” posted on Canvas.

Find:

a. Convert the elevation data to meters. Then compute the minimum, maximum, average, and standard deviation in meters for all elevations in the file.

b. Compute and plot a histogram for elevation in meters. Use 10-20 bins of equal size. Compute frequency as a percent for the left vertical axis and frequency as a fraction for the right vertical axis. Format the chart in the usual way and paste it into your solution.

c. Compute the percentage of all elevations that were greater than 330 m.

For a and c paste your Excel formula and results into your solution using the snipping tool.

4. Given: The historical cost of tuition at private nonprofit 4 year universities shown below:

Find:

a. Plot the data as a scatter chart with academic year on the horizontal axis and cost on the vertical axis. Show the data as markers only. Fit a trend line to the data that gives the largest R2. Add the trend line equation and R2 to your chart, format the chart in the usual way, and paste it into your solution.

b. Use the trend line equation to manually predict the cost of tuition for the 2025-2026 academic year. Use the trend line forecast tool to predict the cost in the same year. Prepare a second chart that shows the forecasted cost and paste it into your solution.

5. Given: A record of annual precipitation for a location in Iowa:

Year	Precipitation (in)
1953	5.32
1954	18.94
1955	5.96
1957	4.76
1958	9.61
1959	13.24
1960	10.09
1961	9.03
1962	6.53
1963	4.76
1964	18.50
1965	16.59
1966	8.61
1967	14.94
1968	9.57
1969	12.02
1970	14.46
1971	16.69
1972	10.62
1973	5.72
1974	12.47
1975	15.21
1976	11.67
1977	3.20
1978	8.66
1979	3.83
1980	7.21
1981	2.63
1982	13.98
1983	13.52
1984	6.60
1985	0.69
1986	11.19
1987	10.38
1988	6.57
1989	8.20
1990	16.09
1991	6.76
1992	9.34

Find:

a. Convert the precipitation amounts to mm. Then compute the Minimum, Maximum, Average, and Standard deviation of annual precipitation amount in mm.

b Prepare a histogram of annual precipitation amounts with bin sizes of 5 mm (i.e. 0 to 5 mm, 5 to 10 mm). Put precipitation amount on the x axis and frequency on the y axes. Label one y axis as frequency in percent and label the secondary y axis as frequency as a fraction (0 to 1). Format you’re your histogram in the usual way and paste it into your solution

6. Given: The table of civil engineering salaries vs years of experience for 2018:

Years of experience	Average annual salary
0	$57,440
1	$58,459
2	$64,658
4	$75,849
10	$86,750

Find:

a. Plot the data as a scatter chart with years of experience on the horizontal axis and average annual salry on the vertical axis. Show the data as markers only. Fit a trend line to the data that gives the largest R2. Add the trend line equation and R2 to your chart, format the chart in the usual way, and paste it into your solution.

b. Use the trend line equation to manually predict the number of years of experience required to obtain an average annual salary of $ 100,000.

7. Given: The elevation data contained in the file ”Topographic data for CE6” posted on Canvas. A 40 ft wide horizontal (constant elevation = 1130 ft) road will be built along the line northing = 3960 ft.

Find: The total volume of cuts (yd3) and the total volume of fills (yd3) required to build the road. Copy the portion of the spreadsheet with your calculations and results into your solution.

8. Given: The elevation data contained in the file ”Topographic data for CE6” posted on Canvas. A 40 ft wide horizontal (constant elevation = 1130 ft) road will be built along the line easting = 5544 ft.

Find: The total volume of cuts (yd3) and the total volume of fills (yd3) required to build the road. Copy the portion of the spreadsheet with your calculations and results into your solution.

Grading Rubric for Homework Assignment 6

Maximum points possible for this assignment = 58 points

Late work will receive a grade of 0 for the assignment.

This assignment must be completed on scanned engineering paper ! (copy the problem statements into the Word document that contains the scanned engineering paper, solve hand calculations on printed pages, copy Excel and other output into the Word document and print before submission)

10 points per assignment for a total of 10 points graded as follows:

· Required information in boxes on top of each page of assignment = + 2 points

· Underline Given, Find, Solution, and Answer, start each problem on new page= + 2 point

· Showing units for all numerical quantities = + 2 points

· Performing calculations using the method presented in lecture = + 2 points

· General neatness in appearance, clarity in writing = + 2 point

8 points per problem for a total of 48 points graded as follows:

· Not attempted = 0 points

· Manual or Excel calculations performed correctly = + 4 points

· Excel charts formatted correctly = + 4 points