The Use of Spreadsheets for the Seismic Design of Piles

Salah Sadek
Faculty of Engineering and Architecture
American University of Beirut
[email protected]
Fadi Freiha
Faculty of Engineering and Architecture
American University of Beirut
ff[email protected]

December 21, 2004

Abstract
Understanding the response of pile foundations to seismic loads is a necessary pre-
requisite to properly analyze and/or design them. This objective presents the civil
engineering student and teacher with a complex problem, which typically challenges
and strains the traditional didactic educational approach. In teaching the topic,
we had faced significant difficulties in meeting the challenge of leaving the students
with a thorough appreciation and understanding of the material. This was reflected
in course evaluation results and comments. As such, an attempt was initiated to
improve and enhance the learning/teaching process by incorporating a spreadsheet-
based set of analysis/design tools with associated graphical representations. The
approach described herein proved to be an effective and accessible method, accord-
ing to the feedback solicited from students. In this paper, the basic background
and available methods for the design of laterally loaded piles are presented along
with the developed spreadsheet tool. Two design methods were included; one is
a commonly used solution for laterally loaded piles; the other is a hybrid method
which we compiled during the course of our work. The programming techniques and
the methodologies adopted are also included. The Excel spreadsheets which were
developed are presented along with illustrative examples.

Submitted March 2004; revised and accepted September 2004.

Keywords: deep foundations, seismic design, soil-structure interaction, spreadsheet,

civil engineering education.

1 Introduction
Understanding and mastering the seismic analysis and design of deep foundations is
a challenging yet essential element of the advanced education of students in the field

eJSiE 1(3):142-167 c
°2004 Bond University. All rights reserved.
http://www.sie.bond.edu.au
 S Sadek and F Freiha

of civil engineering. Our past experience in the academic context of helping students
achieve the desired outcomes had been a frustrating endeavor, given the time and effort
invested. It is in part in response the need to provide a leaner and more efficient learning
and teaching approach that the work described in this paper evolved.
In essence, the transfer of lateral loads from deep foundations to the subsurface strata
is a complex soil-structure interaction problem. The movements and flexural stresses in
the pile depend on the soil resistance, while the soil resistance is a function of the
deformations of the pile itself. Furthermore, the ultimate resistance of a vertical pile to
a lateral load and the deflection of the pile as the load builds up to its ultimate value are
complex and involve the interaction between a semi-rigid structural element and soils
which deforms partly elastically and partly plastically. Given the typically limited time
and resources allocated to this topic in a three credit course, as other equally relevant
applications are to be covered, imparting sufficient and fundamental understanding of
this applied problem constitutes a real challenge that the spreadsheet approach presented
herein attempted to meet.
In this paper, an Excel-based teaching and analysis tool is presented, which attempts
to address some of the educational and applied challenges associated with the problem.
The basic premise of the educational approach is anchored in the need and desire to
empower the student to explore effects of the various parameters on the response of
the piles in an environment designed to generate rigorous solutions with auto-generated
and easy to interpret graphical representations. The paper also explores the benefits of
coupling programming and spreadsheet calculations.
We have taken the spreadsheet developed through one cycle of testing in an actual
classroom environment as part of a course on geotechnical earthquake engineering at
the American University of Beirut in the Fall 2003 semester. The number of students
registered for the course was 18 (10 graduate and 8 undergraduate students). The process
and tool presented in this paper have significantly improved the educational experience
from the teaching perspective, but also have generated more interest and response from
the students than we had experienced in similar contexts using the traditional didactic
approach. The use of the developed spreadsheets allowed us to adapt “what if?” scenarios
to illustrate the effect and importance of certain parameters such as soil types, pile
characteristics, levels and type of loading, etc. in the classroom in real—time.

2 Background—the engineering problem

Extensive work has been published on the resistance of piles to lateral loading. However,
no simple design method has emerged, which can be universally applied to any soil
and/or pile type. A number of interrelated factors influence the response of piles under
seismic/lateral loading conditions. The pile stiffness is a critical factor given that it
controls the deflection and determines whether the failure mechanism is one of rotation
of a short rigid element, or flexure in bending of a long flexible member. The type of
loading, whether sustained, alternating, or pulsating, influences the degree of yielding of
the soil.

eJSiE 1(3):142—167 143

 Seismic Design of Pile Foundations

Seismic loading is typically considered as an additional “pseudo-static” lateral load

applied to the pile. Piles are then analyzed by separating the two cases of vertical
and lateral loading and then superposing the results. However, such an approach is
inaccurate in non-elastic, non-linear media such as soils. Nevertheless, superposition
still yields acceptable results given the fact that vertical loading involves the deeper
layers of soil while the horizontal loading tends to involve the near-surface layers.
Earthquake loads are usually accounted for by the following two methods: Equivalent
static load or equivalent dynamic load (Poulos and Davis [11], and Nair [10]). The need
for an equivalent load arises if the solution of the rigorous problem is time-consuming
and difficult.
In this paper, the presented analyses and design methods are typically based on the
“pseudo-static” approach. Two methods are currently in use to determine the magnitude
of the equivalent static load to be applied at the top of the pile (Nair [10]):

• The load taken is a certain percentage of the vertical static load.

• The total lateral load applied is taken to be the base-shear utilized in the seismic
analysis of the building resting on the piles.

To obtain the base shear of the building or the horizontal force applied at the top of
the pile, the formula Fh = Fv Sa /g is used.
From attenuation curves, and using deterministic or probabilistic seismic hazard
analyses, a peak horizontal acceleration is obtained. It is then corrected, based on the
type of in-situ soil. Normalized response spectra are then used to assign the appropriate
spectral acceleration Sa for the design of the piles supporting the structure based on its
natural period. Fv is the vertical force applied on the pile and g is the acceleration due
to gravity.

