This version corrects a problem in the evaluation of near source factor Na. A patch was applied to the formula for that searches 2D table (with rows and column headings) using Excel's Index and Match functions.
The original version was shipped with the recalculation option set to manual. This option has been switched to automatic in this version.
Re: Final Final Released Version of 1997 UBC Lateral Design Spreadsheet
It seems I was a bit hasty in sending out this spreadsheet yesterday as I must have been blind or overstressed by work to miss the glaring problem that was not resolved. I apologize for this and urge you all to delete all prior versions.
The letter below will describe the features and changes over the beta version. Since I had the extra time, I also corrected the program to allow for the insertion of drag forces in the Flexible diaphragm analysis that might occur at the junction of two adjacent flexible blocks. You are warned to be sure that both blocks reflect the shared reaction from each side of the diaphragm in the similar grid number associated with each block.
This spreadsheet has been protected, however, no password is issued (when unprotecting the workbook, leave the password blank) should you wish to study the code and offer improvements. I have protected it simply to prevent overwriting important values and formulas. As a rule of thumb, anything in a shaded cell is input. Some cells that are shaded have formulas in them. However, you may override the formulas. To reset the cells, you will need to open a new spreadsheet version of the template and copy and past from one workbook to the next. Sorry - I'll have this automated in the future release.
The delay was due to the fact that the flexible diaphragm shear distribution was not working as I had planed. To compensate, I wrote the spreadsheet code based on assumptions that shear is distributed proportionally into the level below based on the relationship of tributary shear divided by total diaphragm or story shear.
Therefore for the shear distributed from the roof to the second floor walls at grid line C will be:
The total Force from roof * Shear at any grid line in the level below and divided by the total story shear in the level below. (Easier done than said) I think that as you work with the numbers you will feel more confident about the method and about the conservative values that are derived.
I have also removed the Bubbles from the Diaphragm analysis worksheets. Although I like the appearance of the bubbles, working with them is a pain. Anytime a cell needed to be resized the bubbles would distort. I believe that it is simply easier to define the diaphragm width as "left column" TO "right Column" - therefore the input format (actually anything you want) is 1 to 2 or A to B. This is not used anywhere else in the spreadsheet at this time. I expect that I will extract values from your input to be used to cut out a step or two in defining columns in other areas of the workbook.
On worksheets SWD (Shear Wall Design) you will find that wall deflection is calculated. If the deflection exceeds L/240 the cell turns red as a warning that you have exceeded the allowable story drift. You may need to return to the shear distribution worksheet SS to add or lengthen walls used.
IMPORTANT INSTRUCTION ON GRID LINES
I believe this is mentioned later on in this document but I think it bears repeating so that everyone understands the nature of the program. You should define your shearwalls from the first level up. Each line of shear should be defined as a column or grid line and each grid line should appear on every level. THIS IS IMPORTANT because the program distributes load by proportioning shear based on an elaborate table which is hidden from view.
This may become on of the debatable issues because, theoretically, you may have a diaphragm span farther than two grid lines because there are no walls to resist the diaphragm at the level below. Therefore, the deflection of the diaphragm may be larger.
I made the assumption that in buildings with a large number of interior walls used for shear, the diaphragm will never be considered flexible. The results of the analysis to determine the recommended procedure (rigid or flexible) will no longer matter as the program defaults to an envelope solution.
Let me get back to why the grid lines must project through each level. In the load distribution worksheet, the designer has the ability to define the location of shearwalls. The walls do not have to stack as the program determines by proportionality the amount of load that is distributed to all lines of shear. It's easier to play with the program and actually see the numbers rebalance than to explain the process.
The calculated distance between shearwalls (it identifies grid lines with no shear elements and combines these distances) distributes the loads.
The program then calculates the full load from the level above and proportions it into the level below - by comparing reaction shear to total shear (see section above for more information).
Be somewhat realistic in describing lines of shear. In other words, if a grid line is five feet or less away from an adjacent gridline above, there is an assumption that the shear can be transferred through the diaphragm sheathing and that the grids can be combined. This is an engineering judgement that the designer needs to make.
Please feel to send me your comments. I am interested in receiving feedback on the use of this program.
Dennis
October 14, 1999
Re: Final Release version of 1997 UBC Lateral Design Spreadsheet
To: All interested designers
From: Dennis S. Wish PE
I am pleased to announce the final release of the 1997 Uniform Building Code Lateral Design Spreadsheet. A great deal of work has gone into this version and great efforts were taken to insure its accuracy.
WARNING: There were few responses to the Beta version and most of the data checking was done in the process of using the program to design small projects in my office. Therefore, I want to warn all users to verify your results and verify the answers you obtain with this spreadsheet against a known example or sample problem. I would recommend that you spend time learning to use the program by solving a problem that you have already designed manually and which you feel confident about your results. Please notify me immediately if there is a noticeable discrepancy between your results and the results obtained by this tool.
The remainder of this letter is an erratum that you received with each beta version. There are features that were added and some were deleted from those documented. Decisions were made to insure a working version as quickly as possible, while other features, although desirable, will be left for development in future versions.
Features Removed:
1. Unfortunately, there is no easy means to maneuver around the workbook other than by the tabs at the bottom of the sheet. I had developed a menu system but found that it did not work reliably and, as the spreadsheet grew, became equally cumbersome. I do intend to add buttons to the sheets, which will help to move around the workbook easier.
2. The macro to input data from a cad drawing has been temporarily removed. This will be the first feature to be added back in and I expect this to be ready in the next week or so. I had some difficulty with this feature and need to study it some more before including it as part of the package.
Features Changed:
Conservative Results
At this time there is debate occurring as to the whether the design of wood structures should be done by Flexible or R 21421i83v igid design (depending on diaphragm Deflection results) OR by an envelope method. THIS SPREADSHEET ASSUMES THE MOST CONSERVATIVE ASSUMPTIONS AND RESORTS TO AN ENVELOPE SOLUTION. In addition, the applied loads used in the torsion analysis are accumulated from the flexible analysis as the summation of all worst case reactions (seismic compared to wind) in each direction. The user may, at their discretion, override the default formula in the Px and Py applied load cells of the spreadsheet.
It is recommended that you copy the cells to another location and cut and paste them back into place should you decide to use the default values rather than a less conservative approach. To make it easier on the user, each load at each level and in each direction is given a range name associated to the block and workbook that it originated in. There is a note attached to the Load cells in the torsion analysis that explains how the range name was developed. The range name will be in the form of B1FEW01 which refers to Block 1, Force in East-West direction, at lowest level 01.
Flexible Analysis
The program allows the user to define up to four blocks - three stories each. Each block can have up to 40 shearwalls per level in each direction.
Caution must be taken to insure that diaphragm area's match the total areas input for the block used. You can do this by defining the grid (column) spacing and depth of diaphragm before filling any information in the worksheet B(x)-SS (where x is the block number). The program multiplies each area between columns by the gross depth of diaphragm and sums the total areas to the right of the spreadsheet B(x) - NS or B(x)-EW. The total area may differ in each direction due to minor discrepancies in defining the area. The program takes the largest area accumulated in each direction and applies it as a default to the worksheet B(x) - SS.
You may override the areas manually, however, if your area is less than the area calculated on the NS or EW sheets, a red warning will appear to alert you to check your results.
The purpose of this is to calculate the unit shears, which are a function of the area of the diaphragm. Therefore a warning is given when the calculated areas exceed that which the user inputs.
You will notice that you are encouraged to override the default areas at times. This occurs when you have designated areas where the weight of materials may differ - for example if there is an area of the diaphragm used as a floor and an adjacent area used as a roof.
You must be alert to the balancing of the areas until some feature is designed to simplify this for you.
Adjacent blocks and accumulated
shears.
The
user must keep track of the reactions resulting on column lines shared by two
blocks. This release does not combine shears at adjacent blocks automatically.
I expect to have some method of automating this process in the next release.
This may, for the moment, create some difficulty when trying to determine the
stiffness of the wall elements inasmuch as they will be determined
independently in each block.
This
is not a concern on the torsion analysis as the diaphragm is considered to be
homogenous and distribution is complete.
THIS FEATURE HAS BEEN CORRECTED AND INSTALLED. NOTE: THE USE MUST MANUALLY INSERT THE LOAD AT THE APPROPRIATE COLUMN IN EACH BLOCK. THE PROGRAM WILL DETERMINE THE REACTION TO BE USE.
Rigid Analysis
There is a question about the accuracy of defining different materials for stiffness in the Torsion Analysis. The program uses a straight distribution approach based on the physics of the area. The user can define the wall stiffness in terms of Modulus of Elasticity, Thickness, capacity per foot of wall. The program will work out the stiffness (k) factor as a function of E and t in the Calculations worksheet. The demand to capacity ration is given in the Output sheet so that the user can aware when the demand exceeds the capacity of the wall.
The program is, admittedly, keyed more for masonry and concrete where the stiffness of the wall is more a function of the wall thickness and modulus.
I URGE ALL OF YOU TO NOTIFY ME OF ANY SUGGESTIONS YOU MAY HAVE TO BE ABLE TO MODEL MIXED SYSTEMS OF MASONRY, WOOD AND STEEL.
Most of the remaining features are mentioned in the remainder of this letter. The spreadsheet should be fairly self-explanatory, although there has been no time to create a useful Help file of other form of documentation. Any help would be greatly appreciated. Until this occurs, I am afraid that you must play with the program thoroughly to become familiar with its features and quirks.
CAUTION:
As I mentioned before, this is a large and complicated spreadsheet. Every care has been taken to resolve conflicts or errors. I am sure that some may still exist, but for the most part, you will find these by error messages such as a #DIV/0 warning. This should not affect accuracy but may be an inconvenience until enough people have worked with the program and can advise me of the bugs that need to be found.
I have used the program on four buildings. These were only one-story structures. To verify the accuracy of a 3-story building, I duplicated the same structure at each level and compared the results as if each level represented three separate one-story structures. The only difference (in the torsion analysis) is the applied load values and these are taken from the original multi-story distribution sheets in the Flexible diaphragm design portion.
The program should save the user considerable time and allow for a good deal of experimentation and "tweaking" of the finished product.
Printing
Print areas have been saved for each sheet. The user must manually set the printer for each worksheet when a small building is designed. The default is to print all sheets for a three-story building although this may not be necessary. You can do this by choosing the page numbers of the worksheet that you are printing. Refer to the Windows Print help files for more information.
You may also create print macros that will help you automate the process. I urge you to share these additional features with the rest of us.
USER IMPROVEMENT
The program is useful as presented. There are many improvements to be made that will make you more productive and simplify some of the more tedious tasks. I have spent a great deal of time to prepare this program which is to be donated to the engineering community. A few others have also contributed great efforts to the creation of this program - in particular, David Merrick who upgraded the torsion analysis considerably. My deepest cudo's to David for his efforts.
I can't impress upon each of you the importance of creating tools such as this and donating them to the engineering community to help each of us perform our work in compliance with the code in the most productive manner. I don't wish to impede those who want to create and sell software, but many of us can learn to create our own tools by studying the efforts of others willing to share their efforts.
Therefore, I urge each of you to improve upon the software and distribute it for other to learn and benefit from. If you do this, please send the software back to me at SECONSLTNT@aol.com and I will revise the errata and track all version numbers and changes. This will help maintain the history of the software.
Sincerely,
Dennis S. Wish PE
1997 Uniform Building Code Lateral Design Spreadsheet Final Release Version 1.0
The enclosed spreadsheet is protected by copyright to Dennis S. Wish, PE and individuals or firms whose additions are incorporated into this software. This software is Freeware and shall not be licensed, sold, distributed for profit or incorporated into any other software which is intended to be distributed for a fee, licensing or which will e sold commercially with the intention of collecting profit (personal or commercial). It may, however, be used in the practice of engineering as a productivity tool and can be used indirectly to generate income.
The software is consider Evolution Ware, the ps generate sucessive upgrades, improvements, and corrections (where needed) by users who agree to donate the changed software back to the engineering community for the purpose of Freeware Distribution.
This spreadsheet is intended as an educational tool for the learning and understanding of the Wood provisions of the 1997 Uniform Building Code. The program is to be used at the users discretion and may be used in any creative manner to achieve results that can be proven or justified by the user and which will be in compliance with the intent of the code. The user may, at his or her discretion, modify and or change the software to fit the needs of the individual for the purpose of satisfying the code intention. The user may not alter this software with the intention of transfer of monetary funds in any form. The software may be transfered for monetary value only by non-profit engineering organizations and only with the express written consent of all parties who participated in the development of this software. Any and all non-profit organizations who are allowed to generate income from this software shall not in any manner disturb or prevent the continued Free access and distribution of this software.
Users understand that this software may contain mistakes, bugs or other errors which may affect the printed solutions. The accuracy can not be guaranteed inasmuch as the user has total control and can change any and all formula's. All efforts have been taken to insure the accuracy of this software but the user agrees to hold harmless any individual or group of individuals who participated in the creation of this software. As with any professional software, this product is intended to be used by design individuals with a valid and thorough understanding of the principles of structural engineering. The authors and contributers are to be held harmless for any use or misuse of this program. The user shall review the software to insure that the results are accurate and shall take responsiblity for any and all output. The developers of this software shall take no responsibility for design drawings resulting from the values determined by this software. Consequently, the developers take no responsiblity for the finished product. This software is to be used as both an educational and design tool only with the understanding that the software is not now nor will most likely be considered a finished product as long as work continues to be performed in the evolution of this software.
The enclosed spreadsheet is intended to comply with the provisions of the 1997 Uniform building Code Chapters 16 and 23. The software is an interpretation of the written code and may contain inacuracies that reflect different interpretations of the same code. Whenever possible, I will have tried to explain who the results were derived. The software uses tables published in the code which have been reproduced in the spreadsheet to act as a database for the choice of values based upon a given variable chosen.
Errata (most current revisions are listed first).
July 21, 1999 Beta V1.0721
First, I would like to thank those of you who have sent me comments and feedback by email. Only a few of you have done so, but your comments were both useful and informative. I receive constructive critism that yielded some very good suggestions. I have maintained a record of these and have incorporated as many as possible. As I upgrade the program (I think this will be a never ending process) I will add more and more.
I am admittedly ignorant of Visual Basic programing and was not able to accomplish some of the slicker techniques I wanted in the program. You will notice, if you are a student of spreadsheet programing, that I have worked out solutions that are, at times, convoluted but do get the job done. I welcome anyone who can show me how to simplify some of these methods.
This version incorporates the Torsional analysis by James Lord and David Merrick into the spreadsheet. It is, at this point, only capable of working on one level. In the next week , this will be my primary focus to add the next two levels and integrate the information from the first portion of the workbook. I need to take a small hiatus to complete a one story project that was the main reason for creating the software. Unfortunately, my clients are not willing to wait much longer.
The Torsional Analysis is essentially the rigid diaphragm Spreadsheet created a few years ago by James Lord, S.E. (a member of SEAONC). One of our list members, David Merrick, P.E. did some major modifications to the spreadsheet that allows skewed shearwalls to be input. He as rewritten the calculations for Center of Gravity as well and has also provided a macro that will extract data from an AutoCad drawing. I was unable to get the Macro to work and notified David. He seems to have had luck with it so I left it in the program in case the problem was on my end. At any rate, if any of you find it works well, I would be appreciate hearing from you.
For those, like me who were unable to use the macro, I will offer the following suggestion for how to extract data easier than by working your way around a blue line set of drawings with pencil and paper.
Extracting Data:
The goal is to use AutoCad's "List" command to give the coordinates for all walls and the perimeter polyline. You will then simply Cut and paste the information from the AutoCad text screen to Wordpad or any other text editor. This may not work well if you are using a DOS version of Autocad (it will work if you run Autocad from a DOS window). This method should work for any Autocad compatible program. I have checked it with Intellicad as well and believe that it also works with IMSI's Visual Cadd 3.0. It is a bit tedius but less so than trying to pick up points off a drawing.
1. Isoloate a polyline representing the perimeter of the diaprhagm under consideration and place it on a separate layer.
2. Create a layer for Shearwalls and use a single line over the exterior side of outside walls that represent the shearwalls length. You can use either side of the wall for the interior, however, the most accurate results will occur if the line is in the center of the wall thickness (ie, 2-3/4" for a 2x6 wall).
3. Use the Autocad command "Units" to change the default units to Engineering. This will give wall lengths in decimal inches. It would be easier to work with decimal feet, but I was unable to figure out how to setup Autocad for anything other than their default feet and decimal inches or simply inches.
NOTE: The coordinates will be given in inches which you can input on the Wall Worksheet in the Torsion section. This will be converted to decimal feet on the Input-Output sheet automatically. If you do work in decimal feet you can enter the information directly to the Input-Output sheet.
4. Turn off all layer other than the two you just created.
5. Locate the farthest left most point and the bottom most point and draw a horizontal and vertical line that convergest to a "datum" point at the lower left.
6. For simplicity sake, Move the objects on both layers so that the "datum" point becomes 0 (origin). This will insure that the X and Y axis of the sketch or Chart is outside of the structure.
7. Use the List command and first chose the exterior diaprhagm polyline. Hit the F2 button (function key) and highlight the coordinates shown on the Autocad Text Window. Copy and Paste the information to any text editor and save it.
8. Turn off the layer that the Diaphragm polyline was on and repeat the instruction (7) above. This will capture the wall coordinates. Carefull here, the coordinates are displayed screen by screen and requires you to hit ENTER each time for a new member. It is best to copy and past the information from each screen to your text editor as you go along.
9. Save the file when finished as it will contain all of the information you need to complete the torsion analysis geometry.
NOTE: IF ANY OF YOU KNOW OF A METHOD FOR EXTRACTING THE
COORDINATES INTO A SPREADSHEET FILE, PLEASE LET ME KNOW. THERE IS
The rest of the torsion analysis information will be mentioned below.
Changes, Revisions and Modifications:
1. I have decided to remove the multiple block capabilities. The size of the progam keeps growing and is over 2.5 megs which may create a problem for those of you with older machines. I also have a concern that as the program grows it becomes increasingly more difficult to keep track of the information. Adding more blocks requires that the program more than triple in size. I believe that it would be much easier to save the workbook as a new name and change the block information for each unique portion area.
2. I have again upgraded the floating toolbar that helps navigate the spreadsheet. One problem that I have is that if you change the name of the spreadsheet, the toolbar macros no longer work. I have not been able to get any information off the knowledgebase or help files (or the excel users groups) to explain how to set the toolbar to work in any worksheet renamed from the original. IF ANY OF YOU HAS AN ANSWER FOR THIS, PLEASE LET ME KNOW.
3. I Expanded the Torsional Analysis to 40 walls at each level (only one level is ready at this time). THERE IS CURRENTLY NO LINK BETWEEN THE TORSION ANALYSIS INFORMATION AND THE INFORMATION INPUT IN THE FLEXIBLE ANALYSIS, COVER SHEET OR THE LOADS SPREADSHEET. THIS WILL CHANGE, BUT FOR NOW THE INFORMATION MUST BE HANDLED MANUALLY.
4. The floating toolbar can be docked at any edge of your screen. Drag it with your mouse to the sides or below the top toolbar and it will integrate itself into the perimeter of the screen. I left it floating so that you will take notice of it.
Philosophical Changes
Obviously, the program will work almost flawlessly if you are modeling a rectangular building. However, I tend to get the more complicated, unusually shaped custom homes (See the sketch on the Wall Worksheet) that force me (and you I am sure) to think creativly about this new methodology.
The flexible diaphragm analysis takes the old tried and true method of using tributary distribution to distribut the loads through the diaphragm. Rather than rehash the methodology, refer to the notes from the prior version to bring yourself up to date (they are sequentually numbered with the oldest notes appearing at the end of this file). There are some things that you must keep track of:
A) The area input on the Structural System Worksheet must equal the sum of the areas occuring between gridlines that are calculated on the Block #1 N-S and E-W sheets. Inasmuch as you have total control over the geometry of each subset of the total block, you need to be sure that the area calculations balance or the unit shears will not be acurate.
HINT: Dimension the Grid Lines starting from the lowest level up to the roof. There should be a grid line representing every line of shear in the building. Since the walls may not stack, some levels will have grid lines where no walls occur (these walls occur at other levels).
Make sure that the sum of the spans between grid lines does not exceed the physical width of the building (in each direction). The diaphragm depths between each grid line may increase or decrease. The total area is calcuated from the Summation of the gross depth of the diaprhagm (between gridlines) times the span. BE SURE TO CHECK THE SUM OF THESE AREAS AGAINST THE SIZE OF THE AREA INPUT ON THE STRUCTURAL SYSTEMS SHEET.
Refer to the notes below for version V1.0715 for conditional statements added to the cells to avoid exceeding the wall maximum shear capacity or the code allowable H/b ratio.
I completed the Shearwall Deflection analysis. I had to make a decision as to how much "Tweaking" you could do in order to get all of the walls in any one line of shear to deflect at close to the same rate. IMPORTANT: I believed that it was not practical to try and change variables for multiple walls in any one line so as to match stiffness. The reality is that it will be next to impossible to control in construction. It is much easier to deal with masonry, concrete and steel shear wall construction because the trades are more specialized and familiar with constructing piers with differing levels of reinforcement. In wood, plywood is typically applied by a subcontractor who gets paid by the panel. My experience has been that unskilled labor is used to nail panels and nails are spaced "as close as possible to the shearwall schedule". However, most contractors take the position that it is better to install more nails closer together than take the time to measure off the actual spacing. Therefore, it is common to find walls which are specified at 6" on center to be nailed closer to 4" and often some at greater than 6" and closer to 3".
Until this revision to the code, stiffness was not a factor. I personally believe that we will not be able to control exact spacing of nails and that the wall stiffness will vary from wall to wall due to construction practices.
Therefore, I made the walls adjustable as a group and only allowed for minor changes such as the size of the King Studs or posts at each end of a wall panel. This is reasonable as the requirments for holddowns will control the size of the tension and compression chords.
The shearwall deflection sheet breaks down each of the four terms in the deflection formula so that the designer can compare the results of each term to see what term differs the most.
The analysis also calcualtes the stiffness factors K for the individual wall sections and the sum of the sections in each gridline.
I have not incorporated an analysis for shearwalls with openings. I am not sure at this time how to do this (or how to add deflection calculations for walls that don't stack and occur above beams). These may need to be done manually and the stiffness "tweaked" separately.
CM & CR
This next section calculates the Center of Mass and the
LIMITATION
The block method of analysis as I have been explaining does not take into consideration skewed walls. I am faced with this in my first project under this code. I plan to use the examples provided the the second section by David Merrick for the analysis of the Center of Gravity to change the program and to allowed for unusually shaped blocks and skewed wall sections.
PART II - TORSIONAL ANALYSIS
NOTE: SECTION II OF THE WORKBOOK IS INDEPENDENT OF THE FIRST SECTION. ALL COORDINATES TO DETERMINE THE AREA OF THE DIAPHRAGMS MUST BE CALCULATED SEPARATELY. THE USER MUST TRANSFER THE DIAPHRAM FORCES AND LOCATION OF SHEARWALLS FROM THE FIRST HALF TO THE TORSION ANALYSIS MANUALLY AND CAUTION SHOULD BE TAKEN THAT THE GEOMETRY IS CONSISTENT. THESE NEEDS WILL BE ADDRESSED IN FUTURE UPDATES BUT THE PROGRAM IS STILL USABLE AS LONG AS THE DESIGNER IS AWARE OF THE LIMITATIONS.
The first thing you should notice (after reading the two note sheets embedded in the spreadsheet) is the sheet titled "Wall Worksheet". I have input the plan of the project that I am currently trying to complete. This example has created a number of challenges and questions that I need to answer, but both James Lord SE and David Merrick have helped considerably with their donations.
1. Refer to the instructions for inputing the coordinates of the diaphragm area from a CAD drawing. All dimensions shown on this sheet are in inches due to the limits of the Autocad units. The coordinates are referenced to the Input-Output sheet where they are divided by 12 to convert them to feet so as to be in compatible units with the analysis. YOU MAY OVER-RIDE THE PROGRAM AND ENTER FEET DIRECTLY INTO THE INPUT-OUTPUT SHEET, HOWEVER, YOU WILL REMOVE THE CELL FORMULA AND WILL NO LONGER BE ABLE TO CONVERT A CAD (INCH) DRAWING IN THIS WORKBOOK. YOU ARE, HOWEVER, ABLE TO REWRITE THE FORMULA. I WOULD RECOMMEND THAT YOU CUT AND PASTE ONE CELL TO AN INCONSPICOUS CELL IN THE INPUT-OUTPUT SHEET SO THAT YOU CAN PASTE IT BACK LATER AND COPY IT ALL LOWER CELLS. THIS WILL REINSTATE THE INCH TO FEET CAPABILITIES.
2. You are allowed to specify different wall materials (concrete, wood, steel etc.) in the Wall Type boxes located on the Input-Output worksheet in cells B56-F65. If you use all the same type of wall, you can enter a 1 in the E column and t columns. These are relative and will cancle out if all walls are of the same materials.
TIP: YOU CAN DEFINE STEEL COLUMNS AND WOOD IN THIS SECTION SO THAT THE PROGRAM WILL CONSIDER THE STIFFNESS OF THE DIFFERENT MATERIALS IN THE TORSION ANALYSIS. YOU CAN ALSO DEFINE A NUMBER OF SHEARWALLS BY CAPACITY (IE., 320 PLF = 0.32 K/FT). THIS LETS YOU DEFINE A TYPE OF SHEARWALL FOR EACH WALL SECTION (40 WALLS PER LEVEL BUT ONLY 10 DIFFERENT TYPES OF SHEARWALLS - WHICH SHOULD BE SUFFICIENT ).
SUMMARY
The final portion of the workbook will compare shears in each line. The problem here is that the second half of the workbook can calculate the torsion and shear by rigidity of diffiult geometries while the first half is limited to smaller simpler geometries. To be consistent, you would need to break apart the diprhagm into sections that would allow both sections of the workbook to compare similar geometries. This would simplify the next step of creating a summary to determine the variations between flexible and rigid diaphragms.
If, however, the structure is relativly regular in shape (square or rectangular), modeling the entire structure is easy and straight forward. Unfortunately, this is not typical of the conditions I face in custom homes. This is where, in my opinion, the Seismology committee has failed to understand the complexity that they created by required the method of wood and expected that all materials will essetially perform the same. In some ways this may be true, but the amount of work required to find a relativly insignificant difference will cause many of us a lot of sleepless hours.
****** IMPORTANT CODE CONCERNS WHICH NEED TO BE RESOLVED**********
It appears from working through the design examples that under ideal conditions the goal is to design the struture so that all walls deflect with the same relative stiffness. The code does not suggest what should be done to "tweak out" the walls nor does it explain what to do when stiffness differes from grid line to grid line or between walls in each grid line.
As I have stated in the summary above, it does not seem practical in wood framing to tweak out walls to specifically. You may be able to reduce your liablity, but will have a great deal of trouble controling the quality of construction in the field. If the Building Industry does not take some responsiblity to require those who build these structural systems to be certified and educated with special knowledge of the code, there can be no quality control that will be be effective and you, as the engineer of record, still remains liable under the Strutural Observation guidelines.
There have been many discussions both public and private on the SEAINT Listservice regarding these topics and most engineers supporting the code feel that we need to design both ways and to identify the effects of torson on our structures. However, arbitrarily adding stiffness will change the distribution of torsion and may not yeild ideal results.
The code requires the design of wind, and in most cases, wind will still govern in wood design. Wind is a uniform load that will not be affected by the geometry of the structure as in seismic design. With uniform loading, the rotational differences do not appear to be as great as when seismic controls. Therefore, one of the underlying questions is "If wind controls, can torsion be ignored and the resisting walls designed by rigidity to keep the same stiffness in each line of shear?"
Now that the aspect ratio of the walls are greatly impacted by the new code, I suspect that we will start to see more proprietary shearwall systems like the Hardy Frames or Simpson Strongwalls. How are these frames suppose to be modeled in this program? You can not simply substitute these propriatary frames for wood walls without knowing their stiffness factors. Yet I suspect that engineers will want to substitute one Hardy frame for four various shearwalls in one gridline so as to simplify the analysis. Does this force us back into manual calculations to readjust the shear?
How do we treat field changes when the owner decides he wants to add walls or remove walls - or wants to enlarge walls during the course of construction. Any minor change such as this will require rebalancing the entire structure and a change in one area may affect the torsional shear in another area. How do we explain this to our clients?
There are a great number of problems with the new code as it applies to residential construction that needs to be addressed and quickly. I am interested in any comments from my peers.
Dennis S. Wish PE.
PROBABLY MORE NOW THAN IN THE PAST, YOUR COMMENTS ARE MOST
IMPORTANT. THE TWO PORTIONS OF THE WORKBOOK NEED TO BE TIED TOGETHER
AND I AM STRUGGLING WITH THE
July 15, 1999 Beta V1.0715
There have been a few major changes and I recommend that you delete all previous versions. The changes are not so much in additions as they are in corrections and subtle changes to prepare for the next steps in the design process.
1) The first obvious addition is a floating toolbar that assists in moving from worksheet to worksheet. If you are modifying the spreadsheet, you will not be able to use the macro from within another command and will need to use the tabs at the bottom of the workbook. In the future, you will be able to access the print macros from this menu.
2) Although a minor change, you can now customize the title block on the Cover sheet and most of the changes will be reflected on each subsequent worksheet. This includes the Company name, company address, job number, job name, designer and checker input. The graphic can be eliminated or replaced with your own graphic logo or it can be modified within the graphic tools in Excel - however, you must manually replace the logo on each sheet.
3) The comparison between wind and seismic was not accurate. The problem did not surface until I started to play with different values and noticed that the uniform shear v increased and decreased with changes in the span between shear panels (gridlines). The reason is that the first term of the deflection formula has been converted to use the diaphragms unit shear. The wind load is a function of the height of the building and is applied to the span between grid lines. Normally, we would do the same thing when calculating seismic shear to the structure. In this case it is different. The design example calculates the diaprhagm shear stress at each level so as to allow the designer to compare the diaphragms capacity to this demand. This is to alert the user if the capacity is exceeded to allow for changes in panel nailing, thickness etc.
The first term of the deflection formula is 5 v L^3 / (8 E A b) The term ' v ' is the diaprhagm shear stress calculated along the depth of the diaphragm (b dimension). The difference in the formula compared to the typical beam deflection formula is two fold and maybe it is a good idea to explain it as it was explained to me.
Start with the beam deflection - Delta = 5 w L^4 / (384 E I)
Since v is a function of w, then v = wL 2b) AND w = 2 v b / L
This next part was difficult for me to grasp since I tried to convert the Moment of Inertia working in plan view. The trick was to learn that the effect of the I value was along the diaphragms thickness. The first term calculates the I between the chords and disregards the I of the sheathing (which is left to the second term of the formula). I = Sum (Ad^2)
d = b/2 (the distance between the chords and to the neutral axis)
Since there are two chords the term becomes: I = 2A (12 b/2 2 (12 is used to convert units) I = 72 A b^2
Substituting terms: Delta = 5 v L^3 / (8 E A b)
Finally, to compare seismic to wind you need to either convert 'v' back to w or convert wind to a diaphram unit shear along b. The later is easier to do since it does not require a convoluted way to reconvert back for the deflection formula. Therefore, the new term for uniform wind load is actually the reaction of the wind load between gridlines divided by the net depth of the diaphragm ( wind * L / (2 b) )
4) The next step was to convert back to a uniform applied load ' w ' in the Flexible Diaprhagm shear wall analysis. I won't go into the steps that it took, but this actually worked out to be pretty straight forward.
5) The Flexible Diaphragm Shear distribution sheet some other changes and is still not finished (however, it is usable). The most noted change are the limits placed on shear values and wall capacity:
a) When the shearwall aspect ratio exceeds 2:1, the cell containing the wall length turns red as a warning.
b) When the unit shear exceeds 1100 pounds per foot, the cell containing the capacity turns red and the cell indicating the number of sides to sheath indicates the word "FAIL" to alert the designer that they are exceeding the code's allowable wall values.
6) The appearance of the sheet has changed just to simplfy the processes of expanding the use of the sheet. This will include the thickness, structural grade, nail size and spacing. The information from this will be summarized in a shearwall schedule later.
There have been some other minor changes which have been made - mostly in appearance and to correct a few minor mistakes. As always, please feed your comments and suggestions to me and I'll see what I can do to incorporate them into the program.
July 12, 1999
1) The first change you will notice is the the name change. July 12, 1999 RDV10712.Zip which stands for: Residential Design Version 1."post date". zip or exe
I have had some email me that their downloads were truncated and the Zip file was damaged. I am shortening the file name tso as to conform to the 8.3 DOS name requirments. This may have not benifit other than to try and reduce any possible problems associated with long file names and email transmission.
2). There is a major change to the logic which is in the programing and not visible. This occurs in Worksheet "Block #1 Flexible Diaphragm" (refer to tables located at the range AB35 to CO53). The conditional statement tested for the number 1 which reprented the presence of shear walls in any of the grid lines at each level. If the number 1 was detected, the grid line was assumed to have shear resisting elements. Any other number was considered to mean that no shearwalls occured.
I did not plan ahead well enough and realized this morning that I could use this input to define the number of resisting elements (walls) in each grid line. Therefore I needed to change the conditional statments to recognize the lack of shearwalls or the number zero.
This became a time consuming change because the order of the conditional statement also needed to be changed to avoid rewriting hundreds of formulas. This took most of the day be provides a much more flexible means to account for an unlimited number of shearwalls in each grid line.
The purpose of this is to create a means to test wall rigidity and to try and make all the walls the same stiffness.
This is where I have stopped today and will continue to work on the sheet tonight and the rest of the week.
TIPS:
In the course of writing the spreadsheet, I began to see the relationship between materials and shear distribution. There are a number of Theories which I came away with to help simplify the design process. These are debatable and you might want to rack your minds finding flaws in the logic.
1. Forget about separating materials by stiffness (ie steel columns or plywood shearwalls). As long as you know how to run a deflection calculation, if you design the elements to deflect the same amount, you have matched stiffness.
2. One major conclusion drawn by many engineers is that in the case of an open front type structure (garage, retail store) the rear walls are much stiffer than the front. To compensate will require a much larger steel section - in other words the columns will be very large to match the deflection of the back wall. What many are forgetting is that the back wall does not have to be as stiff. Rather than sheath the entire back wall for shear, design the plywood walls to be less stiff so as to come closer to matching a lighter steel column that still does not exceed the allowable story drift. This will yield a more ecconomical design by reducing the amount of plywood and mechanical connectors and reducing the size of the embedded steel columns.
3. I've said this one before (see below) it is a known fact that you can not create a rigid moment resisting connection between two pieces of wood without embedding one in a foundation (flagpole). Therefore, you can not make a joint between two block "L" or "U" shaped structure that will have zero rotation. Because of this, you can not treat a "L" or "U" shaped structure as rigid and design the entire shape for torsion.
You can, however, break the shape into it's basic elements (blocks) and treat each block as though it were rigid. If you do this you have a possiblity that the torsional shear applied at the common end will work in the opposite direction and cancle out the torsion from the adjacent block.
Since the two blocks can not be rigidly connected, I would recommend that the torsional shears be additive only (not following sign convention) and thuse yeild more conservative results.
This would justify the use of this spreadsheet to reduce complicated shapes into basic and easier to analyze blocks. It does mean more analysis (a full analysis required for each block) but isn't this what the spreadsheet is suppose to simplify.
4. Simplify the design further by limiting the aspect ratio of all shearwalls in the same grid line to be the same. In most cases this is possible. Where piers are limited and the shorter pier must control, you can make the deflections uniform by using proprietary shear elements like the Hardy Frame or Hardy Panel from Simplified Structural Systems. The advantage is that you can use an R value between 4.5 and 5.5 which means that you are not penalized as you are with embedded columns and these proprietary systems are much stiffer than conventional plywood shearwalls (ie. about 4,000 lbs of shear in a 4x8 panel). CAUTION: be sure that your foundation is designed appropriately for the use of these walls. Hardy recommends using (2) #4 bars at the top and (2) #4 bars at the bottom of the foundation and extending the bars 3'-0" past each end of the wall or wrapping the steel for 3'-0" around a corner. Don't neglect the uplift forces which means that at the front of a garage you are safer to design a continuous grade beam than to eliminate the beam between garage door piers.
5. Use the spreadsheet to help you understand the relationship between the code variables. I have learned a great deal about the new code from the creation of this worksheet and the discussions/debates I've had on the List. Take the time to delve into it - I think you will feel confident about using the code once you do (and at least somewhat frustrated about those methods that you don't agree with).
July 10, 1999
The lateral distribution to shearwalls by flexible diaprhagm analysis is now in the process of being completed. It is important that the following guidelines be strictly adhered to so as to insure accurate results:
1. All Lines of Shear in each block must be accounted for at each level in the Diaprhagm Deflection analysis. The spans used in the Diaphragm Deflection Calculations are referenced to the Shearwall analysis. The governing shear between each grid line is also referenced from the Shear Wall distribution.
For example, if you have a shear wall occuring on one level and not on another, that shearwall must have an appropriate grid line associated to it at all levels. Assume you have two walls at the roof and three at the second floor. Each level must have three gridlines (you can consider the middle grid line to be a "ghost" of the wall at the level below. The purpose for doing this is to allow for distribution of shear to walls that do not stack. If you look at the tables used on the Flexible Shear Distribution Worksheet you should see the relationship of how shears are cumulated. The uniform forces are summed on each side of the shear wall and are dependent upon where walls are indicated and where they are omitted (because of the "ghost" wall at the level below).
This may not be the most elegant way to deal with the programing but it seems to work just fine and makes it much easier to track loads.
2. There were corrections made to some of the Load sheets. I noticed that some of the Combo boxes did not appear on sheets that were copied for the three structural systems. They are now fixed.
3. The spacing between grid lines are now fixed at all levels. This is to insure accurate results for non-stacking walls. If you decide to add a wall to a lower level, you need to go back, to the roof and change the grid spacings. Possibly someone can help determine an easier way to handle this - still, you save a lot of time over doing it by hand:>)
4. A background pattern (appropriately called wood.bmp) is included in this revision. You do not need to use it, but it will make your spreadsheet easier to stare at and will not print as output.
5. Diaphragms are now refered to
by level. A three story structure will have a Roof, Second and First level. I
decided to standardize this because some 2 story residences need to consider
the first floor as a level if it is framed above a cripple wall. Therefore, the
program becomes limited to levels rather than stories. For somewhat selfish
reasons, I have no specific need for anything greater than a two story design
program due to the height restrictions here in the desert. The
HELP WANTED
We are in need of either custom pulldown menus to navigate the spreadsheet and macros to automate the printing process. The spreadsheet is growing rapidly in size and I guestimate it will reach somewhere around 3Mb in size. If anyone can help create a Navigation Floating Toolbar, it would be very helpful.
Other issues:
I am seriously considering changing the program to allow appropriate design for any type of building. This does not appear to be difficult. It needs to consider the Rho and Omega factors for mixed structural systems. This will be down the road a bit, but I intend to make the modifications so that we are not restricted by occupancy type.
Comments:
Please let me know your comments and suggestions. Most of all, please let me know if you find inaccurate results - these need immediate attention (a few compliments wouldn't hurt either:>)
July 8, 1999
This version of the spreadsheet represents a pre-release version that is only partially complete. The spreadsheet follows the methodology presented in from the 1998 seminar held by Structural Engineers Association of Southern California "1997 Uniform Building Code (UBC) Wood Provisions" (Feb. 21, 1998). However, I have provided certain changes that reflect my professional interpretation of the wood frameing code. These include the following:
1. Wood structures with wood diaphragms are neither entirely rigid nor entirely flexible. Therefore, irregular shaped structures such as "U" or "L" shaped structures can be broken down into individual blocks to simplify the design. The reasoning behind this interpretation is that it is not possible to create a totally rigid wood connection without embedment of the wood materials. Therefore, where an "L" shaped building is analyzed, the joint at the connection of the two legs can not be entirely rigid and will experience rotation which translates into a pinned connection. The user who does not agree with this opinion should be warned not to use the software to design independent blocks.
2. I have been advised that the diaphragm deflection calculation is an emperical based formula and that the units will not balance. Users are urged to satisfy themselves that the results of these formula are accurate and valid.
3. As noted above, the software is far from complete. The user is warned to disregard the shearwall analysis and subsequent graph and schedules. These are left over from the one story flexible design spreadsheet that was used for layout. The workbook sheets to the right of that labled Block #3 E-W is not valid at this time and should be eliminated or ignored.
4. All tables are included in each worksheet. Printed page area's are defined (use the preview button to see the limits of what will be printed). All tables are added to the right of the print area. It was my original intention to hide the tables from view to discourage inappropriate changes. However, the spreadsheet is offered to allow the user to modify or improve it as they see fit. therefore, all tables are accessible and nothing is protected on the spreadsheet.
5. ActiveX controls have been added. These are very simple to use. A table at the right of the printable sheet contains a column of choices which will be visible in the pulldown
Combo box. Each choice starting at the top is assigned a number beginning with one. The integer is used to search for other values rather than relying upon text or ranges. This simplifies the lookup tables as you will not that each table contains simple integers at the top and sides. Once a variable has been chosen from the Combo Box the associated integer is written into the cell located below the combo box. If you right click on the combo box, you can move it out of the way to verify the location of the written integer. This cell is later used in the Vlookup or Index and Match functions in Excel. The user is encouraged to build upon the tables and add or change any of the choices.
6. The methodology uses Allowable Stress design. The values for Ca and Cv have been reduced by 1.4 to convert for ultimate strength design to allowable stress as indicated in the seminar notes.
Inclusions:
1. The spreadsheet contains the following worksheets:
A) Cover - a generic coversheet used to identify the project, designer, code and material stresses used throughout the total design project. No information on this page is used by any other worksheet.
B) Loads - This allows the user to define the materials and loads used throughout the spreadsheet. Only the Roof I and II, Floor and Deck/Balcony loads are used in the remainder of the spreadsheet. Exterior and Interior wall partitions are only listed for the record. The spreadsheet assumes that 5 psf for roof level and 10 psf for floor levels is arbitrarily added to the roof and floor diaphragm deadloads. The wall loads are added to each Structural System Sheet (at the right of the work sheet) and may be changed or modified by the user. One suggestion has been to assume a 1 psf load for each vertical foot of combined interior and exterior walls. Therefore, the formulas can use the actual story height to increase the generic wall load for taller structures.
Wind Loads - The code still requires the designer to evaluate wind loads and compare them to the seismic loads on the structure. All tables for the determination of wind loads are included for levels up to 40 feet. This is pretty self-explanatory. Exposure, Wind speeds are chosen from pull down combo boxes.
Seismic Loads - incorporates all new code criteria for Near Source evaluation of base shear. The difference between code and this program is that the user can evaluate up to three independent blocks with varying strutural systems (which effects the R in each appropriate direction). The designer must remember that the blocks must be tied together and the walls in the cumulative line of shear must be balanced by deflection. The user is cautioned to reference the code and appropriate maps to evaluate the correct distance to source, soil profiles and seismic source type. The appropriate base shear will be evaluated for each system type in each block represented.
C) Structural Systems: The program allows the definition of three structural systems - with two systems which can be applied to any one block with only one system for each direction of force. For example, the designer must chose the worst case structural system for each block and for each direction of shear. If the system is a combined system (ie, pendulum or embedded columns and plywood shearwalls) the designer must stipulate the worst case condition or the appropriate lowest R value. The results are used in the analysis of each block at each level and in each direction.
D) Loads N-S and Loads E-W: This is the Diaphragm Deflection Analysis. To save using the pageup and pagedn keys, a worksheet has been devoted to each direction. Each block consists of up to three stories and can be divided into as many as 10 lines of shear. This worksheet distributes diaphragm shear only (typical multistory lateral distribution). The block may have different spans between shearwalls and different gross and net diaphragm depths at each level. The Loads sheets treat each block as a flexible diaphragm and design deflection between walls as simply supported beams. Only after the diaphragm is determined to be rigid or flexible will it be treated as a continuous beam for distribution of shear to the the resisting walls (not completed as of this date).
The spreadsheet also calculates wind loads at each level for comparison to the seismic load. The distribution of shear into the diaprhagm is based upon the controlling shear between each grid line. Therefore, wind may control in the first gridline (where the diaphragm depth is small and roof material light) while the adjacent grid line may have seismic controling. This produces the most conservative results.
This brings up a questions as to the using shortcut methods to circumvent rotational analysis should the diaphragm prove rigid. One engineer suggested that the stiffness of the resisting walls is critical and that it is not suggested to simplify the approach by using a conservative uniform load across the entire diaprhagm rather than the actual loads attributed between grid lines. However, the seminar methods suggest that the worst case loads be considered in the design of resisting elements. These will not be true to the torsion determined by the difference in distance between the center of mass and the center of rotation as any changes to the implied applied forceses will shift the distance between the centers. Anyone who cares, may wish to discuss this with me to see if it is not appropriate to determing the worst condition across the entire block and to apply only those loads so as to create a more realistic model for the determination of diaphagm rotation.
The program will determine the number of stories from the previous Structural Systems sheet and complete the analysis for deflection.
The deflection formula is broken down into its four basic parts; beam deflection, shear deflection, nail bending and slippage and chord slippage. The designer has a virtual complete choice of materials which determines the modulus values G and E. The user also has a choice of Wet/Dry and Dry/Dry materials that will effect the use of additional reduction values applicable to nail slippage and bending.
Chords are considered to be spliced with 16d nails and the constant for 1/32" or half of the diameter of the 16d nail is used in the Chord Slippage analysis. If any other nails are to be specified, the formulas must be changed accordingly. Since the code is specific about chord splices, the formula assumes the code designated nailing schedule.
Soon to be released:
The program is far from finished. Some time ago I distributed a rigid diaphagm spreadsheet created by James Lord, SE. It is my intention to incorporate the spreadsheet into this one. James Lord's name will appear as author of this section of the spreadsheet and is entitled to all copyright protection noted above.
David Merrick PE has donated a macro routine that will allow for the rigid diaphragm geometry to be automatically evalutated from an open AutoCad DWG file. This will save a considerable amount of time in the process of defining the diaphragm boundaries and the location of shear elements. David Merrick will be credited as the author of this macro and will be entitled to the same copyright protections as the rest of us.
The shearwall design portion of the program will work similar to the diaphragm deflection design. The basic deflection formula will be divided into its separate components so as to allow the user to identify those elements that are grossly variant. This will allow the user to focus on the most critical elements that need improvement in order to save time and iterations in the wall balancing section. I would appreciate any help from anyone who can create a macro or program that will automate the process of finding the most ideal solution.
I would also like to create a floating menu system to speed up movement around various portions of the spreadsheet. I would also like to automate the printing process using macros. This is fairly easy but will require some time - which I do not currently have much of.
Any help in the continued creation of this spreadsheet will be greatly appreciated and all credit will be given where applicable.
Finally, it is imparative that we have a good manual or set of help files. Can anyone help create these as we continue the creation. these will help those who follow in our footsteps.
In order for changes to occur consistently, I request that all improvements be submitted to me at SEAINTONLN@aol.com. I will insure that all revisions and corrections or improvements are combined and documented appropriately.
Please feel free to write me and let me know what you think. I can't try and make one program that will satisfy everyone so any major changes will be left to each of you. I will, however, correct any errors or misinterpretations of the code that need addressing.
Compliments are always appreciated as I would really like to know how useful you found this tool to be.
Sincerely,
Dennis S. Wish PE
seaintonln@aol.com
Final Disclaimer:
This software is not in any manner associated with
Structural Engineers Assoication International,
July 8, 1999 Beta Version 1.0
|
