Using Excel Solver Feature to analyze and optimize the Steel Truss Design.

Using Excel Solver Feature to analyze and optimize the Steel Truss Design.

If you are a Structural Designer and an advanced excel user, you might have or would like to use Solver Add in in Excel to optimize various designs.This feature is a paradigm shift from one way traditional design methods. Here, you  make Excel try numerous design options/ possibilities till it meets all codal provisions for safety etc. and is also most economical. But I would like to warn the new professional that there is no substitute to getting deep into the design process and asking right questions from the professional peers and industry stakeholders.

For why you should design with excel please refer to my previous blog at  http://www.ashutoshp.in/2014/01/why-design-using-excel.html



Skip the next link if you are already using the solver add-in. Else please read my earlier blog. Excel Solver Primer for Construction Stakeholders

To quicken the learning process, download the example from the link below.
Drafting,designing and Quantity Survey of Trusses in Excel
  

First create a joint and member data which is automated with simple Excel Formulas.

Simple Charting concepts and good skills are required to draw the truss.Visualization is important for both validation of the entered data and conceptualization of the problem. Explore the chart data and you you would realize that drawing it is in fact simpler that you think.

Load Calculations would require clarity of codal provisions. No special Excel skills are required as simple formulas are used for calculations.

The wind Load sheet calculates the  maximum wind pressure on the purlin and truss. It uses plain and simple formulas.  Sheets 'Purlin' and 'Tubes' are also primarily input data. Since our purpose here is to learn solver we skip the ordinary.

Now comes the solver's role. Static equilibrium of the truss requires that at all joints the net vertical, horizontal loads and moments are zero. We have to accordingly formulate our problem.And we do it using the basic principles of statistics that is taught to all engineers and architects.
The sheets 'DLAnalysis' and 'WLAnalysis' are the sheets where we make solver to play  the trick. As we know that stability requires ∑ X, ∑ Y and ∑  M to be zero, we fill the solver parameters accordingly and let it solve it for us.

 Solver feature has been used for calculating the internal forces of the members. This condition is achieved by formulating the model in the solver window which through iterative process adjusts the internal member forces to achieve the equilibrium.

In the 'Design & QS' sheet, you  try and choose the available sections and check the adequacy of the member being designed for tension and compression.

This is what you optimize.

This may not be an easy example for appreciating the solver feature, but it would make you realize the possibilities Excel Solver feature offers.