Integrated Excel workbook for Wood Structure Design

Right now we have a spreadsheet for all lateral portions of the building, wood studs, diaphragms, shear walls and holdowns. We then use separate spreadsheets for beams, columns, spot footings and continuous footings. We also have different lateral spreadsheets depending on how many stories single, 2, 3 or 4 stories. Combining all this into one spreadsheet would be nice, especially for revisions. Right now, we don't save a copy of each spreadsheet we use in each project folder. We have a scanned pdf of the final calculations. The calculations for each structural member displays enough information that we can quickly reenter into a spreadsheet if we are making revisions. Would be nice if a beam span changed and the revised reactions were then used to revise any other affected previous calculations. We discussed before that you had programmed a spreadsheet for continuous girder truss loads, what do use for the I when you don't know how many plies the girder truss may be?

I will get into girder trusses in a couple of days. Basically I came up with a method of estimating reactions for 2-span GT's, and modified this workbook which is available for free down load.

http://www.steeltools.org/resources/viewdocument/?...

The above Excel titled "beamanal" will be used to more accurately estimate 3 to 5 span GT reactions. It is still an approximation because truss design software takes into account the variable I of the truss - so if you have a 2-span girder truss of equal spans, the center reaction will actually be higher than 1.25 x trib....but I can't do anything but to still use the 1.25 for purposes of discussion now.

To get started, I am offering this design criteria - its data is was linked to all the other sheets but I broke those links.

It lists load types as 3 different roof and 3 different floor types, and has various data tables for wood stresses, etc.

The print layout is basically between the gray margins. I don't password protect any cells. Most input is into light blue or medium blue cells. There are cells with a formula you can override as req'd, those are a light or medium green.

• http://files.engineering.com/getfile.aspx?folder=815e397b-ef6b-492d-8c03-5d

The next topic will be estimating truss and girder truss reactions. The Bldg Dept here requires the EOR to specify hangers, so we need to estimate number of truss plies.

Also it is a good idea to have the Excel calculate the number of 2x4 or 2x6 studs to support GT's, and check wood bearing stresses etc.

Here is a values-only version of my typical Excel so you can understand the next discussion.

Basically, to estimate the number of plies in a GT, have the Excel calculate the required bearing length, or use a Simpson TBE4 or TBE6, until the bearing stress of the wood stud top plate is not exceeded, nor the bearing stress of the GT bottom chord bearing stress is not exceeded. It helps to copy the Simpson catalog for the TBE values- you will see that on this on Excel, and the next step is automate all that on Excel.

But to estimate the reactions of a multispan GT, let me discuss that tomorrow.

• http://files.engineering.com/getfile.aspx?folder=1ec82e6a-da78-4eb4-ba96-e2

In the Excel attached to the previous post, we are looking at GT1 a 2-span - the reaction at the interior is the 5883# shown in cell L20, we selected 2x6 supporting wall, we select 2 plies in cell G22, and H21 shows a reqd brg length=4.57", less than 5.5" for the top plate width, so we are OK and we don't need a Simpson TBE6.

" Would be nice if a beam span changed and the revised reactions were then used to revise any other affected previous calculations. "