3 The use of Excel spreadsheets

Despite the rational adoption of the simplified methodologies presented earlier, the analy-
sis of the laterally loaded piles remains a difficult undertaking. The complexities are
associated in part with the difficulty of calculating and visualizing the moments and
deflections along the full length of the piles given certain loading conditions, as well as
the understanding of the effects of the various inter-related parameters that influence
the pile design (Figure 1). The “difficulty in visualizing behavior” was in fact the prob-
lem most identified by earlier student evaluation and feedback. As such, and in part
in order to promote a more fundamental understanding of the problem, solutions and
visualizations within an Excel spreadsheet environment were developed for use in the
classroom. Whenever we needed to perform a task repeatedly, a Visual BASIC macro,
which is basically a series of commands and functions that can be run on the fly, was em-
bedded within the spreadsheet. This enabled us to automate some loops and repetitive
procedures and solve for values using trial and error.

144 eJSiE 1(3):142—167

 S Sadek and F Freiha

Figure 1: The level of complexity of the formulas used in the various spreadsheets

The seismic pile design methods which were incorporated in the spreadsheets were the
“Characteristic Load Method” (Duncan et al [5]), and the “Hybrid Analytical Method”
(based on Matlock and Reese [8] and Davisson and Gill [4]).
These design methods were adopted given that they are typically used in engineer-
ing practice today. Automating the various expressions and procedures involved in the
analyses allowed us to develop a reliable and useful design tool. In this context, when
using this tool for education in the classroom, students are being introduced to pro-
fessional level practice while at the same time relying on the same approach to gain
knowledge of the fundamentals. The exposure to academic teaching tools which can
also serve the purpose of real-world design aids is an added benefit which enriches the
educational experience of the student of applied engineering.

3.1 Building the spreadsheets

A large set of parameters, some of which are interrelated, is involved in the analysis.
In order to facilitate the interaction between the student and the spreadsheets, which
may seem intimidating at first glance, the cell values that require an input by the user
are highlighted in light yellow and the ones that will be automatically generated are
highlighted in dark grey. Furthermore, the student is guided through the design-analysis
process by comments/hints which are linked to the individual cells. They include the
definition of each term, the units that should be used, etc. (Figure 2).
Some of the calculations involve trial and error procedures and loops. In such in-
stances, Visual BASIC macros were assigned to specifically created buttons for the pur-
pose of running these loops and obtaining the required parameter value with minimal
effort. The idea is to lessen time spent on performing tedious and repetitive tasks which

eJSiE 1(3):142—167 145

 Seismic Design of Pile Foundations

encroach on the “quality-learning time” and reduce the tedium to a few mouse clicks
while maintaining the advantages of spreadsheet interfaces (Figure 2). It is important
to add here that the automated processes do not create “black boxes” invisible to the
student, but merely perform those tasks that are fully understood and clearly explained.
After entering all the required parameters the response of the piles, expressed in
terms of moments and deflections along the pile length, can be calculated at a depth
increment assigned by the user. Charts are then auto-generated in separate worksheets
to visualize the variation of these moments and deflections with depth. To do that,
the usual charting procedure is used and recorded in a new macro, its code modified to
fit regardless of the actual data limits. This specific macro is assigned to a button for
future re-use. This ability of creating “instantaneous” graphed results as variables are
modified or changed is at the core of the power of the spreadsheets in an educational
context. Students were encouraged to explore the effect of changing the pile stiffness,
material, soils, etc. on the predicted behavior. This was simply not possible prior to the
introduction of this approach into the classroom.
Based on the feedback we received from the students who took part in the course
evaluation, a majority noted the spreadsheets used in the seismic design of piles as key
for their appreciation of the various elements involved and their contribution to the
overall response. The spreadsheets enhanced the problem solving task extensively and
allowed us to assign a greater number of different tasks and problems.

Figure 2: Light yellow cells, dark grey cells, comments, and VB buttons used in the
various spreadsheets

146 eJSiE 1(3):142—167

 S Sadek and F Freiha

In the following sections the two analysis methods are illustrated through the example
of a pile embedded in clay and supporting an earthquake load equivalent to a static
horizontal load of 0.1 MN and a moment of 0.05 MJ. The response of the pile will be
analyzed using the created Excel spreadsheets. Tables and figures are added to show the
results of both methods allowing for some comparison.
The example considers a square concrete free-headed pile, 15 m long, 0.6 m wide. The
concrete modulus is equal to 27800 MPa. The pile is embedded in a plastic, normally
consolidated clay, with a linearly increasing soil modulus. The soil undrained shear
strength is 48 kPa, the soil modulus is 36 MPa (= 750× undrained shear strength), and
the coefficient of modulus variation is approximately 6000 kPa m−1 depth.

4 Characteristic load method (CLM)

The characteristic load method can be used to determine:

1. Deflections due to lateral loads for free-head conditions, fixed-head conditions, and
the “flag-pole” condition.

2. Deflections due to moments applied at the ground line.

3. Maximum moments for the free-head conditions, fixed-head conditions and the
flag-pole condition.

4. The location of the maximum moment in the pile or drilled shaft.

Despite the number of simplifying assumptions the characteristic load method still
involves many parameters. Tabulating these parameters was deemed necessary in order
to ease the spreadsheet-based trial and error processes (Figure 3). All relevant parame-
ters are explained in detail in the following paragraphs.

