The Roof of the Taq-i-Kisra

A higher resolution image, from a slightly different viewpoint

The roof of the Throne Room of the Taq-i-Kisra in Iran is the best surviving example of an ancient large span structure built to a catenary profile, the shape that will minimise bending moments in a structure of uniform thickness, standing under its own weight.

The red line added to the photograph is a catenary, and the blue line a parabola with the same span at first floor level. The plot suggests that the roof shape from first floor level does indeed approximate a catenary, although the low resolution photograph and irregular outline of the end face of the structure make it difficult to be certain.

Lane Cove Tunnel, Eastern Portal; during construction

13th July 2006.
Click on thumbnail, then click on image to see full size.

Drawing in Excel - 1

This is a the first of a series of posts describing how to produce scale drawings in Excel, based on a list of coordinates. This post will cover the use of XY graphs, or scatter charts as Microsoft likes to call them. Later posts will cover the use of the various line and shape objects, using VBA to automate the process.

Using XY graphs for line drawings has a number of disadvantages: the drawing is likely to be distorted because of the automatic scaling to fill the chart area; there is no provision for adding shading; adding text is difficult; and there is no provision for generation of shapes such as circles, ellipses and arcs, other than plotting lines as a series of straights. There are however two big advantages: it’s quick and easy, and the graph automatically updates to reflect the underlying data.

For these reasons the use of an XY graph can be a good option for many purposes, and this post provides two alternatives for automatic scaling. A cross-section drawing of an Australian “Super-T” bridge girder is used for illustration purposes. The two figures below show the results of plotting the cross section using auto scaling. The edge coordinates have been plotted using one XY range (with point markers hidden), and the reinforcement and prestressing strands have been plotted from coordinates in a second range, hiding the connector lines, and choosing the point marker symbol closest to a circle. It can be seen that both images are distorted to fill the available space.

The simplest way to get approximately equal scaling in the X and Y directions is to ensure that the plot range is square, then plot an additional line so that the smaller range is stretched to match the larger one. This line is allocated its own data range, and has both the line and data markers hidden.

The coordinates of the ends of the scale line are calculated as follows:

XLength = MAX(xrange) - MIN(xrange)
YLength = MAX(yrange) - MIN(yrange)

AddX = MAX((YLength - XLength)/2, 0)
AddY = MAX((XLength - YLength)/2, 0)

Scale Line Coordinates:
Start: MIN(xrange) - AddX, MIN(yrange) - AddY
End: MAX(xrange) + AddX, MAX(yrange) + AddY

The results of adding the scale line are shown in the two figures below. Advantages of this method are that scaling is carried out automatically, and that no VBA is required. The main disadvantage is that if the plot area shape is changed from square the resulting image will be distorted.

The second method is to use VBA to adjust the range of the graph scales, so that the drawing will have the same scale in both directions regardless of the range of X and Y values, and the shape of the plot area. I have taken the macro to perform this task from Jon Peltier’s site:
http://peltiertech.com/Excel/Charts/SquareGrid.html

The code below has been slightly modified to plot the graph in the centre of the plot area, rather than to one side, or towards the bottom:


Sub MakePlotGridSquareOfActiveChart()
MakePlotGridSquare2 ActiveChart
End Sub

Sub MakePlotGridSquare2(myChart As Chart, Optional bEquiTic As Boolean = False)
‘ Code from http://peltiertech.com/Excel/Charts/SquareGrid.html
‘ Modified DAJ 31 May 08
Dim plotInHt As Integer, plotInWd As Integer
Dim Ymax As Double, Ymin As Double, Ydel As Double, YMid As Double, YHWidth As Double
Dim Xmax As Double, Xmin As Double, Xdel As Double, XMid As Double, XHWidth As Double
Dim Ypix As Double, Xpix As Double

With myChart
‘ get plot size
With .PlotArea
plotInHt = .InsideHeight
plotInWd = .InsideWidth
End With

‘ Set axes to auto
With .Axes(xlValue)
.MaximumScaleIsAuto = True
.MinimumScaleIsAuto = True
‘ .MajorUnitIsAuto = True
End With
With .Axes(xlCategory)
.MaximumScaleIsAuto = True
.MinimumScaleIsAuto = True
‘ .MajorUnitIsAuto = True
End With

Do
‘ Get axis scale parameters and lock scales
With .Axes(xlValue)
Ymax = .MaximumScale
Ymin = .MinimumScale
Ydel = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
With .Axes(xlCategory)
Xmax = .MaximumScale
Xmin = .MinimumScale
Xdel = .MajorUnit
.MaximumScaleIsAuto = False
.MinimumScaleIsAuto = False
.MajorUnitIsAuto = False
End With
If bEquiTic Then
‘ Set tick spacings to same value
Xdel = WorksheetFunction.Max(Xdel, Ydel)
Ydel = Xdel
.Axes(xlCategory).MajorUnit = Xdel
.Axes(xlValue).MajorUnit = Ydel
End If

‘ Pixels per unit
Ypix = plotInHt * Ydel / (Ymax - Ymin)
Xpix = plotInWd * Xdel / (Xmax - Xmin)

