… and non-linear pile properties.
In previous posts I have presented Excel User Defined Functions (UDFs) to analyse a laterally loaded pile, generate soil PY Curves based on the recommendations in the COM624 Manual, and determine the flexural stiffness of cracked reinforced concrete sections for rectangular and circular sections. I have now combined these into one spreadsheet to provide a convenient and rapid analysis of laterally loaded piles, taking account of both soil and concrete non-linearity.
The spreadsheet includes full open source code, and may be downloaded from LatPilePY.zip.
New features in the functions include:
- Soil PY curves may be automatically generated or externally generated curves may be used
- The pile may be divided into any number of sections, each with either a fixed flexural rigidity, or for circular or rectangular reinforced concrete sections, a calculated rigidity, based on the section properties, applied bending moment and axial load, and the concrete tensile strength and modulus.
- The flexural stiffness of reinforced concrete sections is based on the method specified in Eurocode 2, and includes the effects of tension stiffening, loss of tension stiffening, and shrinkage and creep.
Typical input and output are shown in the screen shots below; further details are given in the spreadsheet.
LatPilePY, Input 1
LatPilePY, Input 2
LatPilePY, Input 3
LatPilePY, Output
LatPilePY, Deflection
LatPilePY, Bending Moments
LatPilePY, Shear Forces
Newton Excel Bach, not (just) an Excel Blog 
Hi Doug,
I am a geotechnical engineer, who often read and download excel files from your web site. I found they are great useful and would like to say much thanks to you for providing these excel program files and source codes for free.
I have a comment on this particular “Lateral pile analysis with PY curves” excel sheet program. This program requires an input for soil stiffness as horizontal sub-grade modulus. This stiffness is not a soil property and depends on the size of the loaded area and not easy to estimate.
I am often been asked by structural engineer to provide spring constant (horizontal sub-grade reaction) for a soil layer. As a geotechnical engineer, I can only be able to estimate reasonably an elastic modulus of a soil material based on laboratory tests and field tests (unit kN/m2). This modulus is constant for each elastic material and will not depend on the area of load. As the horizontal sub-grade modulus (unit kN/m3) depends on the area of applied load, it is very difficult to estimate and it is not convenient to use compared to soil modulus.
To my understanding, to overcome this “horizontal sub-grade reaction” problem, in geotechnical engineering analysis, people came up with an approximate method (called analysis of pile in elastic/or elastic-plastic layered materials) that will estimate the horizontal sub-grade modulus automatically by producing a soil mesh and matching deflections between pile (beam on elastic springs) and soil mesh deformation due to forces along pile. This method also takes into account shear interaction between soil layers an so on. Refer to “Pile foundation analysis and design” Poulos & David (1980), you can find discussions on this approximate method.
Another good material is from web site http://geo.verruijt.net/ by Professor Arnold Verruijt. He has provided a source code (in Pascal) that can analyse pile in layered elastic materials subject to horizontal loads. The code file is “pilat.pas” in “CGM.zip”. I have a copy the theory document behind this “pilat.pas” with me but do not know how to send to you as attachment. This theory document is extracted from a book by Professor Arnold Verruijt. I could not remember the book title as I read it 10 years ago, when I was in Uni. Now I only have just few copied chapters about pile analysis from the book and have no idea about its title.
I hope that you would be able to update your sheet that only asks for soil modulus (kN/m2) as input rather than soil horizontal sub-grade reaction (kN/m3). Once I tried to convert the “pilat.pas” to an excel spreadsheet so that I can input soil layers easily and even can update the soil mesh continently to a size that I want. However, I could only understand “the beam on elastic spring”. I could not understand the further more details into generating and solving soil matrix for a soil mesh to obtain interactions between soil layers and pile.
Viet Nguyen
Geotechnical Engineer.
LikeLike
Hi Viet, thanks for your interest and suggestions.
I will certainly have a look at Prof. Verruijt’s site. If you have some electronic copies you are happy to share my e-mail is dougaj4 at gmail. I also have some other soil models that I may add to the program when time permits.
A couple of points about the input of soil stiffness as it stands:
The “spring stiffness” is calculated using the soil properties in the “soils table”, following the recommendations in the COM624 manual. The “initial spring stiffness” is only used for small deflections. For larger deflections the stiffness is based on the soil strength parameters and the “epsilon50” parameter.
If you prefer to calculate your own values you can enter any number of your own PY tables, following the same procedure as used for the first layer in the example in the spreadsheet.
LikeLike
There’s some very useful information on this site, I may need this in my future career in the geotechnical industry.
LikeLike
Nice Spreadsheet! Can you also include option for ‘weak rock’ and Pile head conditions ‘Free’ and ‘Fixed/Rigid’.
Thanks
LikeLike
Pingback: LatPile update | Newton Excel Bach, not (just) an Excel Blog
Khaled – I will look at providing those options when I have some time, but in the meantime:
– Weak rock (or any other behaviour not covered by the current options) can be modelled with a user input PY table.
– The spreadsheet currently assumes “Free” pile head conditions, with or without applied moment. Fixed head behaviour could be modelled by entering a negative moment at the pile head so that the slope at the head was zero (or any chosen value). This could be done by trial and error , or using the back-solver function in Excel.
Also I have just posted an update which fixes a problem if there were two or more user input py curves, so you might like to download the latest version.
LikeLike
Hi Doug,
I tries to use two or more ‘user input PY table’ generated for weak rock but I could not get them to work. Could please show an example where to put (row,column) additional input (PY) table.
Thanks
Khaled
LikeLike
Pingback: 3 year report | Newton Excel Bach, not (just) an Excel Blog
Pingback: Using LatPilePY | Newton Excel Bach, not (just) an Excel Blog
Pingback: Using LatPilePY | Newton Excel Bach, not (just) an Excel Blog
Pingback: LatPilePY 1.02 | Newton Excel Bach, not (just) an Excel Blog
Just checking to make sure, but it seems that with the units you chose to use for input, we could easily change this to lb and inches as long as we stay consistent with that throughout. I think I have some confirmation of this in the example problems you include comparing these results to COM624P. Do I understand this correctly?
Thanks so much for everything on this website!
LikeLike
Frank – see the notes on the “Function Index” sheet.
Yes, you can use any consistent units, provided you specify a fixed pile flexural stiffness. If you get the program to calculate the stiffness of a reinforced concrete pile then you have to use kN and m, with reinforcement diameter and cover in mm, and concrete shrinkage (if specified) in microstrain.
LikeLike
Ahhhh, I missed that line. Thanks!
LikeLike
How do you consider multi-layered soils?
LikeLike
The example in the download file has a multi-layered soil, with three layers. If you need more layers you just add additional columns to “Soil Tables” and adjust the formula in the Output range to include all the specified soil properties.
If you have a problem editing the output formula see : https://newtonexcelbach.wordpress.com/using-array-functions-and-udfs-and-following-links/
LikeLike
hellow, is there any consistency about “Initial stiffness, ki”‘ with depth?
say if we have a 10 m sand layer and we want to priduce P-Y curve for each sigment (10 signemt of 1.0 m each), does the initial stiffness change with the depth?
LikeLike