4.1 Non-dimensional relationships with characteristic load Pc and mo-

ment Mc
The characteristic load method formulation is based on non-dimensional relationships
related to load and moment. Equations (1)—(3) describe load and moment.
µ ¶
σp m1
2
PC = λ1 B ER1 (ε50 )n1 (1)
ER1
µ ¶m2
σp
3
MC = λ2 B ER1 (ε50 )n2 (2)
ER1
64I
R1 = (3)
πB 4
Note that the moment of inertia ratio, R1 = 1.00 for solid circular cross-sections and
1.70 or solid square cross-sections. For plastic clay and sand, λ1 = λ2 = 1.00. For

eJSiE 1(3):142—167 147

 Seismic Design of Pile Foundations

Figure 3: The Excel worksheet of the characteristic load method

Table 1: Typical ε50 values (Matlock [9])

Soil Type ε50

Disturbed, remolded, soft clay 0.020
Normally consolidated medium clay 0.010
Brittle, stiff, sensitive clay 0.005
Medium dense sand with little or no mica 0.002

brittle clay, λ1 = (0.14)n1 , and λ2 = (0.14)n2 . For clay, σp = 4.2su . For sand σp =
2CpΦ γB tan2 (45 ◦ +Φ0 /2) .
P c is the characteristic shear load, and M c the characteristic moment load. λ1
and λ2 are dimensionless parameters that depend on the soil stress-strain behavior.
B represents the diameter of the foundation,
p and E the modulus of elasticity of the
0
foundation (200, 000 MPa for steel; 4700 fc for concrete; 11, 000 MPa for pine wood),
where fc0 is the 28-day compressive strength of concrete (MPa). The representative
passive pressure of soil is denoted by σp , and ε50 is the axial strain at which 50% of
the soil strength is mobilized, obtained from triaxial compression tests or from Table
1. The exponents m1 , n1 , m2 , n2 are those from Table 2. I is the moment of inertia of
foundation, and s the undrained shear strength from the ground surface to a depth of 8B.
The effective friction angle (deg) from ground surface to a depth of 8B is represented by
Φ0 , while CpΦ = Φ0 /10 denotes the passive pressure factor. Finally, γ is the unit weight
of soil from ground surface to a depth of 8-pile diameters. If the water table is within
this zone, use a weighted average of γ and γb = γ − γw . All the above parameters were
tabulated in Excel in the format shown in Figure 3. The soil properties are reduced to

148 eJSiE 1(3):142—167

 S Sadek and F Freiha

Table 2: Values of the exponents m and n (Evans and Duncan [6])

For Pc For Mc
Soil Type m1 n1 m2 n2
Clay 0.683 -0.22 0.46 -0.15
Sand 0.57 -0.22 0.4 -0.15

Table 3: Constants for Load-deflection equations yt /yc = a1 (Pt /Pc )b1 (Brettmann &
Duncan [2])

Clay Sand
Constant Free head Fixed head Free head Fixed head
a1 50 14 119 28.8
b1 1.822 1.846 1.523 1.5

those of the upper 8-pile diameter depth.

Three main groups of non-dimensional relationships are used in the characteristic
load method: Load-deflection, moment-deflection, and load-moment. Different relation-
ships for each group are used for sands and clays. The load-deflection and load-moment
relationships are further subdivided into free-head and fixed head conditions.
A single-term exponential equation in the form of y = axb where a is a constant
and b is an exponent, is used for the non-dimensional relationships. The coefficients a
and b are obtained from a power regression analysis. The constants and exponents for
each of the relationships are determined within the spreadsheet tool using an iterative,
non-linear, least squares curve fitting technique.

yt /yc = a1 (Pt /Pc )b1 With a and b deduced from Table 3. (4)

yt /B = a2 (Mt /Mc )b2 With a and b deduced from Table 4. (5)

Pt /Pc = a3 (Mmax /Mc )b3 With a and b deduced from Table 5. (6)
In these equations, yt is the ground line deflection, B the pile or drilled shaft diameter
or width, Pt the lateral load at top of pile or drilled shaft, Mt the moment at the top of
pile or drilled shaft, and Mmax is the maximum moment.

The flexural stiffness, Ep Ip , of reinforced concrete piles and drilled shafts should be
reduced when tensile stresses are large enough to cause cracking. The magnitude of
the reduction of Ep Ip depends on the ratio of the maximum moment computed to the
ultimate moment capacity or cracking moment of the section. For cracked sections, the

eJSiE 1(3):142—167 149

 Seismic Design of Pile Foundations

Table 4: Constants for Moment-deflection equations yt /B = a2 (Mt /Mc )b2 (Brettmann

& Duncan [2])

Constant Clay Sand

a2 21 36
b2 1.412 1.308

Table 5: Constants for Load-moment equations Pt /Pc = a3 (Mmax /Mc )b3 (Brettmann &
Duncan [2])

Clay Sand
Constant Free head Fixed head Free head Fixed head
a3 1.22 1.63 0.425 0.669
b3 0.79 0.86 0.762 0.84

moment of inertia of concrete is reduced to 40% − 50% of the value for the full-uncracked
concrete section. No reduction is made in the moment of inertia of the steel.

4.2 Deflection due to combined load and moment

