“Beam Design Functions” is an Excel spreadsheet providing a User Defined Function (UDF) that will calculate the stresses, strains, forces and moments in a reinforced or prestressed concrete section subject to any specified bending moment and axial load, using an elastic analysis. The only restriction is that the section must be symmetrical about the vertical axis (i.e. the axis perpendicular to the axis of bending). The spreadsheet may be downloaded from: Beam Design Functions.zip

I recently had a request to provide more detail about how to use the spreadsheet, so here it is:

Like most of my engineering spreadsheet programs, the functionality of the spreadsheet is provided by a UDF, called Elastic in this case, which returns a variety of different arrays, depending on the input values. This provides a great deal of flexibility, allowing the function to work directly on the output of a frame analysis program for instance, but for starting out it is better to work with the example set up on the spreadsheet. The procedure is:

- Enter the concrete section details, in the grey shaded range on the Elastic1 Input sheet.
- Enter the reinforcement and prestress (if any) details.
- Enter the applied moment, axial load, and load eccentricity

When the data is entered the depth of the Neutral Axis and the tension face (top or bottom) will appear in the pink shaded range; more detailed output options are described below. The section can also be plotted by clicking the “Redraw Section” button. Sample input data is provided on the Examples sheet, and this may be simply copied and pasted into the appropriate data ranges. The screenshot below shows data for a circular sections, modelled as a series of trapeziums:

Note that:

- The concrete section is specified in trapezoidal layers from the top face, by layer depth, top width, and bottom width.
- Voids may be specified by entering the top and bottom width of the void under B3 and B4.
- The concrete elastic modulus must be entered on the top row, and for any layer with a different modulus to the layer above.
- Reinforcement is specified by depth from the top face, diameter and number. It is assumed to be symmetrical about the vertical axis.
- The steel elastic modulus must be entered on the top row, and for any layer with a different modulus to the layer above.
- Prestressing is specified by the force per strand for each layer. The force should allow for lock-off and friction losses (if applicable), but elastic losses are calculated by the program.
- See the “Contents” sheet for more details about sign conventions, datum for eccentricity of loads, etc.

Having entered the section details and loads, detailed output information is given by the Elastic UDF on the “Elastic1 Out” sheet.

The download spreadsheet has been set up to adjust the input ranges automatically; the ranges are shown in cells C3 and C4, using a rather lengthy formula:

These ranges are then called in the Elastic function, using the Indirect function:

=(Elastic(INDIRECT($C$3),INDIRECT($C$4),’Elastic1 Input’!$A$6,’Elastic1 Input’!$B$6,’Elastic1 Input’!$C$6,C$7,0))

For general use elsewhere it is easier to enter the ranges directly, using the function wizard:

If the input data is entered in the grey shaded ranges on the input sheet it is not necessary to make any changes to the Output sheet, the data shown below will be automatically displayed:

If it is desired to display the results in a different location in the spreadsheet, or in a different spreadsheet, it must be reentered as shown below:

** =Elastic(conc, reo, momin, axin, Optional [eccentric, Out_Index, Units])**

**Conc:**A 6 column range with concrete cross section details**Reo:**A 6 column range with reinforcement and prestress details**Momin:**The applied bending moment**Axin:**The applied axial load**Eccentric:**The eccentricity of the applied load**Out_Index:**An index number controlling the output data, see example output.**Units:**0 for loads and eccentricity in kN and metres, dimensions in mm, stresses in MPa (default), any other number for any consistent units.

To see all the results for any output column the function must be specified as an array function. See here for details: Using Array Formulas

But once again, if you use the shaded input areas on the Input sheet, you don’t need to change anything on the output sheet.

If anything isn’t clear, or you would like more details of anything, please ask in the comments area below.

Pingback: Composite Concrete Beam Analysis | Newton Excel Bach, not (just) an Excel Blog

Pingback: Daily Download 2: SLS design of reinforced concrete sections … | Newton Excel Bach, not (just) an Excel Blog

Hello,

As I understand right now not supported design by Eurocodes?

Will be in future?

Thank you …

metroxx – The calculation is an elastic analysis of strains and stresses, so it isn’t specific to any code. The ultimate capacity under combined axial load and bending can be found in:

http://interactiveds.com.au/software/RC%20design%20functions5.zip for rectangular sections and

http://interactiveds.com.au/software/ULS%20Design%20Functions.ZIP for non-rectangular sections.

RC Design Functions includes Eurocode 2, but at the moment ULS Design Functions only covers the Australian and ACI codes. I will be updating it to Eurocode 2 “real soon now” :)

Also see http://newtonexcelbach.wordpress.com/2012/09/19/daily-download-3-uls-design-of-reinforced-concrete-sections/ for links to posts with more details.

Ok, thank you.

Also I have question about ULS Design Functions for non-rectangular sections. How its wind Neutra X for section?

I mean, that I understand Its find actual Neutral axis from exterior loading?

But what if I want to find prestressed section Moment resistance?

It finds the NA so that the resultant force and moment of the internal stresses is equal and opposite to the applied force and moment. The effect of prestress is included.

More details in a paper linked here:

http://newtonexcelbach.wordpress.com/2008/10/23/reinforced-concrete-section-analysis-6-ultimate-limit-state/

if you are interested.

Thanks mate, would you please clarify if its a cracked section analysis or uncracked.

Regards

Dragon

Dragon – cracked. The concrete stress at the tension face given in the output is what the stress would be if tension in the concrete was included (i.e. E x strain). I guess that’s misleading so I’ll change it in the next version to show 0 when there is tensile strain.

More details of the analysis are given at:

http://newtonexcelbach.wordpress.com/2008/05/30/reinforced-concrete-section-analysis-3/

and the linked posts.

Pingback: what Microsoft think VBA is good for … | Newton Excel Bach, not (just) an Excel Blog