Monday, March 1, 2010

Eccentrically Loaded Bolt Group: Analysis and Design using 'INSTANTANEOUS CENTER of ROTATION METHOD' with VBA for Excel Implementation

A post for Structural Engineers and Designers.

Once in a while I will be posting on this blog topics about engineering and structural design that are relevant to my job as structural designer. In this post I am going to share techniques in bolted connection calculation using MS Excel and VBA Codes (Visual Basic for Application).

There are two methods in the analysis of eccentrically loaded bolted connections presented by AISC Steel Construction Manual, namely:
  2. The ELASTIC Method.
These methods are discussed in AISC Steel Construction Manual and would be redundant if discussion is repeated here.

In designing bolted connections, you may be confronted with both calculation ease and difficulties when you are employing the INSTANTANEOUS CENTER OF ROTATION Method (ICR). The Instantaneous Center of Rotation Method replaces the conservative but more popular ELASTIC Method which has a closed form analytical solution to the strength of the bolt group; meaning, it has an exact formula. The ICR, developed by Crawford and Kulak, is also known as ULTIMATE STRENGTH Method. Compared with the Elastic Method, ICR is found to be more economical since it is based on the ultimate strength of bolts. The only setback that the method has -- is that -- it requires intensive numeric iteration for a solution and cannot be performed by hand alone.

To preclude these difficulties, the AISC Steel Construction Manual devised a table to abbreviate the calculation of 'bolt group' strength. By providing the table, the formula for the strength of Bolt Group is simplified or reduced to Rn = C x rn; where Rn is the strength of bolt group, rn is the available strength of single bolt, and C is the coefficient provided for by the Table that corresponds to the effective number of bolts. With load eccentricity greater than zero, the coefficient C is always less than the total number of applied bolts. The reduction in the efficiency of bolt group is attributed to the increased load from the torque created by load eccentricity that tends to rotate the bolt group about the instantaneous center. Though the table makes the analysis and design of bolts much easier, it has apparent limitations which also make the design more difficult.

If you look at the tables for 'Coefficient C for eccentrically Loaded Bolt Groups' (pages 7-32 To 7-79 in AISC Steel Construction Manual 13th Edition) you will notice that the tables provided are valid only to loadings with angular positions of 0°, 15°, 30°, 45°, 60° and 75°. Other than these load angular positions, a cumbersome linear interpolation or extrapolation has to be performed to calculate the value of C. The bad thing is that the result of linear interpolation or extrapolation may not be correct: 'load eccentricity' to 'bolt group capacity' relation is not linear but a curve.


(Excerpts from MANUAL OF STEEL CONSTRUCTION, Load and Resistance Factor Design, Second Edition)

To use the table, let us consider calculating the capacity of the bolt group in Fig. A.
Fig. A: Eccentrically Loaded Bolt Group
Assume a load P located 16" from the Center of Gravity (CG) of the bolt group. Also take note, by static equilibrium, load P corresponds to the capacity of bolts (or strength developed by bolt group) for the given eccentricity. By inspection, Table 8-19 (Angle = 0°) matches the bolt group configuration of Fig. A. The value for the coefficient C can then be derived readily from the table by locating on the horizontal row of the table the 'Number of bolts in one vertical Row, n' and then moving down along column 'ex' to locate the lookup value for eccentricity. With 6 vertical bolt rows and eccentricity of 16", the value of C is extrapolated from the table to be 3.24. Having determined the value of coefficient C, bolt capacity can then be calculated from the formula Rn = C x rn as discussed earlier.

The example above illustrates the ease in calculating capacity of bolt group with eccentric loading using tables. The drawback in this method, however, occurs when you have a bolt configuration whose eccentricity does not numerically concur with the lookup values on the table or when load angular position is not defined by any of the available bolt coefficient tables. Here are two scenarios:

  • When you have load eccentricity of 16.5” (using the same Fig. A), the value of C can not be readily extracted from the table. It is found somewhere between the values 3.24 and 2.9 that correspond to eccentricity lookup values 16” and 18”, respectively. An interpolation (which happen to be straight line in nature), therefore, is necessary.

  • When you have load with angular position other than 0°, 15°, 30°, 45°, 60° or 75°, there is no way one can solve the value of C using the tables. This is the problem shared by most engineers in the entire engineering community when designing bolted connections.

