This blog has many posts on analysis of 2D and 3D frame structures in Excel (see Frame Analysis Downloads), but this is the first on performing finite element analysis of a 2D continuum.  The file PlateMC.xlsb contains a simple example using plane strain plate elements to analyse a soil slope, assuming Mohr-Coulomb plate properties, and includes full open source code.

The example is based on Fortran code taken from Programming the Finite Element Method by Smith, Griffiths and Margetts (which book I recommend to anyone interested in programming FEA, especially for soil-structure interaction problems).

Input is shown in the screen-shot below:

The spreadsheet is set up to generate a mesh for a soil slope analysis, with input cells shaded light blue, consisting of:

• The width of the top surface, sloping surface, and lower surface respectively
• The height of the slope and the soil below the bottom of the slope
• Up to 5 different soil properties, defined by friction angle (phi), cohesion (c), dilatency angle (psi), density (gamma), Young’s modulus (e), and Poisson’s Ratio (ro)
• A series of up to 12 reduction factors, which are applied to the phi and c values
• Tolerance and maximum iterations values.  Note that the VBA code performs about 10 iterations per second, so for trial run purposes the maximum iterations should be reduced to keep the calculation time reasonable.
• Element property types (ignored if only one property is defined). Elements are numbered from left to right and top to bottom, with a total of 350 elements.

It is also possible to change the number of elements in the X and Y directions.  The analysis will be carried out correctly, but in the current version the mesh will not plot correctly.

Having entered the required data, click the “Run Analysis” button, and the program will calculate deflections of the slope for each listed increment of the reduction factor.  For conditions close to failure the analysis will require a large number of increments to converge, so for trial purposes the Maximum Iterations value should be reduced to a small number, so that an approximate result for the reduction factor that causes slope failure can be found in a reasonable time.

Typical results are shown below:

Note that it is possible to plot the deflected shape for any increment number, with any required deflection magnification value.

There is also a plot of maximum deflection against reduction factor, showing that the slope is close to failure at a reduction factor of 1.6:

The current version of the spreadsheet will be developed over the coming weeks to provide more flexibility and better performance, including:

• Graphics to update to modified geometry and plot to equal horizontal and vertical scale.
• Modified input to allow input of any mesh shape.
• Staged construction with addition and removal of elements.
• Alternative soil models.
• Add beam elements.
• Add output of strains, stresses and forces, as well as deflections.
• Compiled code for better performance.