The enclosed spreadsheet uses a macro that is capable of estimating secant Young’s modulus at 50% of ultimate shear strength  (E_{50}), friction angle (\phi), and undrained shear strength (S_{u}) of a soil layer based on typical borehole data such as SPT blow count and unit weight. This spreadsheet can be very handy when no CPT and/or laboratory test results are available.

The spreadsheet can be used for three types of soils : sands with fines content less than 10%, silts, and clays.

As noted above, the spreadsheet is applicable for clean sands or sandy soil with very small fraction of fines (FC < 10%). This is because according to the literature (e.g., Phan et al., 2016), friction angle of sandy soils decreases with the increase of fines content (FC), and to date I have not encountered any equation that accounts for the influences of fines content on physical properties of clean sands. Note that clean sand conditions would NOT be properly represented by the use of equivalent clean sand SPT blow count (N_{1(60, cs)}) which was originally proposed for evaluation of liquefaction potential of sands with fines content greater than 5%. Also note that N_{1(60, cs)} is always greater than N_{1(60)} , and this implies that sandy soil with higher fines content have larger friction angle which negates the field and laboratory tests observations. To this end, the enclosed spreadsheet should NOT be used for sand-silt or sand-clay mixtures (typically FC > 10%).

The macro calculates the secant Young’s modulus at 50% of ultimate shear strength  (E_{50}) based on the following equation:

E_{50} = G_{max} = \rho V_s^2

where G_{max} is low strain shear modulus, \rho  is soil mass density, and V_s is shear wave velocity. Depending on the choice of soil type, the macro uses two well-established equations available in the literature to calculate E_{50}. The references for the methods are listed in the spreadsheet in the tab “References”.

Cohesionless material friction angle is calculated using four different methods as a function of SPT blow count and effective overburden pressure. Average or minimum of the friction angles calculated by the four methods can be used for design depending on the reliability of the available data. The equations are formulated in a manner that the resulting friction angle lies within the typical ranges  reported by Peck, Hansen and Thornburn (1974) for sands. For ease of access, the typical values are presented in the spreadsheet in the tab “References”.

Undrained shear strength of cohesive soils, i.e., silt and clay, is calculated using two methods where the input parameters are effective overburden pressure, SPT blow count, and Over consolidation ratio (OCR). The typical undrained shear strength of cohesive soils reported by Terzaghi and Peck (1967) is incorporated into the spreadsheet to ensure that the estimated values are in agreement with previous field and laboratory measurements for similar soils. These typical values are presented in the spreadsheet in the tab “References”.

You can download the spreadsheet here (last updated 05/08/2018):  E50-Phi-Su-Estimator.xlsm

I have validated the spreadsheet for some case histories by comparing the estimated values against those correlated from CPT data as well as those measured in the lab. There were generally good agreement between the estimated and measured values.

Please read the notes in the spreadsheet carefully.