Given that the response is non-linear, simply adding the deflections caused by the lateral
load and the moment is not sufficient. Instead, the non-linear effects should be taken into
account by using a non-linear superposition procedure accounted for in the spreadsheet
solution developed.
The first step in the non-linear superposition is to calculate the deflections that would
be caused by the load Pt or E4 acting alone ytp and by the moment Mt or H4 acting alone
ytm using the equations listed in the previous section and written in Excel as follows:
ytp or E6
=IF(AND(B4="sand",B21="free"),B17*119*((E4/E2)^1.523),
IF(AND(B4="sand",B21="fixed"),B17*28.8*((E4/E2)^1.5),
IF(AND(B4="clay",B21="free"),B17*50*((E4/E2)^1.822),
IF(AND(B4="clay",B21="fixed"),B17*14*((E4/E2)^1.846),"--"))))
ytm or H6
=IF(AND(B4="sand",B21="free"),B17*36*((H4/H2)^1.308),
IF(AND(B4="sand",B21="fixed"),B17*36*((H4/H2)^1.308),
IF(AND(B4="clay",B21="free"),B17*21*((H4/H2)^1.412),
IF(AND(B4="clay",B21="fixed"),B17*21*((H4/H2)^1.412),"--"))))
The second step is to get the load Pm that can cause ytm and the moment Mp that
can cause ytp as follows;
Pm or E8
=IF(AND(B4="sand",B21="free"),((H6/(B17*119))^(1/1.523))*E2,
IF(AND(B4="sand",B21="fixed"),((H6/(B17*28.8))^(1/1.5))*E2,

150 eJSiE 1(3):142—167

 S Sadek and F Freiha

IF(AND(B4="clay",B21="free"),((H6/(B17*50))^(1/1.822))*E2,
IF(AND(B4="clay",B21="fixed"),((H6/(B17*14))^(1/1.846))*E2,"--"))))
Mp or H8
=IF(AND(B4="sand",B21="free"),((E6/(B17*36))^(1/1.308))*H2,
IF(AND(B4="sand",B21="fixed"),((E6/(B17*36))^(1/1.308))*H2,
IF(AND(B4="clay",B21="free"),((E6/(B17*21))^(1/1.412))*H2,
IF(AND(B4="clay",B21="fixed"),((E6/(B17*21))^(1/1.412))*H2,"--"))))
The third step is computing the average of ytpm caused by the load Pt + Pm and ytmp
caused by Mt + Mp using the equations of the previous section:
ytpm or E10
=IF(AND(B4="sand",B21="free"),B17*119*(((E4+E8)/E2)^1.523),
IF(AND(B4="sand",B21="fixed"),B17*28.8*(((E4+E8)/E2)^1.5),
IF(AND(B4="clay",B21="free"),B17*50*(((E4+E8)/E2)^1.822),
IF(AND(B4="clay",B21="fixed"),B17*14*(((E4+E8)/E2)^1.846),"--"))))
ytmp or H10
=IF(AND(B4="sand",B21="free"),B17*36*(((H4+H8)/H2)^1.308),
IF(AND(B4="sand",B21="fixed"),B17*36*(((H4+H8)/H2)^1.308),
IF(AND(B4="clay",B21="free"),B17*21*(((H4+H8)/H2)^1.412),
IF(AND(B4="clay",B21="fixed"),B17*21*(((H4+H8)/H2)^1.412),"--"))))

4.3 Maximum moments and their location

In fixed-head flexible piles or drilled shafts, the maximum moment occurs at the top,
where the pile or shaft is embedded in the cap. In free-head flexible piles and drilled
shafts, the maximum moment occurs at some depth below the ground. The magnitude
and location of the maximum moment can be estimated using the theory, for soil modulus
increasing linearly with depth: the characteristic length T , solution by trial and error of
the following equation is first calculated.
2.43Pt 3 1.62Mt 2
ycomb = T + T (7)
Ep Ip Ep Ip
This is done by running a simple Visual BASIC macro assigned to a button named
"Check". (Ref. Figure 3). This macro is as follows:
Sub trialanderror()
Cells(19, 5) = 0
Do
Cells(19, 5) = Cells(19, 5) + 0.001
Cells(21, 5) = Cells(20, 5)
Loop Until Cells(21, 5) = "check"
End Sub
The formula in Cells(20,5) or E20 is:
=IF(AND((((((2.43*E4)/(B23*B19))*E19^3)+((1.62*H4)/(B23*B19))*E19^2)
/E13>0.99),(((((2.43*E4)/(B23*B19))*E19^3)+((1.62*H4)/(B23*B19))*E19^2)
/E13<1.01)),"check",IF(OR(B21="Fixed",B21="fixed"),"check","retry"))

eJSiE 1(3):142—167 151

 Seismic Design of Pile Foundations

The idea is to keep increasing the value of

µ ¶
2.43Pt 3 1.62Mt 2
T + T /ycomb
Ep Ip Ep Ip

from zero until it reaches 1. At that point the macro stops and the correct value for the
characteristic length is given in cell E19.
Then the moment MZ , at a depth equal to z, is calculated from equations derived
from the values in Table 6 and from equation (8).

MZ = Am Pt T + Bm Mt (8)

To use Excel, a formula needed to be created for Am and Bm . To do that, poly-fitting

