Release Notes - NSBA Splice Spreadsheet - Plate Girder NOTE: Cell callouts listed below may not necessarily align with current state of spreadsheet as rows and columns may have been added and subtracted in subsequent versions. This spreadsheet contains no macros and calculations are all sequentially performed using Excel functions. Althought the sheets are protected, they are not password protected. Please send all comments and questions to "nsbaresources@aisc.org". LIMITATION: This spreadsheet does not currently check AASHTO Article 6.10.1.8 - Tension Flanges with Holes. You are responsible for checking this separately. LIMITATION: Although you can enter two rows of bolts for flanges, the spreadsheet may not arrive at a layout that satisfies the requirements for seal. Currently the spreadsheet assumes flange splice plates are flush with the outside edge of the flange and then sizes the plates and performs the layout from that point. In some cases, the gage between bolt groups may be larger than is necessary and greater than the maximum for seal. Pending an update, in these instances, it is advisable that you adjust the suggested layout by decreasing the gage between bolt groups and recheck net section and block shear outside of the spreadsheet. This occurs more frequently in cases where an analysis is performed of an existing rolled beam bridge splice. Version 3.16 - March 2024 1 - When performing bearing checks for flanges, the spreadsheet was not accounting for filler plates when it calculated the bolt capacity. New variables topFlangeFillerReduction and bottomFlangeFillerReduction added (Cells B343-350 and B354-361). 2 - Per AASHTO LRFD Article 6.13.2.7, a lap splice tension connection greater than 38.0 in. in length, the nominal shear resistance, Rn, is taken as 0.83Rn. As this spreadsheet is currently implemented, the reduction cannot be determined until the bolt count for strength limit state has been determined and the bolt layout has been created. However, since this check cannot happen until after the initial bolt count and layout has been performed, there is currently no way to force a recalculation of bolts without a circular calculation. The follow approach has been taken: Added two levels of checks. The first will display "Notice" for “Joint Length Reduction Factor - Check” an indicate that the joint reduction is needed. The second is a check if the bolts needed to accommodate the reduction is greater than or equal to those needed for strength limit state or the user entered override value if it is enabled. If additional bolts are needed, the “Long Joint Bolt Count - Check” will display “NG” and the design requires user intervention at that point. To resolve the error, the user might be able to adjust the bolt spacing or other geometric properties on the input to shorten the splice and eliminate the need for the reduction all together. However, if that is not sufficient or practical, the user must go into “Bolt Count Override” mode on the “Input” sheet for the flange under consideration and increase the bolt count to match the count needed to accommodate the long joint reduction. The status of these new flags and checks can be found on “Design Check Summary” sheet. An echo of the bolts necessary for the long joint, the bolts needed for strength and user override bolt counts are also shown on this sheet for reference. These are intended to be helpful in resolving the long joint error. If the user has a long joint and additional bolts are needed, the "Status - Error Count" on the "Input" sheet will also display an error. The user should then switch the “Count Override Status” for the affected flange to “User Specified” on the ”Input” sheet and then increase the number of bolts manually until the warning is extinguished. Version 3.15 - March 21, 2023 1 - Updated contact information on EULA sheet. Fixed print layouts. 2 - Corrected Controlling Fu check in web Bearing Resistance Check. Formula was incorrectly multiplying the splice plate Fu by 2 when conditions were met (Cell C206). 3 - When calculating the layout for the inner flange splice plates, the spreadsheet had previously rounded values to 1/8th inch using the ROUND function. This potentially could cause the resulting plate width to encroach on the user specified web weld and web weld clearance values. ROUND has been replaced with ROUNDDOWN to the nearest 1/8th inch. This will ensure that the plate is not sized-up causing a clash with the clearances. These functions are only used to make resulting numbers "cleaner". Once the inner plate width is established to an 1/8th inch, the edge distance is calculated based upon the remaining usable width once the bolt holes are located. Note that the bolt hole spacing is not altered from what is input. The user is advised to review all dimensions and clean them up further if they feel thet dimensions have a higher accuracy than what is really achievable in reality or even necessary. Note that "Net Section Fracture Resistance" checks, for example, are sensitive to the plate cross section area and rounding down may result in a plate that is narrower. In that case you can adjust your inner plate thickness or you can reduce your web weld clearance which would result in the inner plate being wider. 4 - Added new input fields for effective deck width and concrete strength to for checking that flange + Hw forces is greater than Whitney stress block. If a composite assumption results in Flange force + Hw greater than Whitney stress block, the design has exceeded the deck capacity and should redesign as non-composite. Changes made to Input, Design Check Summary and Flange Splice Design sheets. New error field "Slab Strength Status" added. Version 3.14 - December 10, 2021 1 - Splice design reference guide updated and posted. 2 - Corrected calculation of moment arm in flange splice design. Formula was not properly choosing the controlling side of the splice and could result in an incorrect value (Cells C74, C75 and C144). 3 - Corrected version number in print footer. Version 3.13 - November 19, 2021 1 - Corrected cell indicating controlling Hw (Cells C141 and C149). 2 - Added independent transverse stiffener spacing input fields for the left and right web. 3 - Added input field for longitudinally stiffened left and right webs. 4 - Added new error/design check for case where longitudinally stiffened does not have proper transverse stiffening. Although the design will complete and provide bolt counts and layouts, an error will be reported and the design should not be considered valid. User needs to revisit transverse stiffener spacing they entered and correct it as necessary. 5 - Modified "Web Shear Strength" calculations. Added check of AASHTO Eq. 6.10.9.3.2-1 and use Eq. 6.10.9.3.2-2 or Eq. 6.10.9.3.2-8 depending on the result. Reorganized related calculations (Cells A39 - H53). 6 - Added Grade 50CR to the material table and pick list. Version 3.12 - April 22, 2021 1 - Corrected Strength I - Positive coefficients DC1, DC2 and DW. Each was incorrectly referencing future wearing surface in logic check. (Cells B10-D10). 2 - Corrected custom footer in printouts to reflect current version number. 3 - Corrected calculation of Gross Path Length per Failure Plane - Tension for web splice. Was not accounting for case where rows were greater than 2 (B152). Version 3.11 - February 12, 2021 1 - According to AASHTO/NSBA Collaboration G12.1 Guidelines to Design for Constructability, for a web thickness change of 1/8 in., use a 1/8-in. fill on one side of the web rather than 1/16-in. fills on each side. Otherwise specify two fill plates. Added new quantity values to account for filler plates over 1/8 in. in web calculations and summary results (Cells I40, J40 and K40). Version 3.10 - September 28, 2020 1 - Corrected the moment arm used in calculation of Hw in the slip resistance Service II positive moment (cell B377). This corrects a regression error introduced in v3.08. 2 - Corrected flange net area calculation to use proper standard hole diameter (Cells D35 - D38 and B170 - B173). Version 3.09 - July 16, 2020 1 - Filler plate thickness not being calculated correctly and was only being added when the thickness difference was greater than 1/4 in (Cells D49-52, G49-52 and I40). 2 - Added new column Filler Thickness - Total (in) and corrected check when calculating reduction factor R (Cells C63 and C65). Version 3.08 - April 24, 2020 1 - Corrected logic check in web splice bearing resistance checks where the check would never arrive at a FALSE since it was performing a logical AND of the same comparison. Cell G213 was also not referencing the correct input cells (Cells G208 and G213). 2 - Web Slip Resistance Checks were not accounting for Hw in cases of deck casting and positive shear. Also corrected negative shear to reflect the correct Hw (Cells B234, B235, B243, B244 and B253). 3 - Slip resistance Service II - Positive Moment Check moment arm being used in moment calculation incorrect (cell B375). 4 - Condition check not correctly referencing Hw for Positive, Negative and Deck checks (Cell C387 and C397). 5 - Added new "Controlling Hw" to web slip checks and initial bolt count estimates. If there is or are resultant(s) from flange design, use the whichever Hw is governing for Service II slip checks and initial bolt count estimate (Cells D62 - G67 and B245 and B255). 6 - Minor formatting corrections. Version 3.05 - August 2, 2019 1 - Corrected Service II - Negative Moment Check and Deck Casting Check to use the minimum top flange and bottom flange bolt count in checks (Cells B382 and B392). 2 - Corrected Bolt Count - Summary (Initial) calculations to include sqrt of the sum of the squares as was being performed in E66 (Cells E64 and E65). 3 - Corrected Bolt Count - Summary (Initial) Hw which should be per load case and not just the maximum of all Hw (Cells C62, C64, C66). Version 3.04 - May 8, 2019 1 - Corrected Block Shear Rupture Resistance - Girder (Mode 1) gross path calculation for flanges. Gross path did not account for cases where there were more than 4 rows of bolts in the flange (Cells B224 - B227). 2 - Corrected bolt count calculations in web splice which were not returning the right result when an odd number of rows was specified (Cells E54, G62, G64, and G66). 3 - Corrected bolt count calculations in flange splice which were not returning the right result when an odd number of rows was specified (Cells F63, F65, G73, G74 and G75). Version 3.01 - April 23, 2019 1 - Added ability to override the calculated bolt counts for flanges and web from the "Input" tab (Cells F38 - F40). If "User Specified" is selected for "Count Override Status" on the "Input" tab, the user can now enter a value under "Bolt Count - User Specified" which will be used for checks and bolt layout calculations. The values shown in the "Bolt Count - Calculated" still represent the bolts counts calculated by the spreadsheet and can be used for comparison. This can be useful in cases where you are checking an existing splice, or in cases where a moment contribution from the web is required to resist slip and the web splice fails to pass the slip checks. In the latter case, the number of additional web bolts can be reduced by increasing the number of flange splice bolts resisting slip of the flange in order to increase the moment resisted by the flange. The user can try adding 1-row to the web splice, set the "Count Override Status" to "User Defined" for the top flange bolt count override and add a value for the "Bolt Count - User Specified" that is greater than the "Bolt Count - Calculated" until the errors go away. Note that "Bolt Count - User Specified" must be greater than the value the spreadsheet would have calculated for strength and also be an even number (for flanges only) greater than "1". "Bolt Count - User Specified" will be ignored an unvisable unless "Count Override Status" is set to "User Specified". 2 - Moved all bolt count related calculations up in the calculation order for flangs and web. A comparison is then performed to find controlling (maximum) count. Calculations and checks are then performed as usual from then on. 3 - "Composite" / "Non-Composite" option was not previously implemented. Updated moment arm calculations to reflect option. 4 - Various other minor bug fixes. Version 2.06 - January 11, 2019 1 - Bolt pattern layout equations were not properly handling case where only 2 rows of bolts were entered for the flanges. Note that cases with narrow flanges may still fail slip check (Cells G81, H81, I81, B89). 2 - Further correction made to issue item #2 from version 2.04 to account for splice plate thickness when calculation web splice plate height. Account for case where inner splice plate thicknesses may be different. The maximum offset is used to set the final web splice plate height. Entering and tightening clearence assumes flange nuts are on the "interior" of the girder and that splice bolts are installed first. Note that celled have been split to allow for top and bottom offset cases (Cells F67, F68, G67, G68, D73, D74 and E73). 3 - Corrected regression issue item #4 made in version 2.04 when calculating total web bolt calculation (Cell B83). 4 - Corrected Entering and Tightening Clearances to match AISC Table 7-15. 5 - For clarity, added "Flange Bolt Rows" callout to flange section image on Figures tab. Version 2.04 - November 1, 2018 1 - Corrected Per Bolt Pt used in web slip resistance check. Value did not including Ks and Kh values (Cells B217, B224, B233). 2 - Calculation of end distance for Web Splice Plate Height Adjustment did not account for the thickness of the inner flange splice plate when calculating the end distance and end adjustment (Cell F67). 3 - Bottom flange transverse gage calculation improperly using top flange bolt count. Corrected variable referenced to "bottomFlangeBoltRowQuantity" (Cell H82). 4 - Removed unnecessary "Even" from formula used to calculate the Total Bolts (Per Side) - Calculated in web splice (Cells E53/54 and B83/84). 5 - Corrected calculation of moment arm Hw in Slip Resistance Check for Service II Positive Moment so that it is measured with respect to mid-thickness of the deck (Cell B362). 6 - Corrected calculation of design yield resistance of each flange to properly use the minimum of effective flange area or gross area in flange design (Cells G35 - G38). 7 - Corrected calculation of negative moment arm in flange design to properly reflect the controlling side (B129). 8 - Total Bolts (Per Side) - Final was using a decimal bolt count when calculating the height (Cell C83/84). 9 - Native fraction display within Excel will not resolve values to least common denominator. Changed display for calculated values to decimal in flange calculations (Cell B285 - B290). 10 - Native fraction display within Excel will not resolve values to least common denominator. Changed display for calculated values to decimal in web calculations (Cell D79/80). Version 2.03 - January 31, 2018 1 - Revised throughout to correct the computation of the horizontal force, Hw, in the web for the case of negative flexure, which can result in an increase in web splice bolts (Cells B134, B372 and B382). An Errata has been issued to Articles 6.13.6.1.3c and C6.13.6.1.3c of the 8th Edition LRFD Bridge Design Specifications to correct the error in the computation of Hw for this case. 2 - Changed bottom flange plate sizes in Example 2 to reflect new values (Cell B23 - C24). Version 2.02 - August 21, 2017 1 - Corrected interpretation of haunch dimension to match prevailing definition which is from the top of the web to the bottom of the face of the deck (Cells B109 and B114). 2 - A value for both left and right haunch are now displayed. Right haunch is calculated and an error check is performed to ensure that the girder is not embedded in the deck. 3 - Corrected formula for bottom flange Transverse Gage - Bolt Groups which was incorrectly using the top flange bolt quantity. (Cell D72) 4 - Corrected formula for Gross Path Length per Failure Plane - Tension in Block Shear Rupture Resistance - Splice Plates which was not accounting for multiple rows of bolts beyond 4 in the flanges (Cells B159-B162). 5 - Added 1/16in increments up to 2 inches and 1/8 inch thereafter to plate thickness list. 6 - Corrected misc. formatting of cells (decimal vs integer vs fraction). 7 - Corrected web row check which was triggering a NG when the input was not an even number (Cell G12). 8 - "Total Bolts (Per Side) - Calculated at Max Spacing" was hard coded to 2 and did not account for the number of rows actually entered by user (Cell B83). 9 - Filler plate reduction factor was always assuming that the flange, splice plates and filler plate were of the same width and that the sum of the splice plate areas was always greater than the flange area. Change calculation to a general form and added explicit calculations for Af and Ap. 10 - Oversize, long and short slotted hole options removed as they were never properly implemented and their use is not currently supported by the AASHTO Specification. 11 - Per Bolt Pt (kip) in slip resistance checks were not using slip coefficient in the calculations (Cells B364, B374, B384). 12 - Foundation work for future girder alignment options added but not enabled yet. Version 2.01 - June 12, 2017 1 - Corrected denominator of formula for calculating flange filler plate reduction (Cells C49 and C51). 2 - Bottom flange gage calculation incorrectly using the top flange bolt count (Cell I68). 3 - Gross Path Length per Failure Plane - Tension was incorrectly using the gage between bolt groups rather than the gage between bolts when calculating the failure plane. So in effect it was adding the web gap distance (Cell B134). 4 - The splice plate design force being used to calculate Factored Yield Resistance and Net Section Fracture Resistance was being divided by 2 again in the case where the splice was a double shear (<=10%) (Cells B135-B138 and B151-154). Version 2.00 - May 15, 2017 1 - Added support for single Shear Planes per Bolt (Ns = 1 and Ns = 1). 2 - Corrected logic check for determining Strength I - Positive coefficients DC1, DC2 and DW (Cells B10-D10). 3 - Corrected status check formula for bottom flange Bolt Layout - Transverse Gage, which was not using the bottom flange bolt quantity (bottomFlangeBoltRowQuantity) in the formula (Cell G62). 4 - Corrected Entering & Tightening Clearance (in) logic in Web Splice Plate End Distance to properly account for case where user input a value for enteringTighteningClearenceInitial or zero (Cell E67). 5 - Corrected Gage - Bolt Group Check which was not properly checking web layout variables (Cell F97). 6 - Adjusted general print layout for all worksheets. 7 - Flange moment resistance moment arm needs to be calculated based upon the flanges with the controlling Pfy (Cell B103 and B111). 8 - Corrected logic in negative moment Check Mflange which was referencing wrong cell (Cell B115). 9 - Changed formula for determining controlling Fu and thickness in girder flange Bearing Resistance Check (end and interior holes) from an HLOOKUP to a CHOOSE based upon the result of the "Controlling" check (Cells B289, C289, B293, C293, B300, C300, B304, C304). 10 - Updated version number in printed footing. 11 - Corrected formula variable reference in controlling section check (Cells F138-F140). Version 1.01 - March 31, 2017 This version addresses the following bugs: 1 - Filler plate reduction improperly calculated for 0.25in filler plate (Cells C43 and C45). 2 - Flange bolt counts not calculated to an evenly divisible number which was causing them to be underestimated. Corrected in both flange and web. (Cells F43, F45 and E53) 3 - Fixed error in statusSectionFractureBottomFlangeOuter check where design force was incorrectly divided by 2 within the status check. (Cell H140) 4 - Corrected the AASHTO 6.13.5.2 check where design net area check for flanges, An, must not exceed 0.85Ag. Status check was not checking for the "or equals" case. Changed the check from a critical error check to a "NOTICE" warning if An > 0.85 Ag. Conditional formatting changed to a warning type. (Cells E145-E148). 5 - Revised controlling section used in calculation of Rr which should use the minimum of An and 0.85Ag. (Cell G145-148). Swapped tabled Net Section to Gross Section Check - Tension and Net Section Fracture Resistance - Tension. 6 - Changed conditional formatting of "Check An = 0.85 Ag AASHTO 6.13.5.2" to a warning type and eliminated it from the error count on the input tab. (Cells D8-D11 and F39). 7 - Corrected headings for block shear tables in web and flange tabs where it incorrectly labled as "Tension" rather than "Shear". (Various) 8 - Corrected Factored Shear Rupture Resistance of web where Avn was calculated subtracting the total bolt count rather than one row of bolts. (Cell G124) Version 1.00 - March 14, 2017 Initial release of NSBA Splice Spreadsheet. Known limitation is support of Double Shear (N2) only connections that meet the <= 10% rule. User should not expect accurate results if % Difference Ag Inner/Outer Area is greater than 10%.