The way I do that is I have one worksheet for girder trusses (up to 36 GT's), and another worksheet for beams and headers design (up to 112 of them).

By using vlookup and match and offset etc, all the reactions in a data table on a separate worksheet, and by using my labeling codes such as GT3R, B31CM, for example, I can input any of those codes anywhere on any other worksheet and the correct reactions are always automatically updated.

If necessary, I can mock-up a simplified version an actual working Excel if you would like, I just need some spare time this week.

Here is the "subroutine" as it were for selecting Simpson TBE -

• http://files.engineering.com/getfile.aspx?folder=a77b1f30-11be-4c74-af19-05

The problem with the capability to calculate reactions for multi-span members is I ended up with an Excel file too large in MB to even run.

Most times, it was only a 2-span, with very few 3- and 4-spans occurring.

I solved this by having only one copy of beamanal in the workbook to solve more than 2-spans, and coming up with an empirical method of calculating the interior support reaction for all the 2-spans.

It is approximate but fairly good if the ratios of beam spans and load intensities is not too much.

• http://files.engineering.com/getfile.aspx?folder=906548de-5378-4849-b9fa-ac

Here is the actual Excel file for the above

• http://files.engineering.com/getfile.aspx?folder=84b71573-d393-489b-8521-c2

AELLC,
I have already learned two things. That the beamanal spreadsheet can make my life easier by using it to make my simple beam programs able to do multispan beams by embedding that program. My programs now use moment distribution and slope deflection to plot moment and deflection diagrams. Also, I am not aware of the offset function in excel. After messing around with it a bit I think I have some spreadsheets that I can definitely use it in. So if I am understanding you correctly, you have a database sheet in your main spreadsheet that has all of your results. You use different lookup functions and this offset function to pull the data to where you need it?

jeff,

The Vlookup, Hlookup, OFFSET, MATCH, and INDEX functions are all powerful tools to look up values so we can eliminate having to manually look up numbers and input them manually elsewhere.

I am working on a mockup (my original Excel is now too complicated to see things easily)- but I can finish the mockup only if things slow down for my work later this week.

Here is a basic example using OFFSET and MATCH (see cell L27)

• http://files.engineering.com/getfile.aspx?folder=9ba828aa-a10a-411c-952a-95

Here is a different example showing how you should understand how INDEX and MATCH performs the VLOOKUP function.

• http://files.engineering.com/getfile.aspx?folder=9f16bd48-8a0f-4b3e-b0a4-f4

oops post script to the above post -

VLOOKUP is a shortcut instead of using INDEX and MATCH...but you should understand how to use INDEX and MATCH because sometimes it is more expedient. Believe me.

AELLC,
I will take a look at your last posts. I messed around with the offset and match functions and eliminated some nested if statements in a couple of my spreadsheets by making dynamic ranges. How did I not know about offset before? Would have save me some programming time for sure. I always use vlookup and hlookup, I will look more into index as that will be able to help make my dynamic ranges work better.

Not having dynamic ranges previously was definitely hampering my spreadsheets power as many times I would be limited to the amount of nested functions. Which in the past was 8, I think it has increased with this latest version of excel we have.

I can see how it could be more expedient. Especially if you have a table, you write one function for everything, no messing around with static column numbers or row numbers.

Ok. After our sidebar on excel functions lets get back to what we were discussing originally. I think I am at the point where if I did combine too many spreadsheets, my programs will get too large to run. Currently, my beam spreadsheets are at least 5 MB. They are the largest and probably the most difficult to integrate into one large spreadsheet program. Your girder truss sheet is pretty large, i was tying to figure out how many uniform and point loads you used. It looks like there are 4 of each? I have started making a sheet to insert into my lateral workbook that can then calculate the reactions using superposition using the typical beam loading calcualtions. However, that gets me back to the variable I (maybe unknown to some degree) that can occur. The numbers you used in your examples assumed a uniform I. Do you consider this close enough for the reaction loads? I guess you could estimate by creating an equation for I based on the roof slope and roof truss plies, but you would need a few different equations, one for each typical truss profile that are typically used.

jeff,

First of all, it is good to hear that you are finding new ways to improve your Excel.

I would like some sort of example from you as to how dynamic ranges work for you. It is one feature I don't know yet.

My workbook is currently about 4.8 MB.I don't know how much that would be if it was downgrade to .xls, which tends to be much larger than xlsx. Perhaps I have minimized load cases compared to yours, and have done some things more efficiently. The seismic features are very minimal also. Perhaps if you have 2 or 3 separate workbooks with linked data, that will make it run OK, but that is a question for the Excel gurus in Engineering Spreadsheets forum.

I am not worried about the variable I of trusses - think I already have a better handle on reactions than most around here. Our roof live load is non-snow and much more unlikely to be maxed out in reality than if it was snow. I am going to study some more truss calculations next week when I get some free time.

My sheets are mostly patterned with 3 uniform and 4 conc loads, plus 1 uniform and 1 conc on 1 cantilever. (The 36 GT and 112 beam/headers). It also follows that for dead loads on shearwalls. I have a beam analyzer sheet capable of 6 uniform and 7 conc, plus 2 uniform and 2 conc onto left and right cantilevers. That can link reactions shears and moments and deflections to one of the beam/header design types to get a design.
I found a way to input ramp loads as 3 different uniform loads, which is also automated.

Speaking of nested functions, if you have something with a lot of IF statements and it is a case where you can't use data table, I like to use the CHOOSE function.

Finding a more accurate method of predicting truss reactions may be near impossible- look at this 3-point bearing girder.

• http://files.engineering.com/getfile.aspx?folder=bde5f42d-92d5-418a-9372-d5

Here is a "working" mock-up of a workbook - it may be buggy, I checked this minimally.

Green cells contain formulas

Normal input into light or medium blue or gray cells

If I don't succeed in attaching a little hand-written diagram, I will attach it in my next post.

This only addresses dead load reactions, it doesn't do the complete design of a beam - this is all to illustrate how to link data and how to input a beam or girder truss label instead of actual load quantity in pounds when you have one bearing on another beam or shearwall.

• http://files.engineering.com/getfile.aspx?folder=dc8f5b57-2167-48f4-8892-33

Here is the hand-written diagram and notes

• http://files.engineering.com/getfile.aspx?folder=0786785d-9c5b-4072-a04e-3d

errata -

Shear wall design sheet - uplift (cells H37 and H38) - a negative value means no uplift.

The next topic will be how to get those pesky continuous girder trusses and beams figured into the Workbook Example, but today is my B-day so am taking the rest of the day off.

To get started, study the attached handwritten sheets.

The implementation of this into Excel is not as easy as it sounds - I an currently working out some bugs, and will post the working Excel mock-up as soon as possible.

My Design workbook currently does have the 2-span approximate method as described above in my posts Mar 10 19:10 and 19:11 - that is working fine, and was much easier to program.

• http://files.engineering.com/getfile.aspx?folder=d15eb757-ce32-42fd-983e-c2

I have been trying to figure this out for 3 days, help please -

all explained on attached .xlsx file.

• http://files.engineering.com/getfile.aspx?folder=76493388-fedd-4613-8bb9-78

Sorry, I forgot Rule #6:

6) "CONT" is manually input into col C only if suffix of col B is "A". I don't want "CONT" automatically generated in col C, it has to be input in only one instance on the entire worksheet.

Sorry, this one is improved. Discard Book 12.

• http://files.engineering.com/getfile.aspx?folder=9adc323d-c625-4e17-9a09-7b

Don't spend any more time on this, I started a fresh approach and am making some headway. It may be very inelegant but it seems to be the right direction.

Just don't waste any more time on this - until I notify otherwise.

Well, all was for naught. I am getting absolutely nowhere. If no one can find the solution to this, I am giving up on this particular topic, closing this thread.

Sorry - I never encountered such a roadblock before, and I need to get back to my billable projects.

I did ask this same question in the Spreadsheets forum.

Good news, IRstuff solved the problem, and I can now get this going again, but I need to put in some billable hours today.

To re-state what I am trying to accomplish here, I am trying to improve an Excel to make it more productive (functionality).

1) The first goal is already met - to have the capability of a long list of truss and girder truss reaction solvers on the first sheet, and beam/header/rafter/joist designers on a second sheet. All simply supported.

The example workbook I have posted here calculates only dead load reactions to clarify the process. My original workbook is mostly impossible to understand because of its complexity, and the only way I can modify it is by judicious use of those cell precedents/dependents arrows.

2) The problem arises with a continuous beam or truss/girder truss. We have to "detour" to a separate worksheet to manually input spans and loads into a separate continuous beam solver, being the "Beamanal" Excel. BTW, that is a brilliant Excel.

3) The goal here is to input everything into the regular list 1), whether simple supported or continuous. All switching to other sheets or workbooks should not be necessary.

To any interested parties:

I am about 95% there, I am encountering various bugs that are being resolved and I am asking questions to the Excel gurus over in the Spreadsheet forum.

