Download RC Design Functions (includes full open source code) and EStress Examples.xlsx
This series of posts will cover ways of using the many functions contained in the RC Design Functions spreadsheet, starting with some general comments on using the User Defined Functions (UDFs), and details of usage of the Estress function.
All the functions available in the spreadsheet are listed on the Contents sheet:
There are also over 40 general purpose and utility functions called by the main functions listed above, most of which can also be used as UDFs from the spreadsheet.
The simplest way to use the functions is to enter data in the grey cells on the function input sheet. For instance for Estress:
The resulting 8 columns of output can then be seen on the results sheet:
The results are generated using the UDF EStress:
EStress(Input Range, Axial Load, Moment, Output Column Index, Output Row Index, Prestress, Code, Depth, Ect, Cracked):

Input Range: Single column range, 15 rows as shown in range D3:D17 in the screenshot above.
 Axial Load, Moment: Design actions; see notes
 Output Column Index, Output Row Index: Index values defining output as shown in screenshots above
 Prestress: Single column, two row range with prestress in top and bottom row reinforcement (MPA)
 Code: Design code for crack width calculation; see notes
 Depth: Section depth; overriding value in input range
 Ect: Concrete elastic modulus; overriding value in input range
 Cracked: See Note 12
Notes:
 The functions assume a rectangular concrete section, or T section with the neutral axis within the flange.
 Typical output is shown on the Estress Out sheet (screen shots above).
 Input ranges must be a single column range, with data ordered as shown above
 “Axial load” may be a single value or cell, or a multi cell range, or an array of values
 If “axial load” is a range or an array then the function will return a single column array with one value for each axial load.
 If “moment” and/or “depth” ranges are specified they must have the same number of values as “axial load”.
 If a “depth” range is specified the depth corresponding to each moment and axial load supersedes the value specified in the input range.
 Display of output values is controlled by the output indices, as shown in the examples.
 The optional “code” parameter only affects crack width and curvature output (out1 = 8); Available Codes are:
1: AS3600
2: AS5100
3: EC2
4: BS5400
5: BS8100
6: CEB_FIP  If the “Ect” parameter is specified this supersedes the Elastic Modulus value specified in the input range.
 The input assumes a “top” and “bottom” face with positive bending tending to cause tension in the bottom face.
 If the “Cracked” parameter is specified as True (or 1) the concrete is treated as cracked for curvature calculations under all load conditions. The default value is Cracked = False.
The main advantage in writing the routines in this spreadsheet as UDFs is that it offers much more flexibility in use. The section data range, and the list of axial loads and bending moments may be located anywhere, in any open spreadsheet, and similarly the output data can be located in any convenient range. As an example, the screenshot below shows bending moment and axial load data for a concrete arch structure, together with the associated stress in the top and bottom reinforcement layers.
The UDF, EStress, is located in a different workbook to the data, so it must be preceded by the file name. The easiest way to do this is to use the “Insert Function” icon to the left of the edit bar:
The Estress function may then be selected from the User Defined category. The full input for the top reinforcement stress is:
=’RC design functions6.xlsb’!estress($C$4:$C$18,D23,C23,1,2)
The axial load in D23 is specified as compression negative, whereas EStress treats compression as positive; the cell D23 is therefore preceded with a minus. C23 is the bending moment, and the final two inputs specify stress output (1), and the top steel layer (2).
The same function is used for the bottom reinforcement stress, with the final 2 replaced with a 3. Note that the section data is entered as an “absolute” address (with $ signs), so that it does not change when the function is copied.
Having entered the functions for the top and bottom steel stress, and copied down over the full list of results, the results may be plotted as shown below:
On the next sheet the example shows how reinforcement that varies along the length of the arch can be dealt with. The reinforcement is defined in the four columns: C4:F18. The four columns are named: Full_Length, Add_Top, Add_Bott, Add_Both.
The Indirect function is used to select the appropriate data, as specified in the range E23:E86:
=’RC design functions6.xlsb’!estress(INDIRECT(E23:E86),D23:D86,C23:C86,1,2)
In this case the function has been entered as an array function, with the full list of axial forces (D23:D86), bending moments (C23:C86), and reinforcement types (E23:E86), rather than just the top cell. The function returns values for all 64 rows of the input ranges, and must be entered as an array function (press CtrlShiftEnter; see Using Array Formulas for details).
The resulting graph is shown below; note the reduced stress in the regions with additional reinforcement.
The third example illustrate how a section with varying depth can be analysed, without entering the full section details for every cross section. In the screenshot below the section depth has been added in Column C:
=’RC design functions6.xlsb’!estress(INDIRECT(F23:F86),E23:E86,D23:D86,1,2,,,C23:C86)
The range C23:C86 specifies the section depth (in mm), and this overrides the value specified in the section data range. The function again must be entered as an array function.
The examples shown above may be downloaded from EStress Examples.xlsx. Note that the file RC Design Functions6.xlsb should be in the same folder as the examples file, and should be opened first. Estress Examples.xlsx should then be opened, and will link to the RC Design Functions automatically.
Hello,
1) I think it would be good if there will be possible to imput section parameters (such as Inertia etc.) what will make possible to calculate more complex sections? Not only rectangle?
2) How possible to find real deflection? UDF gives us curvature, but real deflection depends on load type (point load, distributed load etc.) ?
LikeLike
Also I think would be great if there will be possible to input more layers of reinforcement with different diameters.
LikeLike
See Beam Design Functions and ULS Design Functions for analysis of more complex cross sections with any number of layers of reinforcement, but for rectangular sections with 2 layers these functions are simpler to use.
For deflections use Macaulay2.xlsb for continuous beams, Frame4.xlsb for 2D frames or 3DFrame.xlsb for 3D frames.
LikeLike
I will take a look. Is there support of EC2 ?
About deflection. I talk about cracked section deflection.
LikeLike
For the effective stiffness for deflection calculations you want the beam design functions spreadsheet. At the moment it will give you the curvature for a fully cracked section. You would have to do your own correction for the tension stiffening effect, according to the Eurocode.
LikeLike
Ok, thank you. Will make some tests.
LikeLike
why is give curvature, not stiffness? for deflection shouldn’t you calculate cracked section stiffness (with or without tension stiffening – its code addition) and in static analysis define beams with calculated EJ in cracked regions and full stiffnes in uncracked?
LikeLike
hadwoau – Stiffness (EI) = Moment / Curvature (M/C), so it’s easy to calculate your own value, but effective E (including tension stiffening and shrinkage effects) is given in output column 8, row 10, and Ig (uncracked 2nd moment of area, including transformed steel I) is given in output column 7, row 10.
I’ll go into more detail on the output from the function in the next post on the subject.
LikeLike
but when I was checking those things in your spreadsheet EJ=M/C is vaild only without axial force.
I cheked simple rectangular RC section with hand calculation and my hand calulated EJ equals EJ counted by your spreadsheet but it does not equal M/C if there is axial force.
LikeLike
The axial force is applied at the centroid of the concrete section, but if the section is cracked, or the reinforcement is not symmetrical, then the centroid of the reaction force is not at the centroid of the concrete section, so if the applied axial load is not zero this creates an additional moment.
If the axial force = N, applied moment = M, depth of NA from top face = Dna, and depth of section = D, then the total applied moment about the neutral axis =
M + N(Dna – D/2)
If you use that revised moment you should find that M/C = EJ (where J = 2nd Moment of Area), for any value of axial load.
LikeLike
Pingback: Using RC Design Functions – 2  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
Pingback: Solving nonlinear equations with two or more unknowns – 1  Newton Excel Bach, not (just) an Excel Blog