The attached Excel spreadsheet is an extension of previously posted beam calculators on this blog and XL4Sim. This one has two significant improved differences:
 |
| Beam Designer Dashboard. |
1. Rather than using the VBA module to do the beam calculations they are done directly via worksheet formulas. This enables "instant" updates as changes are made to the input cells. This makes scenario projects very fast. The cell formulas are on the same worksheet as the input cell sheet and, therefore, the sheet can be copied as many times as needed without loss of functionality. Multiple workbooks can also be made.
2. A VBA macro option is available on the worksheet that will "optimize" the beam configuration given a user list of available beam sections defined by area moment area of inertia and corresponding section moduli (top and bottom). The optimizer will find the combination of given beam sections optimized to the user defined maximum stress using the smallest sections. The ideal optimum is where all the beam segments are at the maximum allowable stress which will usually correspond to the lowest weight and many times the lowest cost. The algorithm will consider the sections given by the user and will not become trapped in a local minimum.
It takes ~ 1-2 minutes to complete if the largest number of beam segments (24) and user input sections (21) are used.
Up to 24 beam segments can be used with each having different section properties. For the optimizer the user can input up to 21 different sections to be considered. The only restrictions are: 1) they be input from smallest area moment of inertia to largest and 2) the largest section, if used for the entire beam, will result in the maximum stress <= the target stress. The program will provide caution messages for each condition. In some cases the program will find sections that can be smaller than the ones the user has provided. These will show up as being stressed less than the allowable. The worksheet charts update after each cycle so you can watch as the optimization proceeds.
Available boundary conditions include simple-support with a given displacement value, fixed slope with a given value, and fixed displacement/slope. Translational and rotational springs are also available. Loads include concentrated forces and moments. Moment, shear, stress and deflection charts are given. The calculations are dimension neutral but they must be consistent.
As an aside, the built-in Excel Solver optimizer can be used but it is not very practical for this type of application because it gets too easily trapped in local minimums and can not easily handle discrete changes to the variables. The algorithm used in this spreadsheet is a guided descent approach which takes advantage of the known properties of the domain.
The spreadsheet download has several examples of trial cases (one per worksheet) that can be used as examples. The VBA module and cell formulas in Range A150:DC700 are password protected to protect them from being broken or inadvertently overwritten. The VBA module has been 3rd party code signed (Thawte) to help with the Excel security settings. The VBA module is also date limited for 6 months. See this blog or www.xl4sim.com for future updates.
Here are two examples. The first, is a fix-ended beam with a center load but the section moduli of the bottom fiber is 1/2 the upper fiber. The second is a bit more complex with an end moment, two vertical loads, a vertical spring, a fixed end and mid-span simple support. Note for each the optimizer tries to find a combination where each beam segment is close to the allowable stress which in these cases is 24ksi. They won't quite reach the allowable stress because of the discrete sections.
Another potentially valuable feature is once the "optimum" is found the user can manually change any of the beam parameters and get an "instant" result for additional "what ifs".
 |
| Fix-Ended Beam with Center Load Example. |
 |
| Complex Beam Example. |
No comments:
Post a Comment