AELLC,
Sorry to leave you hanging. I forgot to check the box to be notified of responses. I am new to dynamic ranges, and they can really help when programming with lookup functions. One example where I used it was since I am in a higher seismic zone, I routinely need to check both wind and seismic uplift forces and see which governs. This gets further complicated when you have endwall, corner, and midwall allowable uplift loads. So I used offset to change the range my lookup up function is looking in depending on whether the holdown is at the endwall, corner, and midwall. I actually have the programming all in one cell that checks the wind uplift and seismic uplift and returns the worst case required holdown. I took that programming out and provided just one cell for wind and one cell for seismic so it would simplify the equations to illustrate better. I have removed the formulas for the wall segment overturning and net uplift and just entered the numbers from my spreadsheet because a lot of it is linked to a background sheet.

• http://files.engineering.com/getfile.aspx?folder=f075da32-1810-4ce2-854d-be

jeff, thanks for the interest. I felt a little goofy talking to myself for days, hehe.

On a related topic, the Excel gurus expressed the opinion that INDEX is more efficient than OFFSET, and re-calculates much faster. Both functions do the exact same thing, it is just different syntax.

However it appears to me to be a non-issue because my computer has no noticeable time pause at re-calculation. Just be familiar with both functions.

My spreadsheet is very similar, and I have additional detail info for a.b., shotpin (interior wall only), LTP4, etc spacing.

It also has the blocking spacing for the detail where the interior shear wall is in between 2 parallel roof trusses.

I was updating my excel spreadsheets for both a single story and a two story steel moment frames and it dawned on me. My spreadsheets use moment distribution to calculate the moments and frame member forces. My spreadsheet also checks those numbers by solving slope deflection method equations by using matrices. The single story spreadsheet matrix is 8 equations and 8 unknowns, which would be the same number of equations as a three span continuous beam/girder. My two story frame program has 16 equations and 16 unknowns, and excel has no problem with the calculations. The matrix math is much more compact than the moment distribution and a 10x10 matrix (a four span continuous beam I think) would fit nicely off to the right of your cells in the BH and GT sheets. I also agree with you that it is probably impossible to develop something to calculate the reactions with a variable I truss. You could probably do it for the hip girders and gable girders, but it would break down in the more complex trusses. The slope deflection equations would get us as close as possible to the actual reactions without your scaling method. We would have to program the end spans of the matrix to change for pinned of fixed conditions and the fixed end moments based on the entered loads, but that would probably be simple. The slope deflection equations and matrix would also let you have a spreadsheet that does not need the beamanal spreadsheet. I have attached a pdf of my moment frame spreadsheet cover and the Dead load calcualtions so you can see what i am talking about. The slope deflection equations are listed on the right hand side of the sheet. Its late tonight, but I'll see what I can do tomorrow.

• http://files.engineering.com/getfile.aspx?folder=6e3e62da-5bdd-4033-859b-39

jeff,

What you are talking about is exactly what I have been trying to implement, but found nothing available. There is a lot of free stuff out there, but it is too academic in nature, and impossible to modify because of complexity and VBA etc. I don't know how to actually compose a stiffness or flexibility matrix analyzer, I just have some peripheral knowledge of the topic.

The solution I am nearing now provides almost the same end result, but you will see is a bit clumsy. It involves only one copy of a modified Beamanal, working in the background. I tried copying Beamanal x 148 (one for each possible design label) but of course that resulted in a ridiculously large excel file. With different load cases, the number of copies would multiply by that. (Except combinations that would be handled by combining shear diagrams)

BTW, Beamanal uses slope-deflection and stiffness matrix methods, but it is a complete analyzer and designer with many features, and a fairly large file. You would have to pare your analyzer down to the bare bones - actually, all I need are all the reactions and the maximum deflection in each span, and from that I can calculate moments exactly.

Anyone else reading this discussion from the "loft", please chime in with yeas or nays.

AELLC,
See my attached file. It matches beamanal exactly. I also did a two span beam to check against too. As I noted on my spreadsheet, I think the statics are right, but it needs to be tested more to be sure it always works to solve for the span reactions. As you can see, the matrix really doesn't take up too much space. Because I don't have any of the extra beam solving stuff, the file is only 13 kb. So you should be able to add a matrix for each girder or beam slot and not add too much size. It's funny, I never thought of this before, until I was updating my moment frame programs. It uses moment distribution because it is the easiest to program. I use a math program I have from school to run slope deflection equations to check my frame forces and make sure they are right. This last week I did some research on matrices to see if I could solve slope deflection equations in excel so my spreadsheet could check itself. This was last week and it still hadn't hit me that this would really help with solving for girder reactions until last night. I was too tired to get the statics right, but this morning I got the span reactions to work out for the examples I was using. I need to come up with some programming to make the matrices dynamic so they would shrink and expand depending on how many spans we have. That would get a little complicated, or you could just have multiple matrices, one for two span, one for three span, and one for four span and then just use IF statements to pull from one matrix or the other depending on how many spans you have. Let me know what you think.

• http://files.engineering.com/getfile.aspx?folder=b8633d45-9feb-4a38-a201-a2

"or you could just have multiple matrices, one for two span, one for three span, and one for four span and then just use IF statements to pull from one matrix or the other depending on how many spans you have. Let me know what you think. "

I believe that is the method Beamanal uses.

Your spreadsheet there looks much more concise than Beamanal, but I still need maximum deflections.

I am about 99% completed my method. Either later today or tomorrow. But the first version of it will not show deflections.

Deflection will be more difficult. My beam programs just calculate the areas under the moment diagram for the angle and then the area of that for the deflection. I haven't had much luck with the deflection with just equations. I suppose there may be some sort of deflection equations out there I can find. I'll look into it.

I have an approximate method for deflections in simple support beams for each of my 116 design labels that is surprisingly accurate. I also have a more detailed beam analyzer (just one design label) that calculates deflection by formula extremely accurately, but it is a very low-tech method that takes a huge amount of cells to tabulate.

If I could have applied all that mechanics of materials that was taught in University, it would be much better. I think what happened, was when I first started working, we were forced to use charts, graphs, the AISC tables, and early computer software, and I lost touch with the engineering basics, i.e. slope-deflection, virtual work, etc.

