Easiest way to make span tables?

[Euler07]

Hi all,
So this is something I've been trying to figure out for a few years. What is the most time efficient way of writing spans tables for structural members? Has anyone figured out an easy way?

The main difficulty with span tables is that the process of calculation is the opposite of a normal calculation. For a normal excel calculation, you start with the span, add the loading, go through the calculations, then the result is the required member size.

For span tables, the end result you want is actually the allowable span. This means that all the equations are reversed. While this is relatively straight forward for a basic calc like M = wL^/8 -> L = (8M/w)^0.5, this becomes difficult for complex calculations. Especially when all our normal spreadsheets are written span -> member, so using our normal pre-made spreadsheets is impossible.

In most cases, there are 2 types of span tables:
1) Left Column = span, Top Row = load width/load, Result inside = required member.
This is the easiest table to make as far as I have found, and I can utilise my normal spreadsheets. Using an Excel "Data Table", a range of spans and load widths can be input and Excel will get the end result for each one and automatically put it in a table.

2) Left Column = member size, Top Row = load width/load, Result inside = allowable maximum span.
This is the standard preferred way of setting out a span table since it gives the actual maximum spans for each member. However I have not figured out how to do this using conventional calculation spreadsheets. The only way I can think of doing this is through iteration using a scripting language.

My question is, how do people here go about making span tables? Also, has anyone figured out how to make the standard second type of span table in an easy way?

Thanks

 

[Eng16080]

Depending on how complex your calculations are, you may be stuck using an iterative approach. With that said, I came up with a method of sorts which should work for a simply supported, uniformly loaded beam, which is fully braced laterally.

In my case, my code/spreadsheet calculates the unity ratios for bending, shear, and deflection, with ratios less than or equal to 1.00 being acceptable (meeting code) and those above not. The inputs are beam size and material strength, uniform load, and span length. If all three ratios don't exceed 1.00, then the beam is good, etc. I'll assume your speadsheet is setup similarly.

From there, using a unit span length of 1, it's possible to calculate the maximum span due to shear, Lv, the max. span due to bending, Lm, and the max. span due to deflection, Ld, as follows:
[ul]
[li]Lv = 1 / unity ratio for shear[/li]
[li]Lm = sqrt(1 / unity ratio for bending)[/li]
[li]Ld = cbrt(1 / unity ratio for deflection)[/li]
[/ul]

Then, your max span is simply: Lmax = min(Lv, Lm, Ld). Realistically, the shear ratio (and Lv) will probably never control.

FWIW, I used this procedure to compare my calculations to the wood span tables found in the IRC code. Hopefully this is helpful to you!

 

[SE2607]

For the time and effort it would take to make span tables, I recommend creating a spreadsheet. It would be much more versatile than load tables. Even if you are one of the few people who doesn't have MS Excel, there is always Google Sheets. I've attached a sample.

 

[SWComposites]

Simple. Use the first approach to analyze a complete set of loads and span lengths. Then copy the results and re-sort them by member size, then for a given load value extract the maximum span. Probably can be done with a pivot table but would probably take some messing around to sort out.

 

[HTURKAK]

My suggestion would be (e.g. bending design )

Left column ;Element designation and Cross section resistance ; Top Row = Length between lateral restraints ;and Result inside corresponding Buckling Resistance Moment .

You may look to the following doc. to see the concept .



Use it up, wear it out;
Make it do, or do without.

NEW ENGLAND MAXIM

 

[Agent666]

If you already have a design spreadsheet, look at simply using goal seek or solver in excel to determine the max span for a given configuration. If you have multiple configurations look at using a macro to do this analysis and record the results to another sheet.

https://engineervsheep.com

 

[wth]

These days I would probably set up a design calculation for the member with regular, not reversed, equations. And then have ChatGPT assist me in an iteration process to create the table...

 

[IDS]

And then have ChatGPT assist me in an iteration process to create the table...

Really?

