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.
Newton Excel Bach, not (just) an Excel Blog 
Great post Doug! Here’s my small offer on using Goal Seek with VBA : http://bit.ly/1P9JlXm
LikeLike
Thanks Winston.
I recommend a read of the link to anyone interested in using Goal Seek with VBA, and using Microsoft documentation in general.
LikeLike
it is a very useful spreadsheet. it is surprising what can be done with excel and vba..
i am developping my own spreadsheet based on the spanish code thanks to your ideas….i never tought of using data ranges as input for a function…
thanks….
LikeLike
I’d like to use horizontal range for input data (section properties, etc) in UmomPF function but I get argument error. Vertical range works well. I try to use Excel’s “TRANSPOSE” function to reverse horizontal range but it doesn’t work. Can you help?
LikeLike
I don’t know why using Transpose inside the function doesn’t work, but it seems that it passes a single value of 40, rather than the transposed array. I don’t know where the 40 came from as the first value in my data was 1000. Very strange.
The simplest workaround would be to use the transpose function on the spreadsheet, and use the transposed range in the function.
Alternatively, if you are happy working with VBA, you could add the following line to he UMomPF code, just after incells = UMomin.Value2:
If UBound(incells) < UBound(incells, 2) Then incells = WorksheetFunction.Transpose(incells)
That seems to work OK.
It would be fairly simple to add something similar to all the functions, so I'll look at doing that in the next few days.
LikeLike
Now it works with additional VBA if statement. Thanks for quick ansewer and solution!
LikeLike
Is there any simple way in Excel to join two ranges into one when using UDF? I’m looking something similar to VBA UNION method. I would like to join two ranges for “input range” argument in Estress fucntion. The first range will be RC sections properties same as for UmomPF function and second – rest of needed arguments of “input range”.
LikeLike
The simplest way would be to set up your input ranges the way you want, then combine them into a single range on the spreadsheet.
If you want to do it in VBA you could write a simple front end function to combine the data into a single array, then call Estress from VBA. I have never actually used the VBA Union method, so I’d create the combined array by just looping through each of the input ranges, but I’ll have a look at the Union method, it sounds like it should be useful.
LikeLike
Hello. I got some questions.
1. To “RC spreadsheet”:
I’ve got small area of tensioned steel and big section – foundation slab. Considering pure tension. When force is a little bigger than cracking force then I’ve got already plasticity state in steel in section where crack occur. I know that’s not good because I’ve got plastic strain. In your spreadsheet – Estressout I get values of stress in steel bigger than yielding stress. Does it should be limited to steel yield stress (flat chart in plasticity state) or do you using not horizontal plasticity chart?
2. To “RC spreadsheet”: Which is the Beta factor in Eurocode?
3. How to properly calculate cracks width under shrinkage and temperature change in pure tension? I mean how to calculate tension forces change after cracking and therefore how to properly calculate stress in steel? I can estimate tension forces in slab on ground in elastic state with some friction between slab and soil. But due to the cracking and then deformation force should be decreased so stress in steel will be lower than force calculate in elastic state. Isn’t it?
LikeLike
1. The Estress function (and associated functions) assumes linear elastic properties for both steel and concrete in compression (see notes on the Contents sheet), and ignores concrete in tension. It is appropriate for working load or SLS stress calculations, but not if the stress in the steel or concrete is outside the elastic range. Note that concrete design codes have requirements for minimum tensile steel area to ensure that the stress in the steel immediately after concrete cracking is less than the yield strain.
You should use the UMom function for ULS strength design, or MomCurveTS for moment-curvature calculations including non-linear material behaviour.
2. The Eurocode Beta factor is a factor depending on the duration of the load, used in deflection calculations. Beta = 1.0 for a single short-term loading, or 0.5 for sustained loads or many cycles of repeated loading. See equation 7.19 in Clause 7.4.3.
3. In an unrestrained beam or column the reinforcement stress at cracks is calculated ignoring the tensile stresses in the concrete. The friction between the base of a slab and the ground would have some effect, but as far as I know this is ignored if it reduces the steel tension. Friction restraint should be taking into account when calculating concrete cracking.
LikeLike