Luckily, I have been in charge since school of making spreadsheets for all of our calculations. It started as a way of teaching me how to calculate stuff, but now it has ended up to where the only third party software we have is Forte and a light gauge steel program. Everything else we do with spreadsheets that I have created. That is the reason why I still am able to remember slope deflection and moment distribution. I don't remember any virtual work or conjugate beam stuff, but the slope deflection and moment distribution have served me well. I will look into calculating the deflection tonight. I imagine I may be able to write an equation or two and add it to the matrices.

OK here it is, probably still buggy.

Make a copy because it is unprotected, and we will be overwriting cells containing formulas.

It isn't very clear at first but follow the list that I started in above post 18 MAR 14 9:15

4) Open Workbook example V3.35 to GT tab

5) Light and medium blue cells are always input cells. Medium green cells have formula but are overwritten according to the following rules. Certain medium green cells change to orange-tan when overwritten.

6) GT3A is the first continuous span. Select CONT in cell I8. Exact reactions appear in cells D8 and H8.

7) Type the same numbers as D8 and H8 into C8 and G8, respectively. Do not copy and paste, that copies the formula and we don't want that. Or, get fancy, and COPY/PASTE SPECIAL/VALUES for extra credit.

8) Repeat similar to 7) for GT3B and GT3C. Leave CONT in cell I8 and do not put CONT in any other cell, for now.

9) GT5 is simple support, no action needed.

10) Note that RT1A shows a RT1AM scaling ratio=1.25. That is a non-exact "placeholder". We need to repeat the same procedure as 6) on down. Now delete the CONT in I8 and select CONT in I58. Continue with similar to steps 7) and 8).


Do similar actions on the BH tab. (But remember to delete CONT in cell I58 on the GT tab first. )


Now we have all the exact reaction values for every continuous member "frozen" as it were on GT and BH, all by using only one copy of Beamanal. The simple support cases are still "volatile" as it were, and input can be changed on them later if needed. Any change to the "frozen" continuous members reactions will simply require inputting "CONT" into the appropriate I column/row cell and copying the formula for the computed exact reaction (yellow cells) from an adjacent design label that is unused or is a simple support label

This will continue as we look at the DATA tab, tomorrow or later.

• http://files.engineering.com/getfile.aspx?folder=4ed99744-2398-4ed7-8224-b6

hot damn, Jeff, I was going to pay someone else to come up with all that but he never produced anything.

I went to a Simpson Strong-Tie seminar this morning, and all this week has been VERY slow work-wise.

I didn't realize what a blessing it was. I can't imagine how much money my boss put into me the first few years for spreadsheet creation. But I am glad it worked out like it did, I feel like I have a much better handle on things than what some other employees did that no longer work with us that basically sat down and learned how to run the spreadsheets. If the power went out they were in trouble. I'll look at this spreadsheet tonight.

At about 4:45 PM I got a very alarming email from some truss mfr that a major girder truss had sagged badly when the concrete roof tile was stacked on.

So that should keep me entertained all day tomorrow...their suggested repair looks very bogus.

When I was an employee, for years we weren't supposed to dabble with the company's Excel. We were supposed to accept the spreadsheets as they existed, but the coding wasn't protected. I have a story about that, too. I worked on them at home, and that went on for many years.

The first versions of Enercalc were actually running off Lotus 1-2-3. But they did almost nothing to improve and de-bug their stuff, they merely kept it current with the new Codes and converted it to Windows. So when I became less reliant on Enercalc, I did a lot more Excel.

When I started the excel spreadsheet we had weren't very advanced. I made new spreadsheets sometimes building off old one sometimes starting fresh. I had to show that they were accurate by testing them for a while along side the main spreadsheets before they were "adopted" into our library. Now I have them locked down with a copy protection and passwords. I wrote a vba script that checks for a license file and if it's not there it clears the contents of every cell in the workbook. Hope your truss repair went good. I'm very curious to see how you have estimated deflection in your program since the only way I have come up with getting an accurate deflection is by calculations the areas of the moment diagram and then the rotation diagram. Definitely not a very concise method. It appears that this is the method used in beamanal to calculate deflections.

1) The truss repair was a PITA, but I got a lot of help thanx to Eng-Tips. More on that later in the main forum.

http://eng-tips.com/viewthread.cfm?qid=361845

2) Right now I haven't got a thing for deflection of multi-span. I only have deflection for simply supported.
Isn't the method used in Beamanal exact, by that I mean within 0.97-1.03?

3) I don't even exactly recall how I estimated deflection for my 112 design labels...I will look into it tomorrow. It is very crude but close enough. I am more concerned about 1.03 of stress than of deflection because headers are usually very low deflections compared to allowable, and the longer beams being glulams or steel beams have a standard or natural camber that causes the deflection study to be conservative. Additionally, I have a feature where dead load deflection of glulam is compared to the standard camber, and some beams are actually controlled by that (I never specify special camber in residential beams)

4) The method I used for my one-label beam analyzer is low-tech but accurate. It populates a huge expanse of cells. It calculates using those formulas from the end of the Beam Section in the AISC Manual. Every ounce of load every 1/36 segment of beam is accounted for, so instead of crudely adding up maximum deflection for every load occurring at a different location, it adds up every deflection at every 1/36 segment for a very accurate deflection. I compared it to the Enercalc beam analyzer, and it was even a little bit more accurate for cantilevers.

5) The "funny" story I had from a long time ago at the place where I started to study their early Excel efforts, this one guy who was always assigned to design concrete tilt-up was actually using their Excel w/o realizing it was a step-iterative, circular reference sort of solution - you had to actually hit the calculate button to get the answer (manual calculation in Settings), but there was nowhere it told you so. Someone previously but no longer there had written it, and I was nosing thru its inner workings. Well this poor guy had done something like 2 years of projects w/o realizing nothing was happening...I guess he thought the same rebar worked magically for all cases. His face actually turned white.

6) I am going to see if I can find some really early Excel for beam analysis.

I couldn't find any really old Excel on my oldest computer that is still hooked up.

I did find this, it is a simple support beam analyzer that appears to use stiffness matrix method for finding exact deflection.

