ULS Design Functions and EurocodeApplied

Posted on 12/07/2020 by dougaj4

The ULS Design Functions spreadsheet (last updated here) has had another update (2.09) to fix a problem when bars in different layers had different E values.  The new version assigns the correct E and strain at yield to each layer.

The new version can be downloaded from:

ULS Design Functions.zip

Results for circular or cylindrical sections designed to Eurocode2 can be checked using the calculator at a new site, EurocodeApplied, which has detailed examples of analysis and use of various Eurocodes.  Results for a circular section with an axial load of 2000 kN are shown below (click on images for larger view):

The results include detailed numerical output, optionally an interaction diagram (Moment capacity plotted against axial loads), and a detailed description of the analysis procedure, with reference to the relevant code clauses.

The spreadsheet results for the same section and loading to Eurocode 2 are:

There are small differences in the results because:

  • The on-line calculation uses the parabolic-linear stress block, whereas the spreadsheet uses the rectangular stress block.
  • The on-line calculation uses a much greater number of layers to represent the circular section than the spreadsheet.

The Circu function in the RC Design Functions spreadsheet has an option to use the parabolic-linear stress block, and uses an analysis of the actual circular shape, rather than subdividing into trapezoidal layers, and gives near exact agreement with the on-line results.  An example will be provided in a few days.

Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , , , | 8 Comments

Numerical integration with on-sheet calculations

Posted on 08/07/2020 by dougaj4

Following a comment here I have prepared a spreadsheet that works through the Tanh-Sinh Quadrature process with on-sheet calculations.  For that purpose Graeme Dennes has provided a simplified version of the code:

QUAD_RODRIGUEZ_TANH_SINH for finite intervals.
Translated from HP RPN code written by Cesar Rodriguez.
This is the second fastest T-S program I have found, second only to the program by Michalski and Mosig.
This is also the shortest T-S program I have found!
No subroutines. Two nested Do loops.

The spreadsheet calculations and the new VBA code have been added to the zip file with the full set of integration routines, which can be downloaded from:

Tanh-Sinh Quadrature

(Link updated 26th November 2020)

Screenshots from the new spreadsheet are shown below, and the download files include unprotected spreadsheet calculations, and full open source VBA code.

The VBA version of the Quad-Rodriguez_Tanh-Sinh function can be used in the same way as the other functions by Graeme, either evaluating functions entered as text on the spreadsheet, or using the built-in VBA functions:

The spreadsheet calculations on the next sheet show the VBA code in Column A, with the corresponding spreadsheet calculations to the right.  The three grey shaded cells allow user input of any function of a single variable and the integration limits:

The bulk of the work is done in two nested do loops, with the outer loop repeated four times, and the inner loop up to 15 times, with the spreadsheet calculations in Columns G to AO in the two screenshots below.  Click on the images for a larger view, or download the spreadsheet to follow the calculations in detail:

The results from each of the four outer loops are shown below, with increasing precision from each loop.  The final loop returns the exact value of pi to 15 significant figures (the greatest precision available in Excel), and agrees exactly with the VBA function results:

Note that if a different function is entered in cell G4 the spreadsheet calculations will be updated, but the “actual error” figures will not be correct, since they assume an integration with a result equal to pi.  Also there may be small differences between the VBA and on-sheet results, since the VBA adds iterations where necessary to reach the required precision, but the on-sheet calculations have a fixed number of iterations.

Posted in Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , | 10 Comments

Excel functions that return references and new functions

Posted on 30/06/2020 by dougaj4

The MyOnlineTrainingHub blog recently posted an article on:

Excel Functions that Return References

As well as comprehensive advice on the use of OFFSET, INDEX, CHOOSE, IF and INDIRECT, it also covers the new functions XLOOKUP (Excel for Microsoft 365 only), and IFS and SWITCH (new in Excel 2019).  There is also a worksheet for free download, including all the examples given in the article.

Posted in Arrays, Excel | Tagged , , , , , , , , , | 1 Comment

XY Chart problems – 2

Posted on 16/06/2020 by dougaj4

The previous chart post (XY Chart problems – 1) looked at problems plotting charts with very short data ranges.  An associated problem is plotting XY charts with data with gaps (see When is an XY chart not an XY chart …? and Plotting Charts With Gaps), using array data returned from VBA or an array function.  In the latest release of Excel there is now a simpler solution to this problem, illustrated in the screen-shots below.

The graphs on the left show plots of the XY data in Columns A and B.  The graphs on the right plot the same data, as returned by a VBA user defined function (UDF), with various options for the data format of the returned data.

The original data plots a continuous line, which displays the same in both graphs:

If the Y data includes a blank cell the graph on the left displays with a gap in the line, but if the data is returned from VBA as a variant array (or a range) the blank cell is converted to a value of 0:

If the empty cell is converted to a #N/A value (using VBA CVErr(xlErrNA)), the cell now plots as a gap:

If the VBA array is declared as a Double array the #N/A value will not be accepted, and blank cells are returned as 0:

If any cell in the Y range contains a space, or any other text, that cell will plot as a zero value, both directly and when returned as a variant array from VBA:

Converting the text to an error value in VBA, the graph displays with a gap:

A blank cell in the X column displays the same as in the Y column; as a gap when ploted directly on the spreadsheet and as 0 when returned as a Variant array from VBA:

Converting the blank to a #N/A value in VBA now displays as a gap:

A space (or other text) in the X data results in the XY chart displaying as a Line chart:

Converting the text to #N/A in VBA returns the graph to XY format, with a gap:

If the data is converted to a double array in VBA, with the text cell converted to a zero, the chart will display in correct XY format, but with a zero X value, rather than a gap:

The correct display can be returned to both graphs by entering the #N/A value on the spreadsheet, and returning the data from VBA as a variant array:

As previously, using a Double array in VBA will not allow the #N/A value to be returned from VBA.

A spreadsheet including the chart and the VBA function (as well as the charts from the previous XY graph post) can be downloaded from: XY chart bug.zip.

Posted in Arrays, Charts, Charts, Excel, UDFs, VBA | Tagged , , , , , , | Leave a comment

The Drover’s Boy

Posted on 12/06/2020 by dougaj4

Ted Egan

Posted in Bach | Tagged , | Leave a comment