in MATLAB was used. Then, linear interpolation is used to get the correct Am or Bm
for a particular Zmax . Two typical Excel formulas for Am and Bm can be written as
follows:
Am or L12
=IF(AND(Z<=1.5,L$11>=5),-0.0552*Z^6+0.2175*Z^5-0.2136*Z^4
-0.1507*Z^3-0.0856*Z^2+1.0145*Z+0,IF(AND(Z>1.5,L$11>=5),
0.0198*Z^5-0.3261*Z^4+2.0962*Z^3-6.4768*Z^2+9.1767*Z
-4.0034,IF(AND(Z<=1.5,L$11=4),-0.0552*Z^6+0.2175*Z^5-0.2136
*Z^4-0.1507*Z^3-0.0856*Z^2+1.0145*Z+0,IF(AND(Z>1.5,
L$11=4),-0.0376*Z^4+0.4664*Z^3-2.0326*Z^2+3.3284*Z-1.041
,IF(L$11=2,-2.3599*Z^7+13.8516*Z^6-31.3601*Z^5+35.1888*Z^4
-20.9114*Z^3+5.6052*Z^2+0.4698*Z,IF(L$11=3,-0.4268*Z^9
+4.7435*Z^8-21.4445*Z^7+50.9252*Z^6-68.5924*Z^5+
53.0533*Z^4-23.0229*Z^3+4.8467*Z^2 +0.6119*Z,"--"))))))
Bm or O12
=IF(AND(Z<=1.5,O$11>=5),-0.2279*Z^6+0.8659*Z^5-1.1393*Z^4
+0.582*Z^3-0.2603*Z^2+0.0316*Z+1,IF(AND(Z>1.5,O$11>=5),
0.0039*Z^5-0.0705*Z^4+0.4808*Z^3-1.4355*Z^2+1.4616*Z
+0.3803,IF(AND(Z<=1.5,O$11=4),-0.2279*Z^6+0.8659*Z^5
-1.1393*Z^4+0.582*Z^3-0.2603*Z^2+0.0316*Z+1,IF(AND(Z>1.5
,O$11=4),0.0039*Z^5-0.0705*Z^4+0.4808*Z^3-1.4355*Z^2
+1.4616*Z+0.3803,IF(O$11=2,-0.0603*Z^7+0.573*Z^6-1.9801*Z^5
+3.4467*Z^4-3.1852*Z^3+1.0065*Z^2-0.1385*Z+1,IF(AND(Z<2,
O$11=3),0.7054*Z^7-4.126*Z^6+9.2089*Z^5-9.8476*Z^4
+5.1559*Z^3-1.3884*Z^2+0.1268*Z+1,IF(AND(Z>=2,O$11=3),
0.0893*Z^3-0.382* Z^2-0.0873*Z+1.288,"--")))))))
The relatively high level of complexity of the above formulas should be noted. These
formulas are automatically filled in the Excel sheet by a simple automated copy/paste
process during the run of the “Fill them up” button or macro. Figure 4 shows the
moment values as they appear in the Excel worksheet after the macro is run.

152 eJSiE 1(3):142—167

 S Sadek and F Freiha

Table 6: Moment coefficients Am and Bm (Matlock and Reese [7], Das [3])

Z = z/T Zmax = L/T ≥ 5 Zmax = L/T = 4 Zmax = L/T = 3 Zmax = L/T = 2

Am Bm Am Bm Am Bm Am Bm
0 0 1 0 1 0 1 0 1
0.2 0.198 0.999 0.198 0.999 0.198 0.998 0.198 0.992
0.4 0.379 0.987 0.379 0.987 0.379 0.985 0.379 0.972
0.6 0.532 0.96 0.532 0.96 0.532 0.957 0.485 0.909
0.8 0.649 0.914 0.649 0.914 0.649 0.903 0.53 0.803
1 0.727 0.852 0.727 0.852 0.694 0.835 0.484 0.662
1.5 0.763 0.638 0.762 0.638 0.663 0.599 0.2 0.215
2 0.628 0.404 0.615 0.404 0.455 0.3 0 0
2.5 0.405 0.2 0.395 0.2 0.132 0.078
3 0.225 0.06 0.198 0.073 -0.01 0
4 0 -0.042 -0.025 0.03
5 -0.033 -0.026

Another Visual BASIC macro and button named "Draw Moment diagram" is then
used to draw the moment diagram along the pile length. This macro also works by
re-running previously recorded actions including the insertion of a new chart and its
formatting. The generated plot of the moments with respect to depth is shown in Figure
5.
To illustrate how the spreadsheet was used in class and/or assignments, the question
would be posed as to likely effect of having say a “stubbier” or thicker pile, say 1m in
diameter. The results generated instantaneously, and shown in Figure 6, demonstrate
that increasing the stiffness of the pile, increased the distributed moments in it.

5 Hybrid analytical method (HAM)

In an effort to expose students to additional available methods for the analysis of piles
under seismic loading, a hybrid approach was formulated which combines the solutions
provided by Matlock and Reese [8], and Davisson and Gill [4]. The methods were inves-
tigated and their respective strengths and limitations identified. The hybrid approach
allows the solution of more complex problems than the LCM such those involving lay-
ered soils. It consists primarily of combining analytical analysis procedures which focus
on various elements of the problem at hand. As such, the process presented narrows
the margins of error and provides the essential data for calculating bending moments,
shearing forces and deflections along the pile at the working loads. The fact remains
that some factors, such as the non-homogeneity of most of the natural soil deposits and
the disturbance to the soil caused by installing piles cannot be reproduced.
The educational benefit sought from this approach is to present the student with a

eJSiE 1(3):142—167 153

 Seismic Design of Pile Foundations

Figure 4: The calculation of the moment Mz at a depth equal to z

Moment diagram along the pile length

Moment (MN.m)
-0.05 0.00 0.05 0.10 0.15 0.20 0.25 0.30
0

-2

-4

-6
Depth (m)

-8

-10

-12

-14

-16
Moment diagram along the pile

Figure 5: An Excel chart showing the moment diagram in a pile along its length

154 eJSiE 1(3):142—167

 S Sadek and F Freiha

Moment diagram along the pile length