‘ Keep plot size as is, adjust scale width
If Xpix > Ypix Then
XMid = (Xmax + Xmin) / 2
XHWidth = (plotInWd * Xdel / Ypix) / 2
.Axes(xlCategory).MaximumScale = XMid + XHWidth
.Axes(xlCategory).MinimumScale = XMid - XHWidth
Else
YMid = (Ymax + Ymin) / 2
YHWidth = (plotInHt * Ydel / Xpix) / 2
.Axes(xlValue).MaximumScale = YMid + YHWidth
.Axes(xlValue).MinimumScale = YMid - YHWidth
End If

‘ Repeat if “something” else changed to distort chart axes
‘ Don’t repeat if we’re within 1%
Loop While Abs(Log(Xpix / Ypix)) > 0.01

End With

End Sub


The results of applying this code are shown below:

The advantage of this method is that it will work for any shape plot area, and will still work if the plot area is changed. The main disadvantage is that the macro needs to be re-run every time the graph data changes, but this could be accomplished by triggering the scale routine with a worksheetchange event.

This code was found via the Eng-tips forum (http://www.eng-tips.com/viewthread.cfm?qid=141275), where there are also some modified versions which switch on the axes display, then return them to their original state. I found that the original works works (at least in Excel 2007) whether the axes are displayed or not, so there did not seem to be any advantage in this refinement.

Reinforced Concrete Section Analysis - 3

Previous post -1

Previous post -2

The theory presented in the previous 2 posts in this series has been incorporated into an Excel UDF, allowing concrete and reinforcement stresses and strains to be evaluated quickly and easily for reinforced and prestressed members of complex cross-section, subject to combined bending and axial load.

The Excel file also includes UDFs for solution of polynomial equations up to quartic, and routines for plotting the cross section shape.

Beam Design Functions Download

 

The Dome of the Pantheon

I have recently made my first Wikipedia edit.

The article on the Pantheon in Rome made the following claim:

“The exact composition of the Roman concrete used in the dome remains a mystery. An unreinforced dome in these proportions made of modern concrete would hardly stand the load of its own weight, since concrete has very low tensile strength, yet the Pantheon has stood for centuries. It is known from Roman sources that their concrete is made up of a pasty Calcium hydroxide|hydrate of lime, with pozzolanic ash (Latin ”pulvis puteolanum”) and lightweight pumice from a nearby volcano, and fist-sized pieces of rock. In this, it is very similar to modern concrete. The high tensile strength appears to come from the way the concrete was applied in very small amounts and then was tamped down after every application to remove excess water and trapped air bubbles. This appears to have increased its strength enormously.”

Some quick researh on the Internet found no evidence for the Roman concrete having particularly high strength, and this article: The Pantheon by David Moore quoted research showing that the maximum flexural tensile stresses were very low.

My edit was as follows:

It is known from Roman sources that their concrete is made up of a pasty hydrate of lime, with pozzolanic ash (Latin pulvis puteolanum) and lightweight pumice from a nearby volcano, and fist-sized pieces of rock. In this, it is very similar to modern concrete.[3] No tensile test results are available on the concrete used in the Pantheon; however Cowan discussed tests on ancient concrete from Roman ruins in Libya which gave a compressive strength of 2.8 ksi (20 MPa). An empirical relationship gives a tensile strength of 213 psi (1.5 MPa) for this specimen.[4] Finite element analysis of the structure by Mark and Hutchison[5] found a maximum tensile stress of only 18.5 psi (0.13 MPa) at the point where the dome joins the raised outer wall.[6] The stresses in the dome were found to be substantially reduced by the use of successively less dense concrete in higher layers of the dome. Mark and Hutchison estimated that if normal weight concrete had been used throughout the stresses in the arch would have been some 80% higher.

4. H. W. Cowan, The Master Builders. John Wiley and Son, New York, 1977, p. 56
5. R. Mark and P. Hutchinson, “On the Structure of the Pantheon”, Art Bulletin. March 1986
6. Moore, David, “The Pantheon”, http://www.romanconcrete.com/docs/chapt01/chapt01.htm, 1999

Worksheetfunction vs UDF - 2

Previous Post

In a recent post at microsoft.public.excel.programming Charles Williams (Decision Models) found that a VBA User Defined Funcion (UDF) searching through a defined range for a specified number was very much slower than simply using .worksheetfunction.match as below:

Function VBAMatch2(arg As Double, XRange As Variant) As Long
    VBAMatch2 = Application.WorksheetFunction.Match(arg, XRange, 1)
End Function

I found this strange, since in the past I had found the exact opposite, and repeating the comparison I again found that searching through the data within VBA was some 400 times faster than calling worksheetfunction.match.

It turns out that there is a logical explanation for these different results, which is that I was calling the functions from a VBA subroutine, whereas Charles was using them as UDFs on the spreadsheet.  The result was that the transfer of data between the spreadsheet and VBA (the bit that takes all the time) occurred once for my VBA search routine, but 10,000 times when I used .worksheetfunction.match, calling the function from the VBA sub.  On the other hand when the functions were called from the spreadsheet the opposite situation occurred.  To quote Charles:

“

Your timing routine has as its first executable statement:
datarange=Range(”a1:A10000″)
This converts the range to a variant array of values before doing any
timing, and then passes datarange to the UDFs as a variant array rather than
a range.

So for your timing run of VBAMatch there is no data transfer between Excel
and VBA or VBA and Excel at all, but for VBAMATCH2 the whole array gets
passed from VBA to Excel 10000 times.
Since the vast majority of the execution time is taken by the data transfer
that explains the differences.

Conclusion:
If you want to develop a MATCH routine to process a sorted VBA array then a
VBA binary search routine (or your equivalent) will be fast because the data
is already in VBA, but if you want to develop a UDF MATCH routine to use as
a worksheet UDF function its better to use Worksheetfunction.MATCH because
then the data never has to be passed from Excel to VBA. “

Importing text files with VBA

Importing data from other programs into an Excel spreadsheet, in the form of text files, is a frequent requirement in engineering and scientific applications.  Often the data will have been formatted to suit printed output and will require processing before importing into the spreadheet.  In addition to rather cumbersome procedures, the built-in Excel facilities for importing text files have several drawbacks; for instance where lines start with a ‘, “, or ^ character, these are treated as text alignment characters and are truncated.  Also if numeric text is split into columns results can be unpredictable .

The link below provides routines to select files for import, and import the text to a specified range, optionaly inserting an initial ‘ to avoid truncation of any text alignment characters in the first column of the text.

Future posts will cover splitting the text into columns, and searching for rows containing data, discarding headers and footers etc.
Importing text file with VBA - 2

To use the routines “GetFileName” and “ReadTextSub” in a new file it is necessary to create the following named ranges:

TfileName
destrange
Inserta

and a range with the name specified in destrange.
The function ReadText is called by the subroutine ReadTextSub, but may also be used as a user defined function. In this case it should either be entered as an array function (press ctrl-shift-enter), or inside an INDEX() function. Examples are given in the file below.

Text-in.zip Right click to download

Screen shot:

Elegant Proofs - 1

Roger Penrose in his book “The Road to Reality” gives a remarkably simple proof of Pythagoras’ Theorem:

Drawing a perpendicular to the hypotenuse from the right angle (line CD) will divide any right angled triangle into two similar triangles, both of which are similar to the original triangle. Since the area of similar shapes are proportional to the square of the ratio of the length of their sides, and the sum of the areas of the two smaller triangles is clearly equal to the area of the enclosing triangle, the area of the square on the long hypotenuse is equal to the sum of the squares on the other two sides (i.e. the hypotenuse of the the two smaller triangles).

The beauty of this proof is not only its simplicity, but also that it makes clear why the Pythagoras formula will only work with a right angled triangle; since only a right angled triangle can be divided into two triangles similar to the original.

This result may also be simply shown algebraically:
From similarity:
b/d = c/b
b^2 = cd
and
a/(c-d) =c/a
a^2 = c^2-cd
c^2 = a^2 + cd
hence substituting b^2 for cd:
c^2 = a^2 + b^2

A different approach to this method is given at: Terence Tao

And for those looking for some variety 87 different proofs can be found at: Cut the knot

Playing with numbers

A reader of Melbourne’s Herald-Sun newspaper has posted some data from the Australian Bureau of Meteorology, purporting to show a total lack of climate change, in spite of steadily increasing CO2 concentrations:

Where’s the heat

 

Funily enough, when you take a slightly longer view frrom the very same site, and smooth out the monthly variations, you get a totally different picture:

 

The black line in the lower graphs is the 11 year moving average.

 

Reinforced Concrete Section Analysis - 2

Previous Post

Pseudo-code for elastic analysis of a layered reinforced concrete section under eccentric axial load and pre-stress load:

 Read data
‘For each reinforcement layer: Find area, first moment of area about top ,and depth of centroid.
Find total reinforcement section properties over all layers
For each reinforcement layer: adjust section properties for compression from the top surface to the layer.
‘For each concrete layer:

Find area, first and second moments of area about base of layer ,and height of centroid above base.
Find the number of reinforcement layers in the compression zone.
Find composite transformed properties about the base of each layer.

Find the centroid depth for the complete composite section in compression, and the reinforcement in tension.
Find the total prestress force and moment about the concrete centroid.
Find total axial force and bending moment, and nett axial force eccentricity from the concrete centroid and top face.
Check compression face.
If the compression face is bottom face, reverse layers and recalculate section properties.
Find the concrete layer containing the Neutral Axis.
If the Neutral Axis is above the top face (section entirely in tension) or below the bottom face (section entirely in compression) then:

Find top and bottom face stresses and position of NA using stress = P/A + M/Z

Else

Find parameters for Neutral Axis equation
Solve Neutral Axis equation
Adjust for reinforcement layers in the bottom concrete layer, below the Neutral Axis.
Find composite transformed section properties about the Neutral Axis
Find top and bottom face stresses.

Find stresses and strains at each reinforcement layer and top and bottom face.
Find concrete, reinforcement and total axial forces.
Find concrete, reinforcement and total moments.
Check equilibrium.
Finish