Every time I have experimented with ChatGPT on even fairly basic numerical work it has responded with something that was fast, at first sight quite convincing, and on further investigation totally wrong.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

Further to the post from Agent666, those wanting to use Goal Seek without repeating the same operations multiple times may find the following posts from my blog useful:

https://newtonexcelbach.com/2013/05/20/using-goal-seek-on-a-multi-column-range/

https://newtonexcelbach.com/2013/01/30/goal-seek-macro-for-multiple-sheets/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[XR250]

Honestly, how useful are span tables for lumber and I-beams?
In the time it takes you to open you spreadsheet, you could have already sized it on your calculator (I mean if you are using RPN that is lol ) if you can commit to memory the I and S of each commonly used member. Sure makes life in the field easier. I mainly just memorize I as that is what generally controls the member. Once you do enough calcs, you kinda already know the answer anyway.
The deflection equation can be made more streamlined by using 22.5WL^4/EI. Where W is in lb/ft and L is in feet

 

[SE2607]

It's easy enough to put a document (Excel, PDF, etc) on your phone with A, S and I.

 

[wth]

Really?

Yes, really. I've subscribed, so using ChatGPT 4. I'm mostly using it for Python and some VBA scripts, file management and data clean up. But also just for basic AutoCAD commands or workflows instead of google. Also tried Revit API/Dynamo, but its not there yet.

For coding it's almost always close on its it's first try in 4.0 with Python, and it's actually started running the code itself and iterating until it gets the results I've asked for. In 3,5 it's more random and a lot more debugging.
But then I'm someone who understands basic coding, but have never prioritized learning it fully. Just started doing some calculations in VBA a couple of years ago , so for someone skilled in a language I'm sure it's suboptimal relying on AI.

But for this case, I'm pretty sure I could prompt it with a premade beam calculation sheet/code (you can't/shouldn't have it actually designing the member), a table of member sizes and properties, and a range of loads, and then ask it to produce a Python code that checks every member for every load until it hits 100% utilization or more, and then write the previous result to a table. Optionally print the full results of each governing case.

 

[milkshakelake]

Honestly, how useful are span tables for lumber and I-beams?

Very useful, depending on your setup and business. It lets drafters do engineering work. I love design tables. Also, the process of opening up my spreadsheet, inputting a few values, making some sketches, etc. can take 10 minutes or so. That's 10 minutes saved, if you're trying to properly document everything. With span tables, your documentation is right there without any additional effort. It's useful in a company where designs are handed off to seniors for checking. If it's a sole proprietorship and you have the experience to memorize things, it might be a wash.


Anyway, the best way for me to make a design table is to use scripting and iterative processes. It has the benefit that you can save a report of every single calc in your load table. For example, if you need the detailed calcs of a 12'-6" member with typical commercial loading, you can do your calcs in a way that the detailed output is there if you need it one day. But if the time it takes to figure out how to do this is more than running each calc manually, then you might as well run like 40 calculations yourself to make a table; it's still worth it. I've done that before when I couldn't figure out how to script something.

 

[IDS]

(you can't/shouldn't have it actually designing the member),

That's basically what I was saying. Yes, for coding, where you know what you want to do but need some help with the code details, ChatGPT can be useful.

But for the case in question, where you want to generate a table using a simple procedure with multiple different inputs, I'm pretty sure using a spreadsheet is still the quickest and easiest way to do it.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Euler07]

Hi all,
Thanks for the responses.

From there, using a unit span length of 1, it's possible to calculate the maximum span due to shear, Lv, the max. span due to bending, Lm, and the max. span due to deflection, Ld

Thanks, Eng16080. This is very useful and I'll implement this in my current spreadsheet to see how it works out. I've never before considered what you've done.

For the time and effort it would take to make span tables, I recommend creating a spreadsheet. It would be much more versatile than load tables. Even if you are one of the few people who doesn't have MS Excel, there is always Google Sheets. I've attached a sample.

Thanks SE2607 for showing me your spreadsheet. I see it's structured vertically which allows you to put in various loading and span conditions. My current beam spreadsheet is structured horizontally with a list of member sizes down the left column. The problem with mine is that it's not appropriate for calculation submittal, but it makes the sizing of an individual beam of various types very fast. I'll try to structure one of my spreadsheets vertically with different load cases / spans in each column like you have done.

Simple. Use the first approach to analyze a complete set of loads and span lengths. Then copy the results and re-sort them by member size, then for a given load value extract the maximum span. Probably can be done with a pivot table but would probably take some messing around to sort out.

I've tried using pivot tables but I can only get one load width column with the member sizes on the left. It then becomes a weird formatting when I try and get multiple columns. I've since given up on the pivot table :S

Left column ;Element designation and Cross section resistance ; Top Row = Length between lateral restraints ;and Result inside corresponding Buckling Resistance Moment .

Thanks HTURKAK, that's exactly what I'm trying to achieve, but using a simple excel method to automate the table creation.

If you already have a design spreadsheet, look at simply using goal seek or solver in excel to determine the max span for a given configuration. If you have multiple configurations look at using a macro to do this analysis and record the results to another sheet.

Further to the post from Agent666, those wanting to use Goal Seek without repeating the same operations multiple times may find the following posts from my blog useful:

Thanks Agent666, wth & IDS. I've started setting up a goalseek script which seems to be working. I'll copy over the function on your website IDS and read your further material; it looks like it's exactly what I need.

Regarding the other comments, the fastest design engineer I know is an old timer principle engineer who hardly uses Excel. Everything he does is through pre-made design tables. He can design a shed in 1 minute that would take other engineers hours. This is where I see the value of tables, not just for beams but for everything including portal frame, footings etc.

I've been using ChatGPT for VBA/python scripting and it just makes the whole process more enjoyable, in addition to saving countless hours. It's more of a back and forth conversation and it makes the focus of coding more about overall structure rather than memorising specific syntax.

Yes, really. I've subscribed, so using ChatGPT 4. I'm mostly using it for Python and some VBA scripts, file management and data clean up. But also just for basic AutoCAD commands or workflows instead of google. Also tried Revit API/Dynamo, but its not there yet.

Do you think it's worth paying for ChatGPT 4 at the moment? Do you use it for other work related things other than scripting? Thanks.

 

[Sam_G]

Hi, I recommend you look up on on the internet on how to write custom Excel Functions. This way a single cell can calculate very complex formulas by throwing all the variable to a function which then computes it and return just the answer you are looking for. The other good thing is you only need to write the formulas for the function once and can edit it one place only.

 

[wth]

Do you think it's worth paying for ChatGPT 4 at the moment? Do you use it for other work related things other than scripting? Thanks.

Yes, it's 'only' 20 USD a month, compared to any other software we're subscribing to it's not bad.

Biggest advantage is less back and forth, ChatGPT 4 runs the code itself, and rewrites it until it works. Also it can search the web and do data analysis.
I mostly use it for scripting, data analysis and cleanup. But I've also used it as a 'tutor' when learning/repeating theory.
Some colleagues use it (3.5) for polishing text in reports and tenders as well, but personally I don't see the need in my native language (should consider it for eng-tips though..)

 

[milkshakelake]

I use a ton of AI for scripting and programming. It fools me into thinking that it actually understands the code, because sometimes it summarizes what a certain line or block of code is doing in plain English. Something like, "this will take all the values above line XYZ and process them." It's a nice tutor. You can also ask it to rewrite the code itself to tweak things, like changing the name of a variable or using a different function. Yeah, you can do that yourself with find/replace, but so can AI. I also had a ridiculously long code to do a simple thing and it simplified it into a few lines.

The only caveat is that a real programmer told me AI writes messy, unusable code. I'm not smart enough to agree or disagree with that.

 

[Euler07]

Thanks all. I'll continue my path trying to do this using VBA.