A hydrometer analysis is a test done on a soil sample to determine the particle size distribution for the smaller fraction of the soil particles. It involves mixing the soil with water in a glass beaker and measuring how rapidly the particles settle out of the solution. The density of the solution changes with time as the particles settle to the bottom of the beaker. The following spreadsheet is designed to analyze the results of a hydrometer test:
Right click here to download a copy of the spreadsheet and enter the formulas for the empty cells shown in light blue according to the following directions.
1. Before entering the formulas, first name the cells at the top of the spreadsheet as follows. Use these names in your formulas.
| Variable | Cell | Name |
| Dry weight of sample | G4 | Ws |
| Specific gravity | G5 | Gs |
| Temperature (oF) | G6 | Tf |
| Temperature (oC) | G7 | Tc |
| Meniscus correction factor (Fm) | G8 | Fm |
| Zero correction factor (Fz) | G9 | Fz |
| Temperature correction factor | G10 | Ft |
| Stoke's law coefficient | G11 | A |
| Specific gravity correction factor | G12 | aa |
| GS index list | K5:R5 | gslist |
| Stoke's Law coeff. table | K6:R19 | stokestable |
2. Enter a formula in cell G7 to convert the temperature from oF to oC.
3. Enter a formula in cell G10 to compute the temperature correction factor (FT).
4. Enter a formula in cell G11 to find Stoke's law coefficient (A) by referenceing Table 2.1.
a. To use this table, you will need to do a "double lookup". You will use a regular VLOOKUP to find the row with the right temperature in the first column using a range lookup.
b. Then you will need to figure out which column should be used to return a value based on the Gs value (third argument to VLOOKUP). To do this you will pass the Gs value (in cell G5) to the MATCH function and use the gslist (K5:R5) to return the index (1,2,3,etc) corresponding to the column in the list where the match is found. In other words, you will embed a call to the MATCH function within the VLOOKUP function (you can assume that you will find an exact match for Gs with the MATCH function). This process is described in the "Two-Dimensional Lookup" section of the VLOOKUP chapter of the VBA Primer. (*Note: the example given in the primer is more complicated than this one. You will not need to use the TEXT() function.)
5. Enter the formula for the Rcp column.
6. Enter the formula for the Percent finer (Pf) column.
7. Enter the formula for the Rcl column.
8. Enter the formula for the particle diameter (D) column. You do not need to do any units conversion. The A coefficient is calibrated to return a diameter in mm.
1. Be sure to save the changes to your spreadsheet.
2. Upload your spreadsheet via Learning Suite..