• http://files.engineering.com/getfile.aspx?folder=6d70d64e-559f-45ef-ad69-8e

^^^single span, not simple support^^^

It seems to be easy to set support conditions to fix or guided etc,

To continue from 20 Mar 18:18,

Now the DATA tab is all sorted out - and it shows zeroes properly where the visible sheets GT and BH show blanks because the were actually having the text value ""

We need zeroes to avoid all the ERROR messages as the DATA numeric values will be used in subsequent versions.

This DATA table goes a long way to automate more features into the workbook-- we can even have a Footing Design table to greatly speed up footing selection and avoid that really tired and burned-out kind of feeling nearing the end of as design project. (I think footing design always gets fast-tracked and tends to have errors because it is the last thing to do)

• http://files.engineering.com/getfile.aspx?folder=131e4002-455b-4b99-bf39-c6

Looking more at Beam1 ( 22 Mar 14 0:45 ), it appears to be more of an academic example, and not very useful for real-life situations.

To calculate the deflection anywhere in the beam, would it be necessary to include an extra node at the desired location?

To automatically calculate the maximum deflection, would it be necessary to add a lot more nodes, say 30-50, evenly along the beam span? Seems to me that would get out of hand quickly, and perhaps that is why Beamanal calculates deflection by slope-deflection methods.

As I said above, I have only peripheral knowledge of matrix math and stiffness/flexibility analysis methods. The professor touched on those topics briefly in University, but I wish he had gotten into it in depth.

I was thinking the same thing too about beam1. In my beam programs I graph the shear diagram using multiple points. I then hae another row that calculates the area under the shear diagram for the points on the moment diagram. I use the moment points to create an M/EI diagram to calculate the rotation at the same points along the beam. I then integrate again by summing the areas under the M/EI diagram to come up with my deflection. Because I am using a lot of points it is extremely accurate and is all built on just the beam reactions ad shear diagrams rather than calculating it separately for shear, moment, and deflection. So you are saying what I ran into on my beam programs. How do I determine the max deflection location without calculating the deflection at many points? When you start to get different types of loading it is difficult enough, but when you start adding multiple spans it gets even tougher. So to add a slope deflection equation or use a stiffness matrix you have to have an equation for that point and solve for the deflection. You would have to iteratively go trough selected points until you found the point of max deflection.

Here is a pdf of a sample beam from my program. I'll make a second post with the background sheet that calculates deflection to further illustrate how I calculate deflection

• http://files.engineering.com/getfile.aspx?folder=ddd2035b-7ffc-4e14-ba42-a9

"I use the moment points to create an M/EI diagram to calculate the rotation at the same points along the beam. I then integrate again by summing the areas under the M/EI diagram to come up with my deflection."

Yep I screwed up on my Excel, doing that is a lot more clean than what I did. Ooops.

A excel file is attached that shows the background programming to calculate the maximum deflection. I had to remove all the links, but it will still give you an idea. Essentially it figures separate shear for each entered load and sums them all into one shear diagram. I then follow the method I outlined above using area under the curves to integrate and get an accurate maximum deflection.

• http://files.engineering.com/getfile.aspx?folder=380d8de3-cca8-4d91-9007-2d

Here is a Values-only version of my single label beam analyzer-designer (so you can see how I like to use color to improve readability)

It was set up to improve productivity and to satisfy the plans checker, and doesn't show all the intermediate and back-up engineering info such as Cv etc.

Those are tabulated on another sheet that I usually don't need to refer to.

The thing that really snaps on this one is can just as easily set up any steel beam, channel, double angle, HSS section etc, just as easily as wood - it has data input cells that don't print out, just to the right of what you see printed out.

If some other beam or GT bears on this beam, it is easily input just by typing in its label code such as B1R and the reactions are automatically figured in. Live load reductions can be managed easily.

• http://files.engineering.com/getfile.aspx?folder=e70581ef-d012-4de7-a389-6c

jeff,

I looked at your 13:25 file - is it saying you tabulate the beam span into that many segments?

I only use 36 for mine in the simple support span portion (mine has cantilever each end)

In my 112 design label worksheet, I used a very crude method to calc deflection but it seems to work well.

I need to study it again because I did that at about 10 years ago and don't recall the details.

jeff-

We need to run a test problem, see what deflection results.

Look at this

I hope no one perceives this as student posting hehe

• http://files.engineering.com/getfile.aspx?folder=902ea802-5f0b-406b-9d80-b6

Here are results -

The AELLC BH method is that crude method I use for my 116 labels because it is very concise

But I was surprised because it gave such good results this time.

The Beamanal results are slightly off because that one is not capable of single span/cantilever - I had to "trick" the input

Where I wrote in black for Beamanal results I goofed - the location was correctly written in red above.

• http://files.engineering.com/getfile.aspx?folder=08a196a8-a47a-4dbc-93ca-92

Yes. I was wondering if you would ask about that when I was attaching the file. I have it split into a ton of segments, but when I was first creating it I wanted to make sure there was extremely low rounding error with whatever programs I was checking it against, so I just made extra sure by providing 1000 segments for each cantilever and span. I ran it in my steel beam spreadsheet and I get the same as Beamanal. I have attached a printout from my steel beam spreadsheet that shows the reaction in the upper right and the deflections are shown down at the bottom.

Ra = 1159 k
Rb = 1021 k
Midspan deflection = 1.201" @ 8.96'
Cantilever deflection = -0.542" @ 24'

• http://files.engineering.com/getfile.aspx?folder=3a898fb1-85f5-41bd-971a-1b

I do so much tract home and used to do so much multi-family, there is a lot of what we would call routine little headers.

We always did those by standard chart or load tables (plf vs span) methods in the past, but Excel offered us a new opportunity to make design more quick and efficient - like occasionally you get a short header with a huge girder truss point load on the span. It also was useful that the trimmer or post was designed right there too.

So to make the sheet productive, almost all intermediate stuff was hidden, where there is input that frequently does niot occur, we put those cells just off the print boundaries.

So I came up with what I have been calling BH, which is a total of 116 labels, 4 to a printed page.

