Automating Goal Seek with RC Design Functions

As promised in the previous post, I will now look in more detail at the process for automating the Excel Goal Seek function with the RC Design Functions spreadsheet, to calculate reinforcement area or section depth for a range of bending moments and associated axial loads.

The example shown in the screen shot below finds the minimum reinforcement required for an arch structure with a range of positive and negative bending moments and varying axial load.

The input ranges are shaded grey for the cross section data, and light blue for the Goal Seek data.  The section data input is in the usual format for the EStress and UMom functions.  Note that in adjusting the reinforcement area the functions use the bar diameter entered in the section data, and in effect change the number of bars.

The data required by the Goal Seek routine is:

  • The addresses of the “target range” (taddr) and the “by changing range” (aaddr)
  • The “Goal Value” (GVal) and lower and upper bounds on the absolute value of the Goal (MinAbs and MaxAbs)

Note that the required range names are already set up on the GoalSeek examples sheet, but the Goal Seek routine may be used on any other sheet by creating the same four names, with Worksheet scope.  Also note that the addresses in cells F20 and F21 are generated with a formula, using the data in B20:D21, but if you prefer you can simply type the required range addresses in F20:F21.

GoalSeek01

The axial force and moment on each section are entered in Columns B and C, followed by the section depth, and top and bottom steel areas in Columns D to F.

Note that the data that will be adjusted by Goal Seek must be in a single column (Column G in this case).  Because the section bending moments may be either positive or negative, Columns E and F contain simple formulas returning either the area in Column G if the steel will be in tension, or the minimum steel area entered in E25 if it is in compression:

  • Column E: =IF($C29<0,$G29,$E$25)
  • Column F: =IF($C29>0,$G29,$E$25)

The steel stress is calculated for the top and bottom steel in Columns H and I, with the maximum absolute value in Column J, and the ratio to the Target Stress in Column K.  It is Column K that will be adjusted to the target value (1.0) by Goal Seek:

The steel stresses are calculated by the EStress function, with input as shown below:

goalseek02

Note that the full columns of axial loads and moments are entered as the Axin and Momin arguments.  The output is controlled by the Out1 and Out2 arguments.  Out1 = 1, for stress, and Out2 = 2 for top steel (column I) or 3 for bottom steel (Column J).

The section depth and steel areas are defined in Columns D to F, as shown below:

goalseek03

Input for the bottom steel stress is the same as the top steel, except that Out2 =3:

goalseek04

Note that the functions will return a column of results, covering the length of the input data ranges, and must be entered as an array function, to return all the needed values:

goalseek05

Initially the tension steel area should be entered as the minimum allowable area:

goalseek06

Then click the “Run Goal Seek” button, and the reinforcement areas will be adjusted, so that the stress ratio is equal to or greater than 1:

goalseek07

The resulting graph of reinforcement stress shows a maximum tension of 250 MPa:

goalseek08

A similar process can be followed for ultimate moments, using the UMom function, and resetting the addresses of the Target cells and By Changing Cells:

goalseek09

The resulting moment capacities are equal to or greater than the applied bending moments:

goalseek10

The section depth may also be adjusted, to match either a target stress, or moment capacity, as shown below:

goalseek11

In this case the reinforcement area is kept constant, so the reinforcement details entered in the main Section Details range are used, and the range for the adjustable section details may be limited to a single column, containing the adjustable section depth.

To carry out a similar analysis on a different sheet:

  • Set up a similar table to those shown above, with a single column for the Target values, and a single column for the By Changing values.
  • Create named ranges with “Worksheet” scope with the names: “taddr” (target values), “aaddr” (by changing values), “GVal” (goal value) and “MinAbs” and “MaxAbs” (minimum and maximum values for the targets).
  • Create a button for the GSeekA macro, or press Alt-F8 and select GSeekA.
Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , | 2 Comments

RC Design Functions update

Following a comment here I have updated my RC Design Functions spreadsheet, which is available for free download (including full open-source code) from:

RC Design Functions7.zip

The main new features in the latest version are:

  • The existing optional “depth” argument has been extended so that an array of reinforcement areas can be entered, which will override the values in the main section data input.
  • My Goal Seek macro has been added, so that the reinforcement areas can be adjusted so that the tensile reinforcement stress, or section bending capacity, match a given value, and the process can be automated over a column of data of any length.
  • Examples of using the Goal Seek macro with the EStress and UMom functions have been added.

The screen shots below show samples from the new examples.  Further details of setting up the data to work with the Goal Seek macro will be given in the next post.

Data input for the Goal Seek macro, in conjunction with the EStress function:RCFunc7-1

Similar input with the UMom function:RCFunc7-2

Reinforcement stresses, and section bending capacities, after adjusting the reinforcement area with Goal Seek:RCFunc7-3

Posted in Arrays, Beam Bending, Concrete, Excel, Frame Analysis, Newton, UDFs, VBA | Tagged , , , , , , | 1 Comment

What-when-how …

… is a web site offering free tutorials on a wide range of subjects, including finite element analysis and VBA.

The basics of finite element analysis are covered by links from:

Computational Modelling (Finite Element Method)

with more advanced material covered at:

FEM for Plates and Shells (Finite Element Method) Part 1

FEM for 3D Solids (Finite Element Method) Part 1

Special Purpose Elements (Finite Element Method) Part 1

VBA starts at:

Getting Started with Excel Formulas and Functions

And for a full list of subjects see:

Topics

Posted in Finite Element Analysis, Newton, UDFs, VBA | Tagged , | Leave a comment

If it keeps on straining …

… concrete’s goin’ to break:

To find out what this has to do with predicting concrete deflections, book into the Concrete Institute seminar on “Finite Element Analysis of Concrete Structures”:

Finite Element Analysis of Concrete Structures – Software and Practice

When:
Wednesday, 18 May 2016
4:45 PM – 8:00 PM

Where:
Ryde-Eastwood Leagues Club
117 Ryedale Road , West Ryde NSW 2114

Posted in Bach, Beam Bending, Concrete, Newton | Tagged , , , | Leave a comment

Beetles …

… from the collections of the Oxford University Museum of Natural History, portraits by Levon Biss:

Beetles1
Beetles2 beetles3 Beetles4 Beetles5

More at:

Microsculpture

Posted in Bach, Newton | Tagged , | Leave a comment

xlwSciPy 1.7

Following recent posts on xlwings 0.7.1, dictionaries, and optional arguments and xlwings – dataframes and statistics, I have added the associated functions and examples to the xlwSciPy spreadsheet, and also updated it to xlwings 0.71.

The new spreadsheet can be downloaded from:

xlScipy-xlw.zip

including full open source code.

The spreadsheet requires Python, including xlwings, Numpy, Scipy and Pandas (all of which are free, and included in the Anaconda package).

Posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, Python Pandas, UDFs, VBA | Tagged , , , , , , , , | 3 Comments

Jack Bruce on double bass

Read all about it: Things We Like

Posted in Bach | Tagged , , | Leave a comment