The spreadsheet for Ultimate Limit State Analysis of reinforced or prestressed concrete beams, previously presented here, has been combined with the Units4Excel spreadsheet to allow the program to work with any selected units, SI or non-SI. Provision for the American Concrete Institute code ACI318 has also been added. The revised spreadsheet, including full open source code, can be downloaded from ULS Design Functions.zip.
The screenshots below show the input for an analysis of a circular reinforced section in kip and inch units, to the ACI 318 code:
Input with kip and inch units to ACI 318
Concrete and reinforcement details in inch and kip units
Concrete section showing neutral axis position
Interaction Diagram in kip-inch units
Input and output for the same section in kN, mm and MPa units is shown below:
Input for mm, kN, Knm and MPa units
Extracts from the code to combine the unit conversion functions into the analysis code are shown below:
' Generate unit abbreviations for default units, or blanks to allow unspecified consistent units, or read units from spreadsheet
If IsNumeric(UnitsA(1, 1)) = True Then
If UnitsA(1, 1) = 0 Then
ForceUnitA(1, 2) = "kN"
StressUnitA(1, 2) = "MPa"
MomUnitA(1, 2) = "kN.m"
LunitA(1, 2) = "mm"
Else
ForceUnitA(1, 2) = ""
StressUnitA(1, 2) = ""
MomUnitA(1, 2) = ""
LunitA(1, 2) = ""
End If
Else
ForceUnitA(1, 2) = UnitsA(1, 1)
StressUnitA(1, 2) = UnitsA(2, 1)
MomUnitA(1, 2) = UnitsA(3, 1)
LunitA(1, 2) = UnitsA(4, 1)
End If
...
' Generate factors to convert input units to N, mm and MPa
If UnitsA(1, 1) <> 1 Then
If MomUnitA(1, 2) <> "N.mm" Then Momfact = ToSi2(MomUnitA, "N.mm")(1, 1) Else Momfact = 1
If ForceUnitA(1, 2) <> "N" Then ForceFact = ToSi2(ForceUnitA, "N")(1, 1) Else ForceFact = 1
If LunitA(1, 2) <> "mm" Then LFact = ToSi2(LunitA, "mm")(1, 1) Else LFact = 1
If StressUnitA(1, 2) <> "MPa" Then StressFact = ToSi2(StressUnitA, "MPa")(1, 1) Else StressFact = 1
' Apply factors to concrete and reinforcement data
For i = 1 To NumSects
For j = 1 To 5
Conc(i, j) = Conc(i, j) * LFact
Next j
Conc(i, 6) = Conc(i, 6) * StressFact
Conc(i, 7) = Conc(i, 7)
Next i
For i = 1 To NumReo
For j = 1 To 2
Reo(i, j) = Reo(i, j) * LFact
Next j
Reo(i, 4) = Reo(i, 4) * StressFact
Reo(i, 5) = Reo(i, 5) * ForceFact
Reo(i, 6) = Reo(i, 6) * LFact
Reo(i, 7) = Reo(i, 7) * StressFact
Next i
Else
Momfact = 1
ForceFact = 1
LFact = 1
StressFact = 1
End If
...
' Factor results to return to input units
Select Case Out_Index
Case 1
ReDim Umoma(1 To 3, 1 To 2)
Umoma(1, 1) = XA(7, 1) * Phiax / ForceFact
Umoma(2, 1) = XA(9, 1) * Phiax / ForceFact
Umoma(3, 1) = XA(11, 1) * Phiax / ForceFact
For i = 1 To 3
Umoma(i, 2) = ForceUnitA(1, 2)
Next i
Case 2
ReDim Umoma(1 To 3, 1 To 2)
Umoma(1, 1) = XA(8, 1) * Phi / Momfact
Umoma(2, 1) = XA(10, 1) * Phi / Momfact
Umoma(3, 1) = XA(12, 1) * Phi / Momfact
For i = 1 To 3
Umoma(i, 2) = MomUnitA(1, 2)
Next i
...
Newton Excel Bach, not (just) an Excel Blog 
I am trying to find the pdf file explaining the ULS system spreadsheet.
Thanks from Colombia
LikeLike
Luis – see https://newtonexcelbach.wordpress.com/2008/10/23/reinforced-concrete-section-analysis-6-ultimate-limit-state/, or the link for the actual pdf file is https://newtonexcelbach.files.wordpress.com/2008/10/ultimate-moment-capacity1.pdf
The paper gives the derivation of the equations used for finding the depth of the neutral axis. These provide a closed form solution, rather than the iterative process usually used.
If you are looking for more information on the spreadsheet itself you won’t find the paper very useful. There is some documatation on the spreadsheet Contents sheet, but the best way is to try some of the examples included in the file. Just copy the grey shaded data from an example into the concrete and reinforcement ranges on the Umom Input sheet and make sure the axial load is less than the maximum capacity of the section. The Results table on the input sheet, the detailed results on the Umom Out sheet, and the interaction diagram will all update automatically. The cross section diagram on the Input sheet will update when you click the Redraw button.
Any questions or suggestions, please ask.
LikeLike
Hello,
Is there calculation of concrete beam ( or prestressed) deflection?
Regards, Andrejs
LikeLike
Hi Andrejs – yes there are quite a few!
For the beam analysis (simply supported or continuous beam) probably the most convenient is : https://newtonexcelbach.wordpress.com/2011/12/31/continuous-beams-with-shear-deflections/
This allows you to divide the beam into segments with different EI values, so you can handle cracked and uncracked sections (0r yielded sections).
To find the section flexural stiffness, allowing for tension stiffening, shrinkage and creep the best spreadsheet for a rectangular section is at: https://newtonexcelbach.wordpress.com/2010/03/12/tension-stiffening/
For a non rectangular section the spreadsheet here https://newtonexcelbach.wordpress.com/2011/09/07/using-beam-design-functions/
will calculate section curvature for a fully cracked section but you would have to do your own adjustments for tension stiffening etc.
Combining the beam flexural stiffness calculations with the continuous beam analysis is fairly high up on my to-do list, but if you want to do it yourself it would be quite easy beacuse they are all written as UDFs; so you could just calculate the section stiffness using the appropriate UDF, based on the bending moment coming out of the continuous beam analysis, and either paste the values into the input for the beam analysis, or set up a macro to do it automatically.
All the spreadsheets available for download are listed on the Download page. At the moment the list links to the most recent blog post describing what they do, so it’s a bit tedious working through the list if you don’t know what they do, but in the near future (maybe September) I’m intending to go through the whole list and provide a short description of what each does, and group together those that can be used in combination.
LikeLike
Ok, thank you. I will try.
Also I have question, is it possible to calculate voided section? (For example Hollow Core slab).
LikeLike
The spreadsheet at https://newtonexcelbach.wordpress.com/2011/09/07/using-beam-design-functions will handle voids, but it doesn’t do tension stiffening. You can either enter the section as a web with two half voids, or if you prefer you can enter the section between two web centre lines with a central void. For a pre-tensioned hollow core slab it would be fine for an uncracked section but for a cracked section you would have to adjust the stiffness to allow for tension stiffening. I’d suggest the Eurocode 2 method, as the methods based on Branson’s equation in Australian and US codes tend to give much too high stifness immediately after cracking.
LikeLike
Pingback: Daily Download 3: ULS design of reinforced concrete sections | Newton Excel Bach, not (just) an Excel Blog
hai i am dong my final year project (designing strap foundation using excel ) but i face a problem on how can i draw the shear and Th bending moment for the footing using excel so if you can give me any tutorial i can study
best regard
Hamdi
LikeLike
hamdi – I’m not sure if you mean drawing the diagram or calculating the values, but either way it’s a very general question and you need to do some searching first. There is plenty of material on the Web (including on this site) that answers both of those questions. If you have a more specific question, feel free to ask.
LikeLike
Pingback: Concrete ULS spreadsheet update | Newton Excel Bach, not (just) an Excel Blog