Another feature is frequently you input a header, and you had forgotten that you had previously just done a "worser-case"...the spreadsheet automatically shows the label code do you can lump the 2 into one design label.

So every "subroutine" had to made very compact (the deflection calc especially)

See attached

• http://files.engineering.com/getfile.aspx?folder=3e8ddf0d-3ab9-491f-8f5f-f9

OIC my LDM sheet gave the exact correct defl on the s.s. span, but the location was off just a tad because the beam was split into only 36 segments.

jeff

How does your steel beam design sheet pick the correct size out of all those AISC shapes, and does it "filter" out beams too wide, etc with pre-set criteria?

The above was a big headache but I got help from the gurus over in engineering spreadsheets

The steel manual used to come wich a cd with a spreadsheet with all if the section properties on it. Now you can just download it from the AISC website. It's not 100% accurate since I had to estimate a beam weight as a percentage of dead load. They are really more like starting points for the beam size. And it finds the lightest weight section for the input loading just based on what section you select and what Ix, Zx, and Sx are needed. It usually is a section or to lighter than what you need.

OK I went with the old ASD because it usually gives the same answer as LRFD for the simple load cases I use here, and use ASD for all my wood design - just makes load bookkeeping easier for me.

I know how to do LRFD but don't see a need for it unless I was doing major commercial work. I believe ASD is no longer legal for steel but all the plans checker allow it because it will give basically the same answer . The only time I saw a big difference is when it is a steel composite with concrete deck.

Back on topic, I think for now I am going to use that method in 20 Mar 14 18:18 because that will give all the reactions, shears, moments, and deflections accurately albeit fairly awkward because only one copy of Beamanal is being used.

I can't think of a method to do all that for each of my 116 design labels because the Excel file would be too large to run, and if I had a pared-down flexibility matrix solver for each beam label, I would still have to have at least 36 beam segments per span, multiplied by load cases.

jeff,

Are you considering most wood beams and headers as being subject to outside elevated temperatures, that reduction factor Ct?

No. Everything I have read has made reference to manufacturing processes and steam or extremely high temperatures that the beams are exposed to for prolonged periods of time. I would consider the headers as interior beams since there is usually foam and stucco on the exterior wall and climate control on the inside. I'm not in the office so I don't have my 2012 NDS, but I found a poriton of the 2005 NDS commentary online, it is attached. The 2005 NDS commentary states "Tabulated design values also are appropriate for use with wood members directly exposed to solar radiation but otherwise surrounded by ambient air, such as members used in bridges, exterior balconies and stairways, and exterior vertical and horizontal structural framing." In southern Utah, we do have elevated exterior temperatures, although not nearly as much as in the Phoenix area. Due to the sun exposure here, most beams on exterior back patio roofs or other similar structures are not exposed wood. Most times they are wrapped and stucco is applied to the beams like the rest of the home. The majority of exposed beams I deal with are for lattice type structures where the members are so overly sized for aesthetic reasons that the reduction in strength due to extreme heat wouldn't make any difference.

• http://files.engineering.com/getfile.aspx?folder=cafc8d6f-df09-433f-8019-e8

jeff,

OK, thanks, I am going to think about this some more, not worry about the beams and headers that have inside air-conditioned on one side.

I think it is going to be a non-issue except for instance, major beams in the attic space, such as a flush beam that supports trusses, and framing in garages.

The attics here seem be at least 120 degrees 24 hrs a day for 6 months. Garages, not nearly as bad.

Yeah. I've thought a lot about attics too. It does say in that commentary that if they are ventilated per code that it isn't really a concern because the part of the truss chord in contact with the roof would be heated up but probably wouldn't be heated all the way through. I know none of the truss manufacturers here have done it, and I can't shine the trouble that would happen to start requiring that. Even commercial buildings that have plan check it has never come up once. However, you guys are typically 10 - 15 degrees hotter than we are and our lows are below 100 most times so you probably have a little more reason to think about it. What is typical down there?

I am reposting my last comment with some corrections.
Yeah. I've thought a lot about attics too. It does say in that commentary that if they are ventilated per code that it isn't really a concern because the part of the truss chord in contact with the roof would be heated up but probably wouldn't be heated all the way through. I know none of the truss manufacturers here have done it, and I can't imagine the trouble that would happen to start requiring that. Even commercial buildings that have plan check it has never come up once from the plan reviewer and the reviewers are structural engineers. However, you guys (Phoenix area) are typically 10 - 15 degrees hotter than we are and our lows are below 100 most times so you probably have a little more reason to think about it than we do up here. What is typical down there?

The low can be 92-94 and the high 110-114 for weeks it seems

IMO the attics aren't ventilated enough and I can't go into them anymore, not even mornings.

I know weve been talking about beams and girder reactions up until now, but how do you determine if a continuous footing can support a point load or if you need a spot footing in the continuous footing? Our footing program models the continuous footing like a base plate and it determines how far the footing can bend with full bearing pressure on it. With our typical footings, it ends up being around a 6'-0" length of footing (3'-0" from the point load on each side). We then take that length of footing and multiply it by the width and the remaining bearing pressure to determine a maximum point load the footing can support without a spot footing. So if our footing is loaded to 800 psf out of 1500 psf allowable, we would have an allowable point load of 6300 lbs for an 18" wide footing. Of course you would need the full 3'-0" of footing in a straight run on either side. If the point load is at a corner we show a spot footing, if the point load is closer than 3'-0" from a corner we will prorate the maximum load.

My method is low-tech - see attached

• http://files.engineering.com/getfile.aspx?folder=0e9bf818-4795-4d65-9eb9-f2

OIC I neglected to account for the uniform wall load that exists in the cont wall ftg to begin with

I could code (to make this very automatic) the above topic of conc load at wall ftg, but that would take a few days - I usually do my Excel tinkering 4PM to 6PM or so

Sorry to throw you guys off here but....
when you say "embed the beamanal program into your workbooks" what do you mean by this or better yet how do you do this?
In essence the idea is to send each beam input into the beamanal program but there is only one instance of the program (not one instance for each beam), correct?

EIT
www.HowToEngineer.com

RF,
No problem - I wanted this thread to be a discussion.

