## Integrated Excel workbook for Wood Structure Design

## Integrated Excel workbook for Wood Structure Design

(OP)

I have seen many Excel worksheets that do individual design tasks and I wanted to start a discussion regarding integrating all that into one large workbook to streamline the entire design process.

I have my own workbook that I have improved over about 18 years and wanted to share ideas, etc.

It is Excel 2010 but can be saved as 2003-2007 with minor loss of functionality. It has no macros, UDF, or VBA etc., just basic Excel formulas, and nothing iterative.

I don't want to get into VBA discussions because not everyone is familiar with that.

I have my own workbook that I have improved over about 18 years and wanted to share ideas, etc.

It is Excel 2010 but can be saved as 2003-2007 with minor loss of functionality. It has no macros, UDF, or VBA etc., just basic Excel formulas, and nothing iterative.

I don't want to get into VBA discussions because not everyone is familiar with that.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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?

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

all explained on attached .xlsx file.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

Sorry, this one is improved. Discard Book 12.

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

notcopy 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

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

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

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

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)

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

I hope no one perceives this as student posting hehe

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

Ra = 1159 k

Rb = 1021 k

Midspan deflection = 1.201" @ 8.96'

Cantilever deflection = -0.542" @ 24'

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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?

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

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

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 -

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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?

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

How many actually gotten built into the structure?

Zero, zip, nada.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

## RE: Integrated Excel workbook for Wood Structure Design

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.

## RE: Integrated Excel workbook for Wood Structure Design

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

## RE: Integrated Excel workbook for Wood Structure Design

Sounds loopy but it works.

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