“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 …

LikeLike

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 https://newtonexcelbach.wordpress.com/2012/09/19/daily-download-3-uls-design-of-reinforced-concrete-sections/ for links to posts with more details.

LikeLike

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?

LikeLike

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:

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

if you are interested.

LikeLike

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

Regards

Dragon

LikeLike

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:

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

and the linked posts.

LikeLike

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

Hello,

If I compare concrete pre-stressed section in Beam design functions (Elastic function) and RC Design functions (estress function) then I see different values for Bottom steel stress.

Please see examples:

https://www.dropbox.com/s/9h703x6z7e5inml/Beam%20Design%20Functions.xlsb?dl=0

https://www.dropbox.com/s/6f09934criqb9b4/RC%20design%20functions6.xlsb?dl=0

As I understood if I want to get same value in Beam design functions I need to add initial pre-stress?

Can you please explain this?

Thank you

LikeLike

Hi mettrox,

The two spreadsheets are giving exactly the same results (compare depth of NA and concrete top and bottom face strain for instance), but they are presented differently. In the RC design functions spreadsheet there can only ever be one or two layers of steel, so the stress and strain results include the prestress. In Beam design functions there can be any number of layers and the output summary reports the stress and strain for top and bottom layer only. Because the steel may or may not be prestressed it only shows the strain due to applied loads, and the associated stress. If you add on the prestress and strain you will find the numbers agree exactly between both spreadsheets.

I agree the presentation in the Beam Design Functions is confusing, and I’ll have a look at changing it.

A couple of other points that I will fix in Beam Design Functions:

– The stress results show tension stresses in the concrete, even though the analysis assumes the concrete takes no tension. I will change the output to show zero stress in the concrete for any tensile strain.

– The last result in the Strain results (column 2) is curvature, with units of mm^-1, but the heading just says strain and units of m^-1 (and strain doesn’t have units). I will change the heading and also show the curvature in m^-1 units, as in the other spreadsheet.

LikeLike