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.

 

[AELLC]

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

 

[jeffhed]

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.

 

[AELLC]

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.

 

[jeffhed]

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.

 

[AELLC]

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.

 

[AELLC]

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.

 

[jeffhed]

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.

 

[AELLC]

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.

 

[jeffhed]

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.

 

[AELLC]

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.

 

[AELLC]

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.

 

[AELLC]

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

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

 

[AELLC]

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)

 

[AELLC]

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.

 

[jeffhed]

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.

 

[jeffhed]

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

 

[AELLC]

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

 

[jeffhed]

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.

 

[AELLC]

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.

 

[AELLC]

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.