Integrated Excel workbook for Wood Structure Design 3

[AELLC]

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.

 

[jeffhed]

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?

 

[AELLC]

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/?DocumentKey=6a127089-cb99-4352-8395-4148db2a2b39

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.

 

[AELLC]

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.

 

[AELLC]

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.

 

[AELLC]

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.

 

[AELLC]

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

 

[AELLC]

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

 

[AELLC]

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.

 

[AELLC]

Here is the actual Excel file for the above

 

[jeffhed]

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?

 

[AELLC]

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.

 

[AELLC]

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

 

[AELLC]

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

 

[AELLC]

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.

 

[jeffhed]

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.

 

[jeffhed]

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.

 

[jeffhed]

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.

 

[jeffhed]

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.

 

[AELLC]

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.