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 and output for the same section in kN, mm and MPa units is shown below:

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 ...

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