CCE 170 - Exam #1 KEY

Fall Semester 2021

Note: You may use the Excel Help Utility on this page of the exam.

20. (excel) When a load is placed on a saturated soil layer, the load is initially carried by the pore water but is gradually transfered from the pore water to the soil particles as the excess water is expelled from the soil matrix (kind of like squeezing water from a sponge). The settlement (consolidation) of the soil does not fully take place until this process is complete. Thus, some soils consolidate quite rapidly and some do not. The time required for consolidation to occur can be modeled with the following set of equations:

      (Eq. 1)

where:

t = time (in days) required for consolidation to a given percentage (U)

H = maximum drainage distance

Cv = coefficient of consolition (depends on soil type)

Tv = time factor.

The time factor (Tv) is a function of the degree of consolidation (U), which is simply the percentage of consolidation completed (25%, 50%, etc.). The time factor can be estimated from the degree of consolidation using the following empirical relationship.

      (Eq. 2)

      (Eq. 3)

Note that for Equation 2, U should be entered as a decimal (0.55), but for Equation 3, U should be entered as a percentage (75.0).

The following spreadsheet has been designed to analyze the time rate of consolidation for selected conditions:

Click here to download a copy of this spreadsheet. Then complete the following exercise.

Click here to download a copy of the solution.

Note: Cells B5 and B6 have been named. Do not name any of the other cells.

(a) (2 pts) Use the Data Validation tool in the Data ribbon to restrict the choices for cell B4 to the soil types listed in D5:D10. In other words, the user will select one of the soil names from a list in cell B4.

(b) (3 pts) Enter a formula in cell B5 that uses the VLOOKUP function to select the appropriate value of Cv from the table on the right depending on what soil type the user has selected in cell B4. In other words, your spreadsheet should automatically display the appropriate Cv value in cell B5 whenever the user selects a new soil type in cell B4.

(c) (3 pts) Enter a formula for cells B13:B33 that computes the appropriate value of Tv based on the degree of consolidation values shown in column A using Equations 2 & 3. You will need to use an IF statement to get the correct set of values. Please note the discussion above on how U is entered to the equations. The values in column A are actually decimal values (the look the way they do because of the Excel percentage formatting). You should pass this value directly to Equation 2 but multiply this value by 100 when you pass it to Equation 3. Use the LOG function to compute the log-base 10.

(d) (2 pts) Enter a formula for cells C13:C33 that computes the time required for consolidation based on the time factors in column B using Equation 1.

(e) (3 pts) Create a chart that displays time (vertical axis) vs. degree of consolidation (horiz. axis) using the XY Scatter type of plot. Change the scale for the horizontal axis so that it stops exactly at 100%. Remove the legend and add some titles for the axes.

(Upload instructions and links went here)