Excel and civil engineering.

24 May Excel and civil engineering.

CCE 102 – Homework 4

Due Friday March 2 at 5 pm 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 computation paper

Problems

1. Given: The following equation from The Engineering Toolbox for unit weight of wood  as a function of its dry specific gravity (SG) and its % moisture content (m):

 (in lb/ft3) = 62.4 (SG / (1 + SG 0.009 m))(1 + m / 100)

SG is the specific gravity (unitless). For Alder (SG = 0.72 ) for Red Oak (SG = 0.98) and for Douglas Fir (SG = 0.85). For example, if SG = 0.72 and m = 10 %,  (in lb/ft3) = 46.4 lb/ft3.

Find:

(a) Calculate the unit weight of each species for a moisture content of 33 % by hand on scanned engineering paper.

(b) Using Excel, compute and plot values of the unit weight of the same three species of wood for values of m from 0 to 100%. Show your results as a scatter chart with Moisture content, m (0 to 100 %) on the x axis vs Density of wood,  (lb/ft3) on the y axis. Format your chart using 16 pt Times New Roman font bold for axis numbers and legend and 18 pt Times New Roman font bold all axis titles. Your legend should label each curve with the type of wood. Show both markers and a line connecting the markers for each data series. Use different marker and line colors for each type of wood. Copy your scatter chart and paste it into your scanned engineering paper.

2. Given: The partial record of annual precipitation amounts for Kansas City shown below:

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) Compute the minimum, maximum, average, standard deviation, and variance for annual precipitation amounts in mm. Report your answers to 3 significant figures with appropriate units. Use Excel to perform these calculations and use the snipping tool to paste your results from Excel onto your scanned engineering paper.

(b) Prepare a scatter chart of annual precipitation in mm (left vertical axis) and inches (right vertical axis) vs years (horizontal axis). Format your chart using 16 pt Times New Roman font bold for axis numbers and 18 pt Times New Roman font bold all axis titles. Choose any marker type you like but connect the markers with a solid line. Copy your scatter chart into your scanned engineering paper.

3. Given: Measured compressive strength of concrete as a function of curing time

Curing time (days)	Compressive strength of concrete (MPa)
0.1	0
2	17
4	23
7	26
14	28
21	29
28	30

Find:

(a) Using Excel, prepare a scatter chart with curing time on the horizontal axis and compressive strength on the vertical axis. Show only black markers with no lines and format your chart in the usual way (Times new roman 16 bold axis numbers and Times new roman 18 bold axis titles). Add a logarithmic trend line to your chart and show as a dashed black line. Be sure to add the trend line equation and R2 to your chart (formatted as Times new roman 16 bold). Copy your chart into your scanned engineering paper.

(b) Use linear interpolation and manual calculation to estimate the compressive strength of concrete for 10 and 25 days curing time.

(c) Check your answers in “b” with the Excel interpolation tool that you made for one of your computational exercises. Use the snipping tool to copy your calculator results for both curing times into your scanned engineering paper.

(d) Compute the compressive strength of concrete for curing times of 10, 25, and 100 days using your fitted trend line. Perform these calculations by hand on your scanned engineering paper.

(e) Compute the curing times required to achieve a compressive strength of 5 and 2 ksi using your fitted trend line. Perform these calculations by hand on your scanned engineering paper.

4. Given: Coastal engineers use seawater density to calculate wave forces on structures. The following is a table of seawater density (kg/m3) as a function of temperature (oC) and salinity (%):

Salinity (%)

Temperature (oC)	0	1	2	3	4	5
10	1000.9	1001.7	1003.3	1004.5	1005.0	1007.8
15	999.9	1000.7	1002.3	1003.5	1004.0	1006.8
20	998.9	999.7	1001.3	1002.5	1003.0	1005.8
25	997.9	998.7	1000.3	1001.5	1002.0	1004.8
30	995.9	996.7	997.3	1000.5	1001.0	1002.8

For example, the density of seawater with a temperature of 15 oC and a salinity of 3 % is 1003.5 kg/m3.

Find: Use linear interpolation to compute the density of seawater at a temperature of 17 oC and a salinity of 3 %. Perform your calculations by hand on scanned engineering paper using the usual format. Report your answers to 6 significant figures. Check your Answer using your Excel interpolation tool and paste the result from Excel into your scanned engineering paper.

5. Given: The table of seawater density (kg/m3) as a function of temperature (oC) and salinity (%) from problem 4.

Find: Use linear interpolation to compute the temperature that corresponds to a density of 1003.4 kg/m3 for a salinity of 4 %. Perform your calculations by hand on engineering paper using the usual format. Report your answers to 4 significant figures. Check your answer using your Excel interpolation tool and paste the result from Excel into your scanned engineering paper.

6. Given: The table of seawater density (kg/m3) as a function of temperature (oC) and salinity (%) from problem 4.

Find: Use two-way linear interpolation to compute the density of seawater at a temperature of 22.6oC and at a salinity of 3.2 %. Perform your calculations by hand on engineering paper using the usual format. Report your answers to 6 significant figures. Check your answer using your Excel interpolation tool and paste the result from Excel into your scanned engineering paper.

7. Given: The table of soil data shown below:

Confining pressure (kPa)	Shear strength (GPa)
100	7.8
200	12.2
300	15.6
400	17.8

Find:

(a) Use linear interpolation to compute the shear strength at a confining pressure of 272 kPa. Perform this calculation by hand on scanned engineering paper.

(b) Check your answer in “a” using your Excel interpolation tool. Use the snipping tool to paste the Excel results into your assignment.

(c) Check your answer in “a” using Wolfram alpha. Use the snipping tool paste the results into your assignment.

8. Given: The table of soil data in problem 7.

Find:

(a) Using Excel, prepare a scatter chart with confining pressure on the horizontal axis and shear strength on the vertical axis. Show only black markers with no lines and format your chart in the usual way (Times new roman 16 bold axis numbers and Times new roman 18 bold axis titles). Add a trend line to your chart and show as a dashed black line. Choose the trend line type that gives the largest R2 value. Be sure to add the trend line equation and R2 to your chart (formatted as Times new roman 16 bold). Copy your chart into your scanned engineering paper.

(b) Use your trend line equation to compute shear strength at a confining pressure of 272 kPa. Compute your results by hand on your assignment.

(c) Use your trend line equation to compute confining pressure at a shear strength of 16 GPa.

Grading Rubric for Homework Assignment 4

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

· Use of straight edge to underline Given, Find, Solution, and Answer and between problems = + 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

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

· Not attempted = 0 points

· Correct approach = + 3 points

· Correct approach and correct answers = + 6 points