Quote (In essence the idea is to send each beam input into the beamanal program but there is only one instance of the program (not one instance for each beam), correct? )

YES - to keep the Excel file size from getting too large and slowing down the re-calc time (my Excel for instance has 36 girder truss labels and 116 beam/header/rafter/joist labels)

Read the "user's manual" of my post 20 MAR 14 18:18

Here is the latest Excel example of all this -

• http://files.engineering.com/getfile.aspx?folder=2d55cde7-c07f-45b1-aad9-22

AELLC,
Our footing program does do it automatically. We print the worst case continuous footing calculation from our spreadsheet and then just change to a second tab that is labeled max point load and print it. It gives a value for an interior footing with no thickened slab and a value for an exterior footing with a thickened slab. How do you take the eccentric loading of a footing at the corner of the slab into account? We have always assumed that with the load at the corner, there would be eccentric loading without a spot footing.

RFreund,
You can insert the beamanal spreadsheet into your spreadsheet and then link the cells. However, originally you would need a separate beamanal sheets for each beam you are calculating. AELLC has streamlined this method I think by using the embedded beamanal spreadsheet to calculate the loads and then "freezing" them by entering the numbers and overwriting the formulas in those cells. It has been a few days since I looked at his sheet he posted, so he may need to correct me.

Here is the version without all the reaction numbers overriding the cell formulas in GT and BH tabs (but this version does not have the latest DATA tab improvements)

I will post a clean more updated version in a few days.

• http://files.engineering.com/getfile.aspx?folder=19f17daf-93f7-4d00-8838-45

jeff-

Wall footing @ corner w/ conc load -I don't account for the eccentricity. I need to study if this is a significant problem.

"AELLC has streamlined this method I think by using the embedded beamanal spreadsheet to calculate the loads and then "freezing" them by entering the numbers and overwriting the formulas in those cells. It has been a few days since I looked at his sheet he posted, so he may need to correct me. "


Correct. The modified Beamanal is actually the CSB tab in my workbook.

"Our footing program does do it automatically. We print the worst case continuous footing calculation from our spreadsheet and then just change to a second tab that is labeled max point load and print it. It gives a value for an interior footing with no thickened slab and a value for an exterior footing with a thickened slab."

I still can't visualize that - can you send a pdf example? Or better yet, a values-only version of the whole excel worksheet?

A values only version is attached.

• http://files.engineering.com/getfile.aspx?folder=36d9f223-cac4-4432-a391-7d

jeff - thanks

I think got it -

1) Excel takes maximum plf wall footing (such as from roof trusses bearing on wall) - particular to the project - that value was linked from another sheet?

2) Excel computes and displays maximum allowed conc load that can be added a) to int ftg b) to ext ftg
- based on what is the "left over capacity" after the wall plf is deducted.

AELLC,
Yes. The loads come from the continuous footing calculations spreadsheet. You enter all the information for the continuous footing and then print. There is a second tab that says maximum footing load and all uniform loads, footing size and reinforcing are taken from the cover sheet where the information is entered.

OK, good thing I have time for all this. I have had very little real billable work for 2 weeks now.

I have 3 projects that are having truss problems, as you see I am starting a lot of threads in Structural Engineering - Other Topics, and in TRUSS Engineering, blah blah - but all that seems to be cleared up now.

Anyways, how I set up the footing design here may be quite unconventional, but I am going to welcome criticism.
It is very related to the OP because it involves a lot of linked cells from sheet to sheet, and all data entry has ben minimized.

Any one else in the "loft", let's hear from you, too.

"Wall footing @ corner w/ conc load -I don't account for the eccentricity. I need to study if this is a significant problem."



For 16" wide footing and allow=2000 psf soil brg, I get 5485#, is that close to what you get?

With my method?

I meant with the post load only, no continuous bearing wall load.

I am not very confident with this particular calc. There are too many assumptions. This calc is "gray", not black and white.

Yikes it is 11:20, I am getting some zzzz's

I guess I'm not sure which method you used. If I use your method just based on a 45 degree angle and an 18" thick slab and footing I get 4.44 sq. ft. which gives me a load on the soil of 8880 lbs. You'll have to show me your area you came up with. I have attached a drawing showing the area I came up with using your method. It also shows what I was talking about before about corner footings being eccentrically loaded. It may not be a big deal, but if you are trying to use the "legs" of the footing going each way there will be an eccentricity. If you were standing on a piece of plywood in an L shape on water and you stood on the corner, the plywood would want to tip at the ends of the legs. It may not add up to much, maybe the slab restrains/removes the eccentricity?

• http://files.engineering.com/getfile.aspx?folder=c74b90ee-79a6-4554-a54d-90

The continuation of the reinforced stem adds greatly to removing the eccentricity, but it is a very gray judgment call.

See attached

• http://files.engineering.com/getfile.aspx?folder=c17d01f8-3a75-4d0b-bfac-61

I agree with the max point load of 8880 lbs being unconservative, thats why I usually use spot footings at corners if the loads are very high. Did you calculate the bearing pressure with the eccentricity? I didn't go into it that far for the same reasons as you. The slab is doing something and the wall load along the legs of the L shaped footing will help remove the eccentricity as well. There is so much going on there that getting a calculation to model what is really happening is difficult to envision. Maybe I am overthinking it. For corner footings I usually see how much a spot footing would be the same width of the continuous footing, only square. So for a 16" wide continuous footing, I would use a 16" square footing and considering the wall load I would determine how much additional point load I could take, probably pretty overconservative, but there are not eccentricities and I have never had someone complain about a few spot footings here or there in the perimeter continuous footing. So did you ran calculations with the eccentric loading to come up with your max load of 5485 lbs? That appears to be in line because a 16"x16" square spot footing with no loading would give me 3556 lbs allowable point load.

Thanks, I am going to go with 5485 because 3556 looks too conservative.

I need to gauge how much I can get away with (value engineering)