Fortunately, I have solved this problem long time ago by writing a program in Visual Basic that iterates and determines the correct location of the instantaneous center of rotation of a bolt group. With the IC located, the three equations of in-plane static equilibrium (ΣFx=0, ΣFy=0, ΣM=0) will be satisfied and capacity calculated. The program is based on the load-deformation relationship of bolt denoted by the equation,

R = Rult (1 – e-10Δ)0.55
  • R = nomimal shear strength of one bolt at a deformation Δ, in kips.
  • Rult = ultimate shear strength of one bolt, kips
  • Δ = total deformation, including shear, bearing and bending deformation in the bolt and bearing deformation of the connection elements, in.
  • e = 2.718…, base of the natural logarithm.

Further discussion regarding this equation can be found on pages 7-6 to 7-8 of the AISC Steel Construction Manual, 13th Edition.


Option Explicit

Type BoltInfo
   Dv As Double
   Dh As Double
End Type

'''Effective Bolt Coefficient
Function BoltCoefficient(Bolt_Row As Integer, Bolt_Column As Integer, Row_Spacing As Double, Column_Spacing As Double, Eccentricity As Double, Optional Rotation As Double = 0) As Double
    Dim i, k, n As Integer
    Dim mP, vP, Ro As Double
    Dim Mo, Fy As Double
    Dim xi, yi As Double
    Dim Ri, x1 As Double
    Dim y1, Rot As Double
    Dim Rn, iRn As Double
    Dim Rv, Rh As Integer
    Dim Sh, Sv As Double
    Dim Ec As Double
    Dim Delta, Rmax As Double
    Dim BoltLoc() As BoltInfo
    Dim Stp As Boolean
    Dim J As Double
    Dim FACTOR As Double
    Rv = Bolt_Row
    Rh = Bolt_Column
    Sv = Row_Spacing
    Sh = Column_Spacing
    ReDim BoltLoc(Rv * Rh - 1)
    On Error Resume Next
    Rot = Rotation * 3.14159265358979 / 180
    Ec = Eccentricity * Cos(Rot)
    If Ec = 0 Then GoTo ForcedExit
    n = 0
    For i = 0 To Rv - 1
        For k = 0 To Rh - 1
            y1 = (i * Sv) - (Rv - 1) * Sv / 2
            x1 = (k * Sh) - (Rh - 1) * Sh / 2
            With BoltLoc(n)
                .Dv = x1 * Sin(Rot) + y1 * Cos(Rot) '''Rotate Vertical Coordinate
                .Dh = x1 * Cos(Rot) - y1 * Sin(Rot) '''Rotate Horizontal Coordinate
            End With
            n = n + 1
    Rn = 74 * (1 - Exp(-10 * 0.34)) ^ 0.55
    Ro = 0: Stp = False
    Do While Stp = False
        Rmax = 0
        For i = 0 To Rv * Rh - 1
            xi = BoltLoc(i).Dh + Ro
            yi = BoltLoc(i).Dv
            Rmax = Application.WorksheetFunction.Max(Rmax, Sqr(xi ^ 2 + yi ^ 2))
        Mo = 0:  Fy = 0
        mP = 0:  vP = 0
        J = 0
        For i = 0 To Rv * Rh - 1
            xi = BoltLoc(i).Dh + Ro
            yi = BoltLoc(i).Dv
            Ri = Sqr(xi ^ 2 + yi ^ 2)
            Delta = 0.34 * Ri / Rmax
            iRn = 74 * (1 - Exp(-10 * Delta)) ^ 0.55
            Mo = Mo + (iRn / Rn) * Ri                           '''Moment
            Fy = Fy + (iRn / Rn) * Abs(xi / Ri) * Sgn(xi)       '''Vertical
            J = J + Ri ^ 2
        mP = Mo / (Abs(Ec) + Ro)
        vP = Fy
        Stp = Abs(mP - vP) <= 0.0001
        FACTOR = J / (Rv * Rh * Mo)
        Ro = Ro + (mP - vP) * FACTOR   
    BoltCoefficient = (mP + vP) / 2
    Exit Function
    BoltCoefficient = Rv * Rh
End Function
Please refer to related post, Analysis of Bolts: Modified Visual Basic Code for Calculating Bolt Coefficient Using a Method Developed by Donald Brandt, for the modified version of the above VBA Code employing Brandt's Approach.

To implement the program to do your calculation, you need to copy and embed the VBA Code above to your excel spreadsheet. Here's how:

Step 1:

Open your excel spreadsheet where you intend to embed the VBA code. Here, for discussion purposes, let us use the data on Fig. A for a sample spreadsheet.

From the Tools menu under Macro as shown, click on Visual Basic Editor to activate the VBA Editor.

Step 2:

As soon as the editor is activated, right click your mouse to display a pop-up menu.

From the pop-up menu, choose Insert then click on Module. This will open a VBA module editor window as shown.

On the VBA editor (assuming you have copied the VBA code above), paste the code.

Once copied to the editor, the program is now ready for use.

Step 3:

The function BoltCoefficient defined in VBA code will now be recognized in Excel environment as a user defined function and can be called in any worksheet within the same workbook where the code is embedded. The function is called and applied in the same fashion as any other built-in math functions.

To test our BoltCoefficient function to calculate the value of C given the data from Fig. A, activate or select the cell where you intend to display or place the value; then click the fx button at the left side of the formula bar.

A dialog box will pop-up showing available functions that you may want to use.

The user defined function BoltCoefficient may not be found from the list of displayed functions. You may want to click on the category list box and click on User defined.

The list of available User Defined function will be displayed. Select and click on BoltCoefficient then click on OK button.

The Function Arguments dialog box is displayed showing the function BoltCoefficient arguments that need to be supplied to calculate C. Input box is made available at the right side of argument description. You can specify or supply the arguments by directly typing the value onto the box or by specifying the cell address where the value is located. In our example, I opted to specify the arguments with the address of each cell. Notice that at the right side of the box, the value of cell is also displayed. Also notice that as soon as you supplied all the necessary arguments, the function automatically returns the value 3.549244316. This is the value of the Bolt Coefficient C!

The calculated value of C is the result from the following inputs:
  • Number of Bolt Rows = 6
  • Number of Bolt Columns = 2
  • Spacing of Rows = 3
  • Spacing of Columns = 3
  • Eccentricity = 16
  • Load Rotation = 25°

Finally, after clicking on the OK button, the value returned by BoltCoefficient function is displayed on its assigned cell.

I am using MS Excel 2003. If you are using MS Excel 2007, the pictures, views, pop-up menus and dialog boxes may look different from the samples i've shown.


  1. REDEEM:

    You are a great programmer and designer
    keep up the good work.


  2. Thanks for this great code.
    One thing though...The values in the AISC manual seem to hover around 98% of the values I get from your code. Any Idea why the differences?
    Josh Gionfriddo, EIT
    Structural Engineer

  3. Great code, But I have a couple of the code Line " Rn = 74 * (1 - Exp(-10 * 0.34)) ^ 0.55
    " what is the 74? Also the answers are a little off from the tables, any ideay why.

    I need to study it some more.

  4. Hi Steven... You've got the same predicament with josh. Actually, I planned not to respond to the question while still looking for the answer -- until i got another question from you.

    I did a review on the equation provided for by AISC Steel Construction Manual 13th Edition on pages 7-6 to 7-8 and ran more analyses -- but still the results are a little bit off from the table. Despite the difference, however, the results returned by the code are still correct: the code SATISFIES the in-plane static equilibrium up to 4 decimal places of accuracy. We may throw the same question to AISC why the difference...

    Please give me more time to look into this matter.

    As to your question, 'what is 74?' -- the number is the ultimate shear strength of 3/4-in. diameter ASTM A325 bolt, 74 kips, which corresponds to its maximum deformation of 0.34 inches. You may refer to pages 7-6 to 7-8 of the AISC Steel Construction Manual, 13th Edition for a thorough discussion.

  5. Thanks for the response. I did find the 74 finally. I think That I may have found the reason for the difference in the tables and the results of your VBA code. there is a paper posted on, that may explain the difference. I have th epaper on my other PC at home. I will try and send the excerpt.

  6. Here Is the Article excerpt...Steve

    L.S. Muir, P.E., Cives Steel Company, The United States
    W.A. Thornton, P.E., PhD, Cives Steel Company, The United States

    Finding errors in the presentation of the theory underlying the calculation of the
    instantaneous center calls into question the values in Tables 7-17 through 7-24 presented in
    the AISC Manual (1). Fortunately these values were produced by a program based on
    Brandt’s work (Brandt (3, 4)). Brandt’s procedure never restricts its search for the instantaneous center to the line perpendicular to the applied load and passing through the
    centroid of the connection, though from a programming standpoint this would seem to be the
    most efficient approach. Instead both the location of the instantaneous center and the angle
    of the resultant are allowed to drift off of the values implied by Crawford and Kulak’s theory
    until equilibrium is satisfied. Being based solely on the load-deformation relationship and
    equilibrium, the C-values presented by AISC are correct. The authors have checked
    numerous cases and are satisfied that both the load-deformation relationship and equilibrium
    are satisfied using Brandt’s approach.

  7. Thanks, Steve, for the excerpts...

    To complete the reading, I downloaded the same article written by Larry Muir and Thornton from the AISC website. After reading, I now understand why the differences between the values returned by the VBA code and that of the table provided by AISC Steel Construction Manual. It is all about Crawford & Kulak's method against that of Brandt’s approach. The table is derived using Brandt's while the VBA Code is based on Crawford and Kulak's.

    Having learned that the values in Tables presented in AISC Manual were produced by a program based on Brandt’s work, I will try to write another VBA Code based on the same approach -- on my subsequent blogposts...

  8. Redeemer:

    Were you ever able to produce a VBA code for finding the instantaneous center using Brandt's approach? If so, would you mind sharing it in this post?


  9. Hi Reid:

    Sorry for the delayed reply. I just opened my blog today...

    Yes, i have a VBA code for finding the instantaneous center using Brant's approach; but i need to sort the code out and make some modifications to suit your purpose. In two days period i will be sharing/posting the complete code here.

    In the meantime you may want to download the 'Standalone Alone Program' i wrote that exactly serves your purpose at this site: AISC Steel Tools.


  10. Firstly thank you very much for making your work available Redem; it has been a great help and is a very impressive piece of work.

    A few people have noted that the published AISC values differ slightly from your VBA (hovering around 98%), which is also what I am finding. While this discrepancy is negligible, I think I know what's causing it, so I thought I'd share it with you.

    On page 7-6 of the AISC manual the load-displacement relationship for a 3/4" A325 bolt (considered to be typical) is given by R = Rult(1 - e-10Δ)^0.55 where Δ is the displacement.

    On page 7-8 it is then stated that Rult = 74kip and Δmax = 0.34in.

    However, you'll note that when you plug Δ = 0.34in in to the equation you get:
    R @ 0.34in = 74kip x (1 - e-10x0.34)^0.55 = 72.6kip, or 98% of Rult.

    In actual fact the equation approaches 74kip asymptotically as the displacement tends to infinity.

    After setting Δmax = 0.34in and solving for equilibrium to find the instantaneous centroid, the bolt reactions are all given by the above equation, with the maximum calculated bolt reaction of 72.6kip. You can then define the bolt group coefficient, C as the ratio of the applied action, P (given by the sum of all bolt reactions) to the maximum bolt reaction of Rmax = 72.6kip.

    While this approach is rational, I think that the AISC tables were derived using Rult = 74kip when defining the bolt group coefficient. This results in a slightly lower coefficient (therefore being a little more conservative).

  11. You are correct, Dan. Agree with you... Sometime ago, I bumped into an article from a journal (presumably from AISC? i'm not so sure) about the asymptotic behavior of the equation...

    I will do some tweaks on the ‘code’ based on your findings. Thank you so much...

  12. Have you tried to develop a code that can be used for staggered bolt groups or is something like that way too complex?

  13. Hi,

    I have posted a program for that purpose a couple of years ago at 'AISC (American Institute of Steel Construction) Steel Tools'.

    You may download the program from this link:

    The program is written in VBA and runs in Microsoft Excel. It calculates the coefficient C of a bolt group of ANY GEOMETRY (staggered bolt groups or is something like that way too complex).

    Please note --- the program is now an old version. I intend to post an update soon. You may email me at
    if you want an updated version of the program.

    Regards and God bless...

  14. Hello,
    I downloaded the Excel spreadsheet for eccentric bolted connection with IC method, it is amazing. I was trying to develop an Excel spreadsheet but just I was successful to reach the numbers for vertical P but not with angle. since I am new to this method I gathered informations and found the IC shall be on the line perpendicular to p and go through the C.G of the bolt connection, but I could not get the right numbers.
    I appreciate your help If there is a way to have a look at my Excel file.
    I look forward to hearing from you soon.
    Best regards
    Arash jalalpour

  15. Hi Arash,

    Thanks so much for your appreciation and for finding my Excel Spreadsheet useful to your job. I find it quite inspiring.

    I am currently reviewing your spreadsheet. I will be sending you email soon...



  16. Thank you Redeemer for your excellent article! (Sorry about the long post...)

    I also run into difficulties with eccentric bolt groups, but not with the loading angle. Rather, the problem I run into is the connected materials...

    Typically, I will fasten a steel angle to a concrete slab or column. For a while I was using the AISC "Coefficients C" tables until, one day, my analysis was rejected by a plan reviewer. I was told these tables were not for connecting to concrete.

    I did some digging and was surprised to find that I agreed with the plan reviewer!

    The assumed deformation (delta) of connected plates is surely different from a plate connected to concrete...

    I'm kind of at an impasse in trying to make these tables work for concrete. It seems the tables are conservative if the "Embed depth"/"Steel thickness" ratio is less than 4 (which never happens), and insufficient if the ratio is over 4.

    Do you have any suggestions on modifying the AISC tables to work for concrete?

  17. Hi Cris,

    Like you, I also do calculations for plate-to-concrete connections subjected to either in-plane or out-plane shear load. But, here, I presume you are talking about connection subject to in-plane loading where the ICR (Instantaneous Center of Rotation) method is especially developed for.

    Please take note that in steel to concrete connection (assuming steel angle, as in you case, connected to side of column) there are two levels of resistance that need to be taken into account. One, the fastener or anchor resistance; and two, the base material resistance. Fastener usually refers to expansion anchor, epoxy anchor, and other anchors that maybe mechanically or chemically attached to the base material. Base material, on the other hand, refers to solid concrete, concrete masonry unit, brick, etc -- where the fastener is supposed to be attached to.

    At fastener level, I believe, the ICR method of calculating bolt resistance still applies. Here, at this level, I treat fasteners the very same way I treat bolts when designing ordinary bolted steel connection. Fasteners are still bolts whether they are attached to concrete or steel and they still assume the same load-deformation relationship when subjected to eccentric in-plane shear load -- as long as they are anchored rigidly.

    At base material level, however, the case is totally different. The 'load-deformation' relationship of fastener is no longer in effect. At this level, the strength of connection is governed by the load-bearing mechanism formed by concrete and fastener. The strength of this load-bearing mechanism is a function of several factors, such as concrete compressive strength, depth of fastener embedment, distance of fastener to the edge, and the zone type (e.g. crack zone or non-crack zone) which when combined together result to a complex analysis. The weakest among the possible mode of failures in anchoring to concrete subject to in-plane shear is what determines the capacity of the fastening. This mode can be either Concrete Pry-out or Concrete Edge Breakout.

    Again, the fastener may be analyzed using ICR method and its analysis may be included in your design calculations. However, sooner, you will find out that the strength of the fastener (steel bolt) will ALWAYS not the governing connection strength. Anchorage to concrete is always the weakest. That must be the reason why the plan viewer rejected your analysis (or just wanted it to be removed from your calcs).

    You may need to refer to ACI 318-08 (or the later version) to calculate strengths related to anchoring to concrete. You may also want to visit HILTI website and download their Profis Anchor Software from this link: PROFIS ANCHOR SOFTWARE .

    The Profis Anchor Software will make your life much easier. All you need to do is model your connection, select fastener, apply necessary loading, then execute calculate. The software will tell you whether your design is OK or NOT and will allow you to perform changes and adjustments.


  18. Thank you Redeemer, for your reply!

    I always use the manufacturer's tested data for the strength/capacity of the fasteners in concrete.

    Whereas, I was using the ICR method to determine the worst case fastener loading.

    The load I obtained, using the ICR method, I would then apply to both the "fastener resistance" and the "base material resistance."

    And I believe that is where the plan reviewer's resistance appeared. For whatever reason the plan reviewer did not think the ICR method was a viable method to determine loading in a concrete anchor.

    I just can't see using one method to determine loading in the fastener and another to determine loading at the base material...

  19. Thank you, Mallesh.
    I also got some interesting posts from you here: