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]

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.

 

[AELLC]

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

 

[AELLC]

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.

 

[AELLC]

Here is the hand-written diagram and notes

 

[AELLC]

errata -

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

 

[AELLC]

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.

 

[AELLC]

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.

 

[AELLC]

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

all explained on attached .xlsx file.

 

[AELLC]

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.

 

[AELLC]

Sorry, this one is improved. Discard Book 12.

 

[AELLC]

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.

 

[AELLC]

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.

 

[AELLC]

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

 

[AELLC]

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.

 

[AELLC]

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.

 

[jeffhed]

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.

 

[AELLC]

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.

 

[jeffhed]

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.

 

[AELLC]

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.

 

[jeffhed]

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.