Beam Analysis Spreadsheet


The "BeamAnal" calculates Shear Force, Bending Moment and Deflection at 31 positions along the member length. Member Lengths can be a single span simply supported or a 2, 3 or 4 span continuous over middle supports. The analysis results are produced in a tabular form and are also plotted in 3 graphs for rapid comprehension.

Loading the Template on to your Computer
BeamAnal is supplied as an Excel 97 Template, having .XLT as its filename extension.

To load BeamAnal on to your computer, copy this file into Microsoft Office folder for its Templates. Generally the path to this folder in Excel 97 is: C:\Program Files\Microsoft Office\Templates

If you are using Excel 2000, the path to this folder is: C:\Windows\Application Data\Microsoft\Templates

To load and use the Template in Excel 97 or Excel 2000, choose: File, New and the select the file BeamAnal001112-

If you receive an Excel Warning about running Macros and are prompted for whether to load them, answer YES to Load and Enable Macros. BeamAnal incorporates VB Macros and to allow your computer to use them is vital for its operation.

Using BeamAnal
After describing Job Information in the heading part, describe data in 4 parts. Shaded cells in each part indicate locations for the user input. In part 1, describe member properties. In part 2, define loads by giving their magnitude, position and load factors. In part 3, describe the number and position of middle supports. In part 4, choose whether the results are to be factored or un-factored.

Method of Analysis
The program uses usual equations for Shear Force, Bending Moment and Deflection equations along the member span. When middle supports are specified, simultaneous equations are set up and solved to calculate the middle support reactions.

The origin or reference point for defining position of loads and locations for analysis is the left supports. This is so even when there are middle supports to make the member length a continuous beam of more than one span.

The program internally works in consistent Force and Length Units. To help comprehend results, use of mixed units is allowed. Inertia and elastic modulus of the member section can therefore be defined in any units. Similarly, any desired units can be set deflection values.
To specify units, go to the Units sheet and describe your own units of Force, Distance, Inertia, Modulus and Deflection. You need to calculate and specify conversion factors from consistent units to your chosen mixed units. Sample values are given for your guidance in this sheet.
The units cannot however be mixed in one project file. Chosen units apply to all beams in the file. This means that if units are changed in the middle of building up a data file, beam properties for all beams need to be re-defined to match the chosen units.

Middle Supports
Each beam can have up to 3 middle supports. User has to click the appropriate button at the bottom right of the sheet. Specifying 1, 2 or 3 middle supports signify 2, 3 or 4 span continuous beams respectively.
After specifying 2, 3 or 4 mid supports, the position of each support can be changed to any exact value by typing it in the cell for Distance a.
Initially, the Template positions the mid supports by dividing the span into equal distances. User can change these positions to any desired value.

Load Factors
Applied loads can be Factored or Un-factored. Factored means multiplying each load with its corresponding factor in the Load Factor column. Un-factored means multiplying each load with a factor of Unity.
Applied Loads:
Up to 28 loads can be applied on the member length. Each load can have an independent Load Factor for multiplication in the Factored Load Analysis. Nine type of loads can be applied. These loads are shown in the LoadType worksheet.
Support Reactions
The support reactions are printed at bottom right of the Analysis sheet. Negative sign signifies that the support reaction is acting upwards.
Decimal Places
At top right of the Analysis Sheet, the number of decimal places can be defined for Distance along the member length, Shear Force, Bending Moment and Deflection. The number of decimal places can be any value so long as the results fit within the column width.

Database of Member Details and Loads
In each BeamAnal file Member Details and Loads are stored for 500 members. Each members is referenced by a number in the range 1001 to 1500. All information is kept in worksheet STORE and is available to the user for use in the design processes.
A casual Excel user generally does not need to tweak or amend the STORE worksheet. At the risk of upsetting this information, however, one can benefit by generating and using this information by Copy, Paste and Fill-Down commands of Excel. It is strongly recommended that Cut Command of Excel is not used on this worksheet; use of this command affects the worksheet layout and can disrupt the working of RectCol.
An experienced Excel User can employ various Excel features to tweak or manage this information. For example, reactions of all beams can be assembled for the analysis and design of column supports, span lengths and loads can be generated via Copy Paste and Fill Down features, etc.
Member data stored is Span Length, Inertia, Modulus, Member Reference, Member Description, number and position of supports, Loading Details and user locations along member lengths for analysis.
"Store Beam Data" command button saves the current beam data. "Get Beam Data" button retrieves the previously saved information. In order to save and retrieve member information, the Template uses a Datastore Number. This number for 500 beams (in the range from 1001 to 1500) must be specified to store or retrieve beam data.
PLEASE NOTE that storing beam data does not save the Excel File. To ensure saving data to your hard disk, you must use Excel File, Save Pull Down Menu Option in the usual Windows procedure.

Auto Analysis of Members
A facility is provided in BeamAnal to analyse and or print all beams by a single click of “Auto Analyse/Print” Button.
To use this facility, two columns coloured in yellow to the right of the “Store” worksheet are provided. One column is headed “Analyse Y?” and the other as “Print ?”. The “Auto Analyse/Print” Button is located at the top of these two columns in the “Store” Worksheet.
Entering “Y” in the “Analyse Y” in these columns, signifies that the beam in its row need to be analysed and or printed. Leaving the cells blank signifies that respective beams are not be analysed.
This facility is useful when all or a selected few members are to be analysed and or printed after updating details and loading of beams in the STORE worksheet.
Reading Analysis Results from Graphs
Plots of Shear Force, Bending Moment and Deflection are shown in 3 separate diagrams. Moving the mouse pointer over these diagrams displays the exact magnitudes of Shear, Bending Moment and Deflection at any desired point on the span.
To ensure that this feature is enabled in Excel 97, go to Tools, Options, Chart and click Chart Tips and Show Values.


Beam Analysis Spreadsheet

Become a Member to see this content



Please enter your comment!
Please enter your name here