Newton Excel Bach, not (just) an Excel Blog

Download Beam1.zip

This is the first of a series of posts in which I will develop a structural frame analysis program operating within Excel.  The first few stages will be mostly spreadsheet based, with later stages using VBA routines to analyse data generated in the spreadsheet, and ultimately linking to external dll programs to do the heavy number crunching involved in the solution of complex frames.

This post will look at the analysis of a single 2D beam element subject to any combination of transverse and and axial loading, with any three of the six end freedoms restrained.  This will introduce the use of the stiffness method, setting up of a beam stiffness matrix and a matrix of end actions, and solution of these matrices using the Excel matrix functions.

Future posts will:

• Modify the beam analysis to deal with beams inclined to the global loading axes.
• Combine and solve the stiffness matrices for a number of straight continuous beams.
• Form and solve the stiffness matrix for a simple 2D frame.
• Convert the spreadsheet solution to a VBA routine suitable for solving large and complex 2D problems.
• Add provision for members with end moment or translational releases.
• Convert to 3D frames.
• Add provision for plotting of frame geometry, including applied loads, and plotting of analysis results.
• Convert the VBA analysis routines to a Fortran DLL, for improved performance.

As always, all the files presented here will be available for download, and all my code will be open source.  In addition to Internet resources, which will be referenced where applicable, the main sources of background information are:

• Programming The Finite Element Method by I.M. Smith and D.V. Griffiths
• The Reinforced Concrete Designer’s Handbookby C.E. Reynolds and J.C. Steedman

The essence of the “stiffness” method of frame analysis is to set up a series of equations:

Kw = f

where K is a square matrix representing the “stiffness” of the frame, that is the deflection of the frame when subject to unit load; w is a column matrix representing the deflections of each node of the frame; and f  is a column matrix representing the loads applied to the nodes of the frame.

If K and f can be generated for any specified frame subject to a known set of loads, the deflections of each node can be found using the relationship:

In this post the “frame” will consist of a single horizontal beam, and the matrices will be formed and solved on the spreadsheet, with the aid of a User Defined Function (UDF) to generate the applied nodal loads from the specified distributed and point loads and moments, that may be applied to any part of the beam.

The required input is shown in the screen shot below:

• Beam length
• Beam cross sectional area
• Beam second moment of area
• Beam Young’s Modulus
• Applied loads: distributed transverse loads, point transverse or longitudinal loads, and point moments
• Restrained end freedoms, three of vertical or horizontal deflection or rotation at either end

Beam properties and applied loads

The nodal actions required for the analysis are the “fixed end” moments and reactions due to the applied loading, that is the end moments and reactions forces that would be generated by the applied loads if the ends of the beam were fully fixed against rotation and translation.  These are calculated by the UDF “FEMACT()”, which has as inputs the beam length, and the thre tables: distributed loads, point loads, and moments.  This UDF will be described in greater detail in a later post.  The output of the UDF is shown in the screen shot below:

The formation of the beam stiffness matrix, and the matrix coefficients for the example beam are shown in the screen shot below.  The beam stiffness matrix coefficients are derived from the standard beam slope deflection equations, combined with the application of Hooke’s law for axial loads.  Further details of the derivation are given in many structural analysis textbooks.

For the purposes of finding the magnitude of the unrestrained freedoms the beam stiffness matrix is reduced to 3×3 by extracting only the rows and columns related to the unrestrained freedoms, as shown in the screen shot below.  This matrix is then inverted, using the Excel Minverse function, then multiplied by the applied loads at the unrestrained nodes, using the Mmult function.  The result is a column of 3 cells giving the translations or rotations at the unrestrained nodes.  The final stage is to multiply the full original stiffness matrix by the full deflection column matrix to derive the nett force at each node.  The reactions at each node are then found by deducting the applied loads (i.e. the “fixed end” actions”).  It can be seen from the example output that the reactions at the unrestrained nodes are zero, and the reactions at the fixed nodes are equal and opposite to the applied loads, as would be expected.

Inverted stiffness matrix and analysis results