Moment (MN.m)
-0.05 0.00 0.05 0.10 0.15 0.20 0.25 0.30
0

-2

-4

-6
Depth (m)

-8

-10

-12

-14

-16
Moment diagram along the pile

Figure 6: An Excel chart showing the moment diagram in a pile of different diameter
(D = 1m instead of 0.6m)

number additional analysis methods in a clear and unifying context, rather than piece-
meal.
The format of the Excel worksheet for the “hybrid analytical method” is shown
below. The definition of each parameter is provided in the following paragraphs.

5.1 Stiffness factors and pile behavior

The first step in the process is to determine whether the pile will behave as a “short rigid
unit” or as an “infinitely long flexible member”. This is done by calculating the stiffness
factor for the particular combination of pile and soil. The stiffness factors are governed
by the stiffness (EI value) of the pile and the compressibility of the soil. The latter
is expressed in terms of a “soil modulus”, which is not constant for any soil type but
depends on the width of the pile B and the depth of the particular soil being considered.
In the case of stiff over-consolidated clays, the soil modulus is generally assumed to
be constant with depth. The stiffness factor, R, in units of length, is given by eq (9).
r
4 EI
stiffness factor, R = (9)
k
Vesic proposed in 1961 eq (10) to estimate the value of k a measure of the relative
soil/pile stiffness (Das [3], Poulos and Davis [11]).
s
Es D4 Es
k = 0.65 12 (10)
Ep Ip 1 − µ2s

eJSiE 1(3):142—167 155

 Seismic Design of Pile Foundations

Figure 7: The Excel worksheet of the hybrid analytical method

Table 7: Representative Values of nh (Das [3])

Soil Type nh (kN/m3 )

Dry or moist sand Loose 1800 − 2200
Medium 5500 − 7000
Dense 15000 − 18000
Submerged sand Loose 1000 − 1400
Medium 3500 − 4500
Dense 9000 − 12000

In eq (10), Es is the Young’s modulus of soil, which is 1500 to 2000Su for heavily
over-consolidated clays, and 750 to 1200Su for lightly over-consolidated clay (Bowles
[1]). Su denotes the undrained shear strength of soil, D the pile width (or diameter),
while µs represents Poisson’s ratio of the soil, which vary between 0.3 and 0.5. For most
normally consolidated clays and granular soils, the soil modulus is assumed to increase
linearly with depth. The stiffness factor is in this case symbolized by the letter T and
calculated as follows: r
EI
stiffness factor, T = 5 (11)
nh
In the Excel worksheet, the student only has to enter the soil type and the correct
formula is identified and used to calculate the appropriate stiffness factor (R or T ).
Typical values of the coefficient of modulus variation nh are shown in Table 7. For
soft normally consolidated clays, nh = 3500 to 14000 kN m−3 . For soft organic silt,
nh = 1500 kN m−3 .

156 eJSiE 1(3):142—167

 S Sadek and F Freiha

If the soil is layered and we have different values of nh at different depths, the constant
value of nh that must be used is the result of a linear regression between nh · z and z.
For this purpose, a new macro was created within the proposed spreadsheet tool and
assigned to a button named “estimate nh ” (Figure 7). This macro is as follows:
Sub linearregressiontogetnh()
Dim t As Integer
t = 0
Do
If Application.WorksheetFunction.IsNumber(Cells(t + 6, 4).Value) Then
t = t + 1
Cells(t + 6, 6) = Cells(t + 6, 5) * Cells(t + 6, 4)
Cells(t + 6, 6).Interior.ColorIndex = 56
End If
Loop Until Cells(t + 7, 4) = 0
Cells(7, 2) = Application.WorksheetFunction.LinEst(Range(Cells(6, 6),
Cells(t + 6, 6)), Range(Cells(6, 4), Cells(t + 6, 4)), False, False)
End Sub
The idea is that, when the user enters the values of nh at the different depths (e.g.:
cells D7:E18), and runs the macro, a worksheet function available to Visual BASIC called
“LinEst” is used to get the straight line that best fits the data. The method used to get
this line is the "least squares" method. An array that describes the line is then returned
(value of cell B7 in our case).
Having calculated the stiffness factor (R or T ), the criteria for behavior as a short
rigid pile or as a long elastic pile can then be related to the embedded length L as follows:

L ≤ 2 × stiffness factor =⇒ Rigid

L ≥ 4 × stiffness factor =⇒ Elastic

5.2 Elastic analysis of laterally loaded vertical piles

Matlock and Reese [8] established a series of curves for normally consolidated clay and
cohesionless soils for which the elastic modulus of the soil Es is assumed to increase from
zero at the ground surface in direct proportion to the depth. These curves represent the
deformed shape of the pile and the corresponding bending moments, (shearing forces
and soil reactions) using special coefficients. These coefficients are related to a depth
coefficient Z for various values of Zmax , where Z is equal to the depth z at any point
divided by T and Zmax = L/T .
For a linearly increasing soil modulus and a free pile head (subject to lateral load
and moment), the deflections and moments in the piles are respectively obtained from
equations (12) and (13).

Ay HT 3 By Mt T 2
yA + yB = + (12)
Ep Ip Ep Ip

eJSiE 1(3):142—167 157

 Seismic Design of Pile Foundations

Table 8: Equations for the calculation of bending moments and deflection in free head
piles carrying lateral load or moment at their heads in soils of constant modulus

Load on pile head For free-headed pile

Moment M Bending Moment M Mh
Moment M Deflection M ym R2 / (EI)
Horizontal load H Bending Moment HMh R
Horizontal load H Deflection Hyh R3 / (EI)

