ULS Analysis of Concrete Beams; now with added units

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"
            ForceUnitA(1, 2) = ""
            StressUnitA(1, 2) = ""
            MomUnitA(1, 2) = ""
            LunitA(1, 2) = ""
        End If
        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
        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
This entry was posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

10 Responses to ULS Analysis of Concrete Beams; now with added units

  1. Luis Barragan says:

    I am trying to find the pdf file explaining the ULS system spreadsheet.
    Thanks from Colombia


  2. dougaj4 says:

    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.


  3. metroxx says:

    Is there calculation of concrete beam ( or prestressed) deflection?
    Regards, Andrejs


  4. dougaj4 says:

    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.


  5. metroxx says:

    Ok, thank you. I will try.
    Also I have question, is it possible to calculate voided section? (For example Hollow Core slab).


    • dougaj4 says:

      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.


  6. Pingback: Daily Download 3: ULS design of reinforced concrete sections | Newton Excel Bach, not (just) an Excel Blog

  7. hamdi says:

    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



  8. dougaj4 says:

    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.


  9. Pingback: Concrete ULS spreadsheet update | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.