This post presents an Excel User Defined Function (UDF) to carry out the analysis of vertical piles subject to horizontal loads, using the same approach as employed in the DOS program COM624. A spreadsheet including the UDF and an on-spreadsheet solution using the same method may be downloaded from LatPile.zip. As usual the file contains full open source code for the UDF and associated routines.
Detailed background information on the method of analysis is given in the COM624 manual, which can be downloaded from the link given in the previous post. An outline of the method, including all relevant equations used, is given below and in the download file, together with some simple examples.
The equations used in the analysis are shown in the screenshot below (click on any image for full size view):
LPile Finite Difference Equations
On the FinDiff sheet of the download file these equations have been entered into a 25×25 matrix, which with the MINVERSE() and MMULT() functions allows the equations to be solved and the deflections calculated for any pile with 20 segments:
Finite Difference Equations in Matrix Format
The same basic method of analysis has also been incorporated in a UDF, allowing the solution to be carried out much more conveniently and flexibly. Input for the UDF is shown below:
LPile UDF
Example 1 is a 20 m long pile of uniform stiffness in a uniform soil:
UDF Input and Results; Example 1
Example 1 Bending Moments
Example 1 Shear Forces
Example 1 Deflections
Example 2 is a reinforced concrete pile with a cracking moment of 60 kNm in a soil with stiffness increasing with depth. The pile and soil stiffness values have been adjusted by hand in this case. Future versions of the program will allow input of non-linear soil and pile section stiffness.
Example 2 Input and Results
Example 2 Bending Moments
Example 2 Shear Forces
Example 2 Deflections
Newton Excel Bach, not (just) an Excel Blog 
thank you very much, i will study this program within couple of weeks. hope to be useful to you someday.
Ziad
LikeLike
Pingback: P-Y Curve function « Newton Excel Bach, not (just) an Excel Blog
Pingback: Beam on Elastic Foundations Analysis « Newton Excel Bach, not (just) an Excel Blog
Pingback: Lateral pile analyis with PY curves … « Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 7: Lateraly loaded pile analysis | Newton Excel Bach, not (just) an Excel Blog
I am bit lost, is there is manual that one can follow or instructions.
LikeLike
Nadeem – have a look here:
https://newtonexcelbach.wordpress.com/2011/07/09/using-latpilepy/
Any questions, please ask.
LikeLike
When I edit the amount in the loads section the whole model gets an error where formulas have been placed. For example my figures in the X, Deflection, Moment and Shear Force columns change to #NAME? and therefore all the graphs do not show.
Any help would be great. I am using Excel 2007.
LikeLike
Hi,
I have found your posts about your LatPile excel files very interesting.
I know it’s a bit old and you have done a very nice job expanding the file to allow non-linear soil responses. However, I really like this first version, because of the worked out matrix in the FinnDif sheet.
I might have missed some correspondence so i don’t know if it has already been adressed. In this first version, I’ve spotted 2 bugs in the ‘LatPile’ function:
– after ReDim-ing your variables, you have: SupportA(1, 2) = SupportDef(1, 1) * SegLength
this should be: SupportA(1, 2) = SupportDef(1, 1)
– and after placing the last elements in the FDA matrix, you have:
FDA(i + 3, i – 1) = 1
FDA(i + 3, i) = -2 + AxLoad * SL2 / EIA2(i – 1)
FDA(i + 3, i + 1) = 0
FDA(i + 3, i + 2) = 2 – AxLoad * SL2 / EIA2(i – 1)
FDA(i + 3, i + 3) = -1
It is not SL2 that should be used, but SegLength (SL1)
The excel worked out function (your verification) is correct.
You can notice the effect of it when selecting a long piles and having a lateral load applied. The excel output presents a correct output (constant pile tip deviation).
Another detail regarding VBA, using worksheetfunction function can shorten the code:
Dim FDA_invert As Variant
FDA_invert = WorksheetFunction.MInverse(FDA)
SResA = WorksheetFunction.MMult(FDA_invert, LoadsVector)
this avoids the making of GESolve
Best regards,
Fredrik Vantomme
LikeLike
Thanks for the feedback. I agree on both points and I will upload a corrected version.
The later versions of the spreadsheet (Latpilepy) use a different routine, based on a “beam on elastic foundations” rather than point spring restraints. The new version gives near exact agreement with results from a commercial FEA program (Strand7), so I am reasonably confident that is working OK.
Regarding using the worksheet functions to solve the matrices, the GESolve routine is taken from my frame analysis spreadsheet, where it gives much better performance than MInverse, and allows much bigger matrices to be solved (especially with older versions of Excel). It doesn’t make much difference with the LatPile routines, but as I have it available, I may as well use it.
Thanks again for the comments.
LikeLike
Pingback: Kapasitas Lateral Tiang dengan PY Analysis (Metode Beda Hingga) – James's Files