MA + MB = Am HT + Bm Mt (13)

The A and B coefficients are obtained from Figures 8 and 9.

For a linearly increasing soil modulus and a fixed pile head (subject to lateral load),
eqs (14) and (15) are respectively used for calculating the deflections and bending
moments.
Fy HT 3
yF = (14)
Ep Ip

MF = Fm HT (15)

The F coefficients are obtained from Figure 10.

Davisson and Gill [4] have analyzed the case of elastic piles in an elastic soil with
constant modulus. The bending moments and deflections are related to the stiffness
coefficient R using the equations of Table 8.
Note that the effect of fixity at the pile head can be allowed for by plotting the
deflected shape of the pile from the algebraic sum of the deflections and then applying a
moment to the head which results in zero slope for complete fixity, or the required angle
of slope for a given degree of fixity.
The dimensionless depth coefficient Z used in the curves is equal to z/R and Zmax =
L/R. These curves are obtained for free-head piles carrying a moment at the pile head
and zero lateral loads or, with zero moment at the pile head and carrying a lateral
load. The length L must be greater than 2R. The needed coefficients are obtained from
Figures 11 and 12.

158 eJSiE 1(3):142—167

 S Sadek and F Freiha

Coefficients for deflection

Deflection coefficient Ay
-2.00 -1.00 0.00 1.00 2.00 3.00 4.00 5.00 6.00
0.00

10.00
Depth coefficient Z

20.00

30.00

40.00

50.00

60.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Coefficients for bending moment

Moment coefficient Am
-0.10 0.00 0.10 0.20 0.30 0.40 0.50 0.60 0.70 0.80 0.90
0.00

10.00
Depth coefficient Z

20.00

30.00

40.00

50.00

60.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Figure 8: Coefficients for laterally loaded free-headed piles in soil with linearly increasing
modulus (after Matlock and Reese [8]): and Ay and Am

eJSiE 1(3):142—167 159

 Seismic Design of Pile Foundations

Coefficients for deflection

Moment coefficient By
-2.00 -1.00 0.00 1.00 2.00 3.00 4.00
0.00

0.50

1.00
Depth coefficient Z

1.50

2.00

2.50

3.00

3.50

4.00

4.50

5.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Coefficients for bending moment

Moment coefficient Bm
-0.20 0.00 0.20 0.40 0.60 0.80 1.00 1.20
0.00

1.00
Depth coefficient Z

2.00

3.00

4.00

5.00

6.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Figure 9: Coefficients for piles with moment at free head in soil with linearly increasing
modulus (after Matlock and Reese [8]): By and Bm

160 eJSiE 1(3):142—167

 S Sadek and F Freiha

Coefficients for deflection

Deflection coefficient Fy
-0.40 -0.20 0.00 0.20 0.40 0.60 0.80 1.00 1.20
0.00

1.00
Depth coefficient Z

2.00

3.00

4.00

5.00

6.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Coefficients for bending moment

Moment coefficient Fm
-1.20 -1.00 -0.80 -0.60 -0.40 -0.20 0.00 0.20 0.40
0.00

1.00
Depth coefficient Z

2.00

3.00

4.00

5.00

6.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Figure 10: Coefficients for fixed headed piles with lateral load in soil with linearly
increasing modulus (after Matlock and Reese, 1961 [8]): Fy and Fm

eJSiE 1(3):142—167 161

 Seismic Design of Pile Foundations

Coefficients for deflection

Moment coefficient Ym
-2.00 -1.50 -1.00 -0.50 0.00 0.50 1.00 1.50 2.00
0.00

1.00
Depth coefficient Z

2.00

3.00

4.00

5.00

6.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Coefficients for bending moment

Moment coefficient Mm
0.00 0.20 0.40 0.60 0.80 1.00 1.20
0.00

1.00
Depth coefficient Z

2.00

3.00

4.00

5.00

6.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Figure 11: Coefficients for free headed piles carrying a moment at pile head in soil of
constant modulus (after Davisson and Gill, 1963 [4]): Ym and Mm

162 eJSiE 1(3):142—167

 S Sadek and F Freiha

Coefficients for deflection

Moment coefficient Yh
-1.00 -0.50 0.00 0.50 1.00 1.50 2.00 2.50
0.00

1.00
Depth coefficient Z

2.00

3.00

4.00

5.00

6.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Coefficients for bending moment

Moment coefficient Mh
0.00 0.05 0.10 0.15 0.20 0.25 0.30 0.35 0.40 0.45 0.50
0.00

1.00
Depth coefficient Z

2.00

3.00

4.00

5.00

6.00
"Zmax = 10" "Zmax = 5" "Zmax = 4" "Zmax = 3" "Zmax = 2"

Figure 12: Coefficients for free headed piles carrying a lateral load at pile head in soil of
constant modulus (after Davisson and Gill [4]): Yh and Mh

eJSiE 1(3):142—167 163

 Seismic Design of Pile Foundations

Figure 13: Typical output of the calculation of the deflection (yZ ) and the moment (MZ )
at a depth equal to (z) for a free head pile and a linearly increasing soil modulus

In order to produce a “useful” form of the Hybrid Method which facilitates the
process of teaching the method and learning it, all the above curves had to be translated
into equations to be used in Excel. Particular points on the curves were therefore chosen
and then poly-fitted using MATLAB. The generated equations were then inserted in the
Excel worksheets to obtain the moment distribution and the deflection at each section
along the pile length.

