using excel for soil testing
TRANSCRIPT
Using Excel for Soil Testing
Excel is a spreadsheet program by Microsoft which can be used by engineers to do
many design tasks and computations. We will look at using Excel to do soil testing
calculations and graphs. Here are the test covered:
Liquid Limit
Plastic Limit
Sieve Analysis
Specific Gravity
Proctor
Liquid Limit
The liquid limit is one of three tests that make up the Atterberg limits (ASTM
D4318). The other two tests are the plastic limit and the shrinkage limit. In all
three tests, you determine the moisture content at which the soil changes from one
state to another.
You will need several pieces of Equipment:
1. Casagrande liquid limit devise
2. Grooving tool
3. Moisture cans
4. Porcelain evaporating dishes
5. Spatula
6. Oven
7. Balance, sensitive to 0.01 g
8. Plastic squeeze bottle filled with water
Procedure
1. Determine the weight of each moisture can (W1).
2. Put about 300-350 g of air-dry soil, passing a No. 40 sieve, into an
evaporating dish. Add water and mix to form a slightly moist paste.
3. Place a portion of the paste into the brass cup of the liquid limit devise. Using
the spatula, smooth the surface of the paste in the cup.
4. Using the grooving tool, cut a groove down the center of the soil paste in the
cup.
5. Turn the crank on the liquid limit devise at a rate of about 2 revolutions per
second. Start counting the number of blows. When the two sides of the
groove in the bottom come together for a distance of ½ inch. Stop counting
the number of blows (N). Collect a sample of the soil in the moisture can
determine the weight of the can and wet soil (W2).
6. Add more water to the soil and repeat steps 3-5 several times.
7. Place the samples in an oven to dry. After drying, determine the weight of
the cans with the dry soil (W3).
The idea is to get some samples of soil less than 25 blows and some above 25
blows. The liquid limit is the moisture content at 25 blows.
Calculations
Moisture content
Flow index, FI, the slope of the line
The liquid limit, (LL), by the U.S Army Waterways Experiment station
approximation
We have collected the following data and entered it into Excel:
The values in column E were calculated from the moisture content equation.
In Excel it looks something like this:
= (C12-D12) / (D12-B12) * 100
Explain
In Excel, we have several arithmetic operators:
1. Parentheses, () 2. Exponentiation, ^
3. Multiplication, * and Division, /
4. Addition, + and Subtraction, -
They are in the order of precedence. Excel will do parentheses first and then the
others next. If you have an equation that has two of the same order, then Excel
does them left to right. Parentheses can be used to change the order.
In Excel it is calculated like this:
= (E13-E14) / (LOG10(F14) - LOG10(F13))
Explain
Excel has about 341 built-in worksheet functions. LOG(number), returns the
base-10 logarithm of a number. The number needs to be a positive real number.
US Army approximate liquid limit 35.7
In Excel it is calculated like this:
= E13 * (F13/25)^0.121
Flow Index FI 18.3
You also make an X-Y- scatter graph with a logarithmic X-axis.
You can determine the liquid limit from the graph by finding the moisture content at
25 blows. I straight line interpolation of two values close to 25 blows yields a liquid
limit of 35.3, which is probably more accurate than the other two methods.
Plastic Limit
You will need the following Equipment:
1. Porcelain evaporation dish
2. Spatula
3. Plastic squeeze bottle of water
4. Moisture cans
5. Ground glass plate
6. Balance sensitive to 0.01 g
Procedure
1. Put about 20-50 grams of air-dry soil passing a No. 40 sieve into evaporation
dish
2. Add water and mix well
3. Weigh the moisture cans (W1)
4. Form the soil sample into and ellipsoidal shape
5. Roll the ellipsoidal sample at a rate of about 80 strokes per minute until it
crumbles at 1/8 inch diameter
6. Place the crumbled pieces in the moisture can
7. Weigh the can and wet soil sample (W2)
8. Place the sample in the oven
9. Repeat steps 3-8 several times
10.After drying weigh the can and the dry soil sample (W3)
The samples should all yield moisture contents that are about the same. I like to do
some statistics on the results.
Calculations
The plastic limit is the moisture content when the sample crumbles at 1/8 inch
diameter.
The plasticity index is the liquid limit – the plastic limit
PI=LL-PL
Here is some sample data:
The plastic limit calculation looks like this in Excel:
=(C12-D12)*100/(D12-B12)
The mean looks like this:
=AVERAGE(E12:E14)
Explain
AVERAGE(cell1:cell2), is another built-in function. The colon, :, between the two
cells mean all of the adjacent cells between the two cells. You use a comma for non-adjacent cells.
The sample standard deviation looks like this:
=STDEV(E12:E14)
Explain
STDEV(cell,1:cell2), estimates the standard deviation based on a sample.
The sample standard deviation of the mean looks like this:
=E18/SQRT(3)
Explain
SQRT(number), returns the square root of a number. The number needs to be a
positive real number.
The % error looks like this:
=E20*100/E16
If your limit was less than 5% error, then this test just made it. If your limit was
less than 2% error, you should run the test again.
Sieve Analysis
The sieve analysis (ASTM D422) is one of three tests used to classify soil bases on:
Unified Soil Classification System (ASTM D2487)
AASHTO Soil Classification System (AASHTO M145)
The liquid limit and the plastic limit are the other two tests needed to classify soil.
You will need the following Equipment:
1. Sieve numbers, 4, 10, 20, 40, 60, 140, 200 a pan and a cover.
2. A balance sensitive to 0.1 g
3. Oven
4. Mechanical sieve shaker
Procedure
1. Collect about 500 grams of oven dry soil
2. Weigh the sample (W)
3. Stack the sieves with the cover and No. 4 on top and No. 200 and pan on the
bottom
4. Pour the soil into the top sieve, No. 4
5. Put the cover on
6. Run the stack of sieves through the shaker for 10-15 minutes
7. Stop the shaker and remove the sieves
8. Weight the amount retained on each sieve and the pan
Calculations
Percent of soil retained on nth sieve
Cumulative percent of soil retained on nth sieve
Cumulative percent passing through the nth sieve
Uniformity coefficient (Cu)
Coefficient of curvature (Cc)
Included is a soil sample sieve analysis.
% retained looks like this in Excel:
=C12/$C$22*100
Explain
The dollar sign in front of the column C and the row 22, makes it an absolute cell
address. That means when you copy an equation to other cells, it will have the
same cell address in every equation. That means every number in cells C11 to C18
are divided by the value in C22, (500).
Cell C22 is the weight, (W), before putting in the shaker. Cell C20 is the sum of all
the weights, (W1), retained on each sieve.
Cumulative % retained looks like this:
=E11+D12
Percent finer looks like this:
=100-E12
% loss during sieve analysis looks like this:
=((C22-C20)/C22)*100
You also make an X-Y scatter graph with a logarithmic X-axis.
From the graph or better yet by straight line interpolation:
D10=0.098 mm
D30=0.21 mm
D60=0.46 mm
Specific Gravity
The specific gravity or the relative density of soil (ASTM D854), is the ratio of the
soils unit weight to the unit weight of water.
You will need the following Equipment:
1. Volumetric flasks (500 ml), a similar test uses a 100 ml pycnometer
2. Thermometer
3. Balance sensitive to 0..01 g
4. Distilled water
5. Bunsen burner or vacuum pump
6. Evaporation dishes
7. Spatula
8. Plastic squeeze bottle filled with distilled water
9. Drying oven
Procedure
1. Oven dry the soil
2. Place about 100 grams into each flask, determine the mass of the dry soil,
W3
3. Add water until the flask is about ¾ full
4. Remove the air by Bunsen burner or vacuum, fill to mark and determine the
mass of the flask, soil and water, W2
5. Empty and clean the flask
6. Fill the flask with water to the mark and determine the mass of the flask and
water, W1
7. Record the temperature of the water, T1 in °C
Calculations
Specific gravity at temperature T1
Specific gravity at 20 °C
Determine A from the above table. We will run several samples of the same soil and
would like to get an error of less than 2-3%.
Here is the data from three samples of the same soil.
Our % error is much less then 2%.
Gs, looks like this in Excel:
=B13/((B11+B13)-B12)
Gs @ 20° C, looks like this in Excel
=B15*B16
The mean, standard deviation, standard deviation of the mean and % error all look
similar to what was shown earlier in this document.
Proctor
The proctor is a compaction tests. There are two tests, the standard proctor and the
modified proctor. The standard proctor (ASTM D698) uses a 5.5 lb hammer dropped
from a height of 12 inches into a mold filled in three layers at 25 blows per layer.
The modified proctor (ASTM D1557) uses a 10 lb hammer dropped from a height of
18 inches into a mold filled in five layers at 25 blows per layer.
You will need the following Equipment:
1. Compaction mold (1/30 cu ft)
2. No. 4 sieve
3. Standard or modified proctor hammer
4. Balance sensitive to 0.01 lb
5. Balance sensitive to 0.1 g
6. Large flat pan
7. Jack
8. Steel straight edge
9. Moisture cans
10.Drying oven
11.Plastic squeeze bottle filled with water
Procedure
1. Obtain about 10 lb of air-dry soil passing a No. 4 sieve
2. Add enough water to bring the moisture content to about 5%
3. Determine the weight of the mold and base plate, without the extension, W1
4. Attach the extension
5. Pour soil into the mold in three layers, compacting with the hammer at 25
blows per layer
6. Remove the extension
7. Trim the sample with the straight edge
8. Determine the weight of the mold, base plate and moist soil, W3
9. Remove the soil from the mold with the jack
10.Determine the mass of your moisture can, w1, take a sample of the moist siol
and place it in the moisture can and determine the weight of the moist soil
and can, w2
11.Place moisture can in oven to dry
12.Break up the compacted soil and combine with the soil left in the pan
13.Add water to bring the moisture content up by about 2%
14.Repeat steps 3-13 until the weight, W2, drops twice
15.The next day, determine the mass of the moisture cans and dry soil, w3
Calculations
Moist unit weight
Dry unit weight
Moisture content
Zero-air-void
Here is a complete set of data for one soil:
Note: Test #1 data is bad and is not included in the graph. The values in column E
look like this in Excel:
=30*D11
The values in column F come from row 22 and are calculated like this:
=(B20-B21)/(B21-B19)*100
The values in column G are calculated like this:
=E11/(1+(F11/100))
The values in column H are calculated like this:
=62.4/((F11/100)+(1/$H$7))
Explain
Again the dollar sign in front of column H and row 7 make it an absolute cell
address. That means that the value, 2.7 in that cell address will be copied in all
equations.
A graph of the data should look like this:
As you can see from the graph, the dry unit weight will increase to a certain point
and then start to drop. It looks like the maximum dry density is about
98.5 lb / cu ft and the optimum moisture content is between 17-18%. Also from the
graph, the Zav curve should be and is above the proctor curve. It should also be
about parallel to the right side of the proctor curve.
Conclusion
Excel can be used for many different soil tests. I always say, “I don’t see how
engineers ever got along without Excel.” Many engineers used to use slide rules to
calculate and graph paper to plot the results. Excel can do the calculations and plot
the results. It can then be copied and pasted into Word for a nice report. I will
include a copy of all these spreadsheets which you can install on your computer.
Also included will be the sand cone (ASTM D1556) and drive cylinder (ASTM
D2937). I sure hope I got all the ASTM standards right. Refer to the ASTM
standards for the exact equipment and procedures.