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:

- The INSTANTANEOUS CENTER OF ROTATION Method; and
- The ELASTIC Method.

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

**R**x_{n}= C**r**; where R_{n}_{n}is the strength of bolt group, r_{n}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.

## BOLT COEFFICIENT TABLE

To use the table, let us consider calculating the capacity of the bolt group in Fig. A.

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 'e

_{x}' 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 R_{n}= C x r_{n}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 = R

where:

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

R = R

_{ult}(1 – e^{-10Î”})^{0.55}where:

- R = nomimal shear strength of one bolt at a deformation Î”, in kips.
- R
_{ult}= 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.

## VBA Code: BOLT COEFFICIENT CALCULATOR

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 Next Next 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)) Next 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 Next mP = Mo / (Abs(Ec) + Ro) vP = Fy Stp = Abs(mP - vP) <= 0.0001 FACTOR = J / (Rv * Rh * Mo) Ro = Ro + (mP - vP) * FACTOR DoEvents Loop BoltCoefficient = (mP + vP) / 2 Exit Function ForcedExit: BoltCoefficient = Rv * Rh End Function

Note:

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.

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

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

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.

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.

REDEEM:

ReplyDeleteYou are a great programmer and designer

keep up the good work.

AMIE MALOBAGO

CIVIL /STRUCTURAL DESIGN ENGINEER

Thanks for this great code.

ReplyDeleteOne 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

Great code, But I have a couple of questions.in the code Line " Rn = 74 * (1 - Exp(-10 * 0.34)) ^ 0.55

ReplyDelete" what is the 74? Also the answers are a little off from the tables, any ideay why.

I need to study it some more. steven.vicha@jacobs.com

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.

ReplyDeleteI 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 of3/4-in. diameter ASTM A325bolt,74 kips, which corresponds to its maximum deformation of0.34inches. You may refer to pages 7-6 to 7-8 of the AISC Steel Construction Manual, 13th Edition for a thorough discussion.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 AISC.org, that may explain the difference. I have th epaper on my other PC at home. I will try and send the excerpt.

ReplyDeleteHere Is the Article excerpt...Steve

ReplyDeleteEXPLORING THE TRUE GEOMETRY OF THE INELASTIC

INSTANTANEOUS CENTER METHOD FOR ECCENTRICALLY

LOADED BOLT GROUPS

L.S. Muir, P.E., Cives Steel Company, The United States

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

THE AISC COEFFICIENTS C FOR ECCENTRICALLY LOADED BOLT GROUPS

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.

Thanks, Steve, for the excerpts...

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

Redeemer:

ReplyDeleteWere 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?

Reid.

Hi Reid:

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

Redeemer

Correct AISC Steel Tools Link: AISC Steel Tools

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

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

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

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

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

ReplyDeleteHi,

ReplyDeleteI 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:

http://www.steeltools.org/Resources/ViewDocument/?DocumentKey=6c293303-eb1d-4a30-a928-73707843ed84

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

redemlegaspi@gmail.com if you want an updated version of the program.

Regards and God bless...

Hello,

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

arashjalalpour@yahoo.com

Best regards

Arash jalalpour

Hi Arash,

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

Regards,

RedemLegaspiJr

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

ReplyDeleteI 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?

Hi Cris,

ReplyDeleteLike 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, thebase 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.

Regards,

Thank you Redeemer, for your reply!

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

Great work buddy!!very useful

ReplyDeleteThank you, Mallesh.

ReplyDeleteI also got some interesting posts from you here: https://plus.google.com/+malleshng