Figure 13 presents the results of the moment and deflection values for the example
problem as they appear in the Excel worksheet after running the “Fill them up” button or
macro. Another Visual BASIC macro and button named "Draw Moment and deflection
diagrams" automate the drawing of the moment and deflection diagrams along the pile
length. The plot of the moment diagram with respect to the pile length is Figure 14.
The results obtained using the CLM for the same example are also presented in the same
figure for comparison. The two methods appear to yield significantly close values for the
moments along the pile length. In essence this validates the developed tool.

164 eJSiE 1(3):142—167

 S Sadek and F Freiha

Moment diagram along the pile length

Moment (MN.m)
-0.05 0.00 0.05 0.10 0.15 0.20 0.25 0.30
0

-2

-4

-6
Depth (m)

-8

-10

-12

-14

-16
Moment diagram according to HAM
Moment diagram according to CLM

Figure 14: An Excel chart showing the moment diagram in a pile along its length

6 Summary and conclusions: academic use and value

In applied engineering, and particularly in the area of geotechnical engineering the
teacher and students are presented with a number of problems where the response and
behavior of the element of interest is complex and very difficult to visualize and explain.
In essence, most problems involving responses conditioned by soil-structure interaction,
fall under that category. Traditionally, the educational process and learning outcomes
associated with these problems have left a lot to be desired. In our experience teaching
deep foundations and the response of piles, the task of tackling the response of piles to
lateral loads and moments has been the most challenging and frustrating. The available
methods, the number of parameters involved and the empiricism embedded in many of
the approaches compounded the difficulties. In response to the above, a new approach
was sought, relying primarily on incorporating the power and functionality of spread-
sheets. The basic idea was to facilitate the teaching process by providing the teacher
with a method based on real-time demonstration of concepts and which also empowered
students to explore the effect of various parameters and variables on the response and
behavior of the piles.
In this paper, the process of development and use of a spreadsheet-based tool for the
analysis of piles under seismic/lateral loading was documented. The significance of the
work presented lies in the reduction of the presentation, comprehension and teaching of
highly complex soil-structure interaction problem to an Excel environment where various
aspects of the analysis can be easily explored, and their importance and effects on overall
pile behavior immediately assessed and plotted. The problem of the seismic design of

eJSiE 1(3):142—167 165

 Seismic Design of Pile Foundations

deep foundations is only one of a number of soil-structure interaction problems where

the application of the analytical and graphing power of Excel could result in an easy-to-
use-and-demonstrate product which enhances the process of learning and teaching.
We have already introduced the approach described in this paper to a class on
geotechnical earthquake engineering at the American University of Beirut in the Fall
2003 semester. The number of students registered for the course was 18 (10 graduate
and 8 undergraduate students). The response to the use of the spreadsheets approach was
very positive, in the form of interested and interesting questions and feedback, students
taking the spreadsheets to generate parametric studies and proposing improvements to
the spreadsheets in form and content. This was from the teaching perspective a marked
difference to the response we had prior to the introduction of this approach in the course
in question in previous semesters when a traditional didactic approach was the norm.
We believe that the most clearly identifiable improvement allowed by the spreadsheet
approach was the adoption of “what if?” scenarios to illustrate the effect and importance
of certain parameters such as soil types, pile characteristics, levels and type of loading,
etc. in real-class-time. The end of semester evaluation comments from some students,
both graduate and undergraduate, included that same point.
In summary, to date our experience with using the spreadsheet tool in teaching
the fundamentals of pile-soil interaction under seismic loading to classes of seniors and
graduate students in civil engineering has been rewarding. The student response to
the exercise presented in this paper has prompted us to explore the adoption of similar
approaches for other civil engineering/geotechnical/structural applications.

References
[1] Bowles, J.E. (1997). Foundation Analysis and design. McGraw-Hill, New York.

[2] Brettmann, T. and Duncan, J.M. (1996). Computer application of CLM Lateral
Load analysis to Piles and drilled shafts. Journal of Geotechnical Engineering,
ASCE, 122(6): 496—497.

[3] Das, B. (1998). Principles of Foundation Engineering. PWS-KENT, Boston.

[4] Davisson, M.T. and Gill, H.L. (1963). Laterally loaded piles in layered soil system,
Journal of Soil Mechanics Foundation Division. ASCE, 89(3): 63—94.

[5] Duncan, J.M., Evans, L.T, Ooi, P.S.K. (1994). Lateral load analysis of single Piles
and Drilled Shafts, Journal of Geotechnical Engineering. ASCE, 120(6): 1018—1033.

[6] Evans, L.T.Jr. and Duncan, J.M. (1982). Simplified analysis of laterally loaded piles.
Rep. No. UCB/GT/82-04, University of California, Berkeley, California.

[7] Matlock, H. and Reese, L.C. (1960). Generalized solutions for laterally loaded piles,
Journal of Soil Mechanics Foundation Division. ASCE, 86(5): 63—91.

166 eJSiE 1(3):142—167

 S Sadek and F Freiha

[8] Matlock, H. and Reese, L.C. (1961). Foundation Analysis of Offshore Pile Supported
Structures. Proceedings of the 5th International Conference On Soil Mechanics and
Foundation Engineering, Paris, 2, 91—97.

[9] Matlock, H. (1970). Correlation for design of laterally loaded piles in soft clays.
Proceedings of the 2nd Offshore Technology Conference, Dallas, Texas, 577—594.

[10] Nair, K. (1968). Dynamic and earthquake forces on deep foundations. ASTM special
technical publication 444 on the performance of deep foundations, 229—250.

[11] Poulos, H.G. and Davis, E.H. (1980). Pile Foundation Analysis and design. John
Wiley and Sons, New York.

eJSiE 1(3):142—167 167