Newton Excel Bach, not (just) an Excel Blog

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.

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:

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:

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

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:

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

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:

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

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:

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

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

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.