I guess you could calculate the flexural strength of the thickened slab, but how would you determine if it was enough to remove the eccentricity? I calculate a 10x10 concrete beam with one continuous #4 bar has an allowable moment of 4341 lb-ft (LRFD level). Dividing that by 1.6 to conservatively reduce that to ASD level gets us to 2894 lb-ft. Dividing our footing by 1'-8" (the moment arm from our l-shaped footing drawing) gives us a point load of 1733 lbs at the end. If you multiply that by two since you will have a thickened slab coming from each direction, that gives us a load of 3466 lbs. That isn't including deflection, so maybe that gets reduced a little. But it could definitely help in increasing the allowable load on your corner footing by reducing or removing the eccentricity.

I considered the conc stem wall to be a conc beam to reduce the ecc.

How much did you consider it reduced it? Does it REMOVE it?

Not completely removed. It was a SWAG approximation anyway. I have to do this stuff all the time. Maybe it is a taboo subject, because no one else seems to complain about it - these gray calculations.

I would probably agree with that. I have maybe been a little overconservative with my corner footings. I think with everything that is there, a SWAG is about the best we can do. In all actuality, the nailing of the exterior sheathing probably helps transfer the load away from the corner as well.

Same thing about this statement - and do not throw rocks at me - I did not utter this statement originally, I am just repeating it.

"A typical tract house in the USA, in a non-seismic, non-hurricane or non-tornado region does not need lateral design engineering at all. No shear walls, no hold downs. Not even IRC required items. (Except temporary construction phase wind cross-bracing at the 4 basic corners of the house)

All the rooms taken as an assemblage of boxes fitted to each other with wood studs, gypsum wall board and gypsum ceiling board, AND the roof all together, comprise a surprisingly strong structure that will not fail in any way when subjected to normal wind loads."

We had some flooding a few years back where the river undermined some houses. Some of the houses had 50% of their foundation washed way yet they just hung there. Imagine the engineering involved to make a house hang over the river like that. There would be huge beams and deep foundation elements involved. However, we don't live in a world where engineering judgement can be the rule. Many times on homes we are just trying to show we have done some due diligence, especially where lateral is involved. My goal on everything I do is to be able to have numbers to back up what I do, even if they are not included in the calculation package, I have numbers somewhere to show why I did something.

Honestly I would agree with that statement. We are in a non-seismic, tornado or hurricane zone and I have yet to run a whole building lateral load analysis for a residential project. Besides designing tall walls and king studs I rarely ever even consider wind.

@ jeff-
yep DUE DILIGENCE - powerful concept.

@ jay -
LOL, I am glad I don't live where you do - being req'd to do lateral calcs is my bread and butter here.

The only thing that sucks about not having to do lateral calcs is residential design is boring. Even the new large custom builds are the same old-same old.

If you've fought with one contractor to put a steel column in the tall wall full of windows you've fought with them all.

Large custom builds are boring? OMG, not around here.

Residential and ESPECIALLY wood multi-family does burn you out, really badly. That is a huge reason that drove me to integrate my Excel workbook, ta-da.

I have never had to put a steel column in a tall wall. Mostly the outside deck or patio roof saved me.

I would say large custom builds can be frusterating, not boring. Here seismic also controls and it makes it a major pain to get your lateral forces through your structure and to the foundation. Many of my custom builds here though are high end and require steel columns at the least, if not steel moment frames. I have had about 5 homes in the last 6 months that needed steel moment frames.

If I spec'd a steel m-f, the contractor would kill me.

I have BTDT for large customs in SoCal, very nasty and gruesome processes.

The contractors I deal with know I won't design a SMF unless I absolutely have to. They trust me and know I have everyone's best interest at heart. Those contractors can be hard to find. BTDT? Can't figure out what that is.

Been There Done That

I have spec'd 5 Simpson Strong-Walls here. (the wood kind)

How many actually gotten built into the structure?

Zero, zip, nada.

Really? How do they pass inspection when they aren't there?

I had to cobble together a field fix - a lot of busted concrete, scraps of plywood, and hundreds of nails.

I pointed this out to Simpson and we are working on a solution.

I think this thread gets to chasing a of rabbits, but that's OK - back to the last major item, has does anyone have any yeas/nays, or advice, or favorite analysis methods on the subject of analyzing continuous beams and girder trusses quickly and efficiently?

The definition of a structural engineer: overdesign by a factor of 1.999, instead of the usual 2.

I think the reactions are easy enough, its determining the deflection without plotting a bunch of points to determine the location of the maximum deflection that is the problem for me.

If I had the time, I could "reverse-engineer" the Beamanal spreadsheet to figure out how it calcs deflections.

Right now, I am overloaded with work.

The definition of a structural engineer: overdesign by a factor of 1.999, instead of the usual 2.

When I looked at it it looked like it was plotting a bunch of points and then taking a maximum and minimum of the plotted points.

My problem is, I don't recall the engineering basics. Specifically how to use slope-deflection.

What my guess is, he uses slope deflection method to calculate deflection every 1/50 segment of each span, then use the MIN and MAX functions to pick out the maximum upward and maximum downward deflection, then uses LOOKUP to get the distance to the right of X=0 of the span.

It is probably the most efficient method. But I have found that, if you don't really need the max defl location that accurately - you need the max defl itself accurately.

So if you use only 25 segments, max defl is pretty accurate, while location is off quite a bit. But who cares, we don't need location that accurately.

See attached.

The definition of a structural engineer: overdesign by a factor of 1.999, instead of the usual 2.

• http://files.engineering.com/getfile.aspx?folder=cbe4bb1c-cf01-4349-80d6-6f

So, if I were to modify Beamanal so it calculates values every 1/25 span instead of 1/50 span, and weed out all the other beam design stuff, etc, I could get it down to a smaller file size and make it easier to reverse-engineer.

The definition of a structural engineer: overdesign by a factor of 1.999, instead of the usual 2.

The other extreme would be to reverse engineer my OWN Excel - I had devised a method to approximate deflection of simple support with cantilever one end, that is fairly accurate. Must have been 15 years ago, all I remember is that it compares the approximate area under the uniform loaded moment diagram and compares it to the point-loaded moment diagram. Some sort of ratio, if you had the maximum moment in ft-lbs being equal in both cases, what is the difference in deflection.

Sounds loopy but it works.

The definition of a structural engineer: overdesign by a factor of 1.999, instead of the usual 2.