Using the Frame Analysis Spreadsheets

Following some recent questions in the comments, here are some answers:

How can nodal moments be created?

The current versions only allow for forces to be applied to beams, but any point load or moment applied at the start or end of a beam will in effect be applied at the node, so set “Position” to zero for any beam starting at the node, or to the beam length for a beam ending at the node.  In Frame 4 (the 2D version) all moments are applied about the Z axis.  In 3DFrame moments are applied about the global axis specified in “Direction XYZ” (Column C).

How can hinge or spherical joints, or bushing joints be created?

Both Frame4 and 3DFrame allow either end of a beam to be given a rotational spring stiffness.  If the stiffness is set to a very low value the beam ends will in effect be free to rotate.  In Frame4 rotations are about the Z axis.  In 3DFrame the rotations are relative to the beam principal axes, which are described here:  3D Frames, axes and stiffness matrices.

3DFrame also allows beams ends to be allocated a translational spring release along any of the three principal axes.

The example frame in each of the download files illustrates the use of the beam end springs.

How can multiple point restraints be applied?

Nodes can in effect be restrained to move together by connecting with very stiff members, with any required end hinge conditions, to provide the required restraint.


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

Installing Frame4 and 3DFrame

Following a recent comment I have added a VBA only version to the download files of the frame analysis spreadsheets:

The VBA versions should work on any version of Excel from 2007, with macros enabled, without any further installation.

The standard versions link to compiled versions of the solver code, that provide much better performance with large frames.  The download file includes the required dll files, and the installation process is summarised here:

Installing C# dll files, reminder

Any problems, please leave a message here.


Posted in AlgLib, Excel, Frame Analysis, Link to dll, Newton, VBA | Tagged , , , , , , | Leave a comment

Tam Lin

Tam Lin is a Scottish folk tale and song dating from 1549 or earlier. It is set at Carterhaugh (not Carter Hall), which is a real place.


The song was widely re-interpreted in the British folk revival, most notably by Fairport  Convention in their 1969 album Liege and Lief.  The recording below is an excellent live version recorded for the BBC’s Peel Sessions:

The song was also recorded by The Pentangle (for a 1972 film that somehow entirely escaped my attention at the time), but not released until many years later.  The You Tube video has been viewed just 52 times up to today:

Finally a very different version from Steelye Span, which sounds better each time I listen:

Lyrics of the Fairport Convention version:

“I forbid you maidens all that wear gold in your hair
To travel to Carter Hall for young Tam Lin is there

None that go by Carter Hall but they leave him a pledge
Either their mantles of green or else their maidenhead”

Janet tied her kirtle green a bit above her knee
And she’s gone to Carter Hall as fast as go can she

She’d not pulled a double rose, a rose but only two
When up there came young Tam Lin says “Lady, pull no more”

“And why come you to Carter Hall without command from me?”
“I’ll come and go”, young Janet said, “and ask no leave of thee”

Janet tied her kirtle green a bit above her knee
And she’s gone to her father as fast as go can she

Well, up then spoke her father dear and he spoke meek and mild
“Oh, and alas, Janet,” he said, “I think you go with child”

“Well, if that be so,” Janet said, “myself shall bear the blame
There’s not a knight in all your hall shall get the baby’s name

For if my love were an earthly knight as he is an elfin grey
I’d not change my own true love for any knight you have”

Janet tied her kirtle green a bit above her knee
And she’s gone to Carter Hall as fast as go can she

“Oh, tell to me, Tam Lin,” she said, “why came you here to dwell?”
“The Queen of Faeries caught me when from my horse I fell

And at the end of seven years she pays a tithe to hell
I so fair and full of flesh and feared it be myself

But tonight is Hallowe’en and the faery folk ride
Those that would their true love win at Miles Cross they must buy

So first let past the horses black and then let past the brown
Quickly run to the white steed and pull the rider down

For I’ll ride on the white steed, the nearest to the town
For I was an earthly knight, they give me that renown

Oh, they will turn me in your arms to a newt or a snake
But hold me tight and fear not, I am your baby’s father

And they will turn me in your arms into a lion bold
But hold me tight and fear not and you will love your child

And they will turn me in your arms into a naked knight
But cloak me in your mantle and keep me out of sight”

In the middle of the night she heard the bridle ring
She heeded what he did say and young Tam Lin did win

Then up spoke the Faery Queen, an angry queen was she
Woe betide her ?ill-fought? face, an ill death may she die

“Oh, had I known, Tam Lin,” she said, “what this knight I did see
I have looked him in the eyes and turned him to a tree”

Read more: Fairport Convention – Tam Lin Lyrics | MetroLyrics

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

Stress increments in prestressed concrete beams

The two main Australian Standards covering prestressed concrete structures (AS 3600, Concrete Structures, and AS 5100 Part 5, Concrete Bridges) put a limit on the allowable increase in stress in reinforcement and prestressing strands and tendons, when the load increases from the moment that results in zero stress at the tensile concrete face to the maximum moment under Serviceability Limit State loads.

The current versions of my beam design spreadsheets do not directly provide the value of this stress increment, but it is easy to calculate, using the Excel Goal Seek function.

The examples below can be downloaded from:

Composite Design Functions-Stress

The screen shots below show an example using the Beam Design Functions spreadsheet, with a standard pre-tensioned “Super-T” bridge beam:

To find the reinforcement stresses when the stress at the bottom concrete face is zero, open the Goal Seek dialog (under the Data tab) and enter:

  • Set cell = C10 (Depth of Neutral Axis)
  • To value = 1800 (Overall depth of the section)
  • By changing cell = A6 (Applied Moment)

Click OK, then OK again when Goal Seek has found the moment (in cell A6) that results in a Neutral Axis depth of 1800 mm (in cell C10):

The resulting reinforcement stresses are found on the “Elastic 1 Out” sheet, in Column C (for the top and bottom layers) and Column L (for all layers).  This data can be displayed on the “Elastic1 Input” sheet with a simple formula: =’Elastic1 Out’!L13.  Only the value for the bottom layer is required, but the screen shot above shows the results for all steel layers in Column O.  When the Goal Seek process is complete the steel stress(es) in Column O should be copied to Column P, using Copy and Paste-Special-As Values.  In Column Q add a formula for the difference between Columns O and P:

The stress change for any applied bending moment can then be found simply by entering the required moment in Cell A6.  Alternatively, Goal Seek can be used to find the bending moment that will result in the maximum allowable stress increase:

  • Set cell = Q25 (Stress Increase for the bottom layer)
  • To value = -200 (or the specified stress in MPa, tension negative)
  • By changing cell = A6 (Applied Moment)

Super-T beams are of course usually used in composite construction, with a reinforced concrete top slab.  The process described above can also be carried out on a composite section using the Composite Design Functions spreadsheet, with the following changes:

  • The composite output does not currently have a full list of stresses in each layer, so use the stress in the bottom layer (and top if desired) from column C, Stage 2 results.  We are interested in the stress in the bottom layer of the precast, so the required value is in cell =’ElasticComp Out’!C33 ( Stage 1 Bottom Steel).
  • To find the moment for zero stress at the bottom face, the value in cell D13 (Combined Depth NA, Stage 2) must be set to the depth of the composite section (2000 mm in the example) with Goal Seek, by adjusting cell B7:

The reinforcement stress at this bending moment is copied and pasted as value to cell Q27:

The stress increase, in cell R27, is then adjusted with Goal Seek to -200 (or the required value), by again adjusting the Stage 2 bending moment in cell B7.

The total maximum SLS applied bending moment is then given in cell B8, being the sum of the moment applied to the precast only, plus the maximum additional load on the composite section:

The output sheet gives additional details of stresses, strains, forces and moments, as well as a strain diagram for loads on the precast and composite sections:

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

Build Bridges, Not Walls

I don’t often do politics here, but with an opening line like “build bridges, not walls” how can I deny Jeremy Corbyn a spot, especially when he is talking at The Glastonbury Festival:

Posted in Bach | Tagged , | Leave a comment

Plotting Freeze-Thaw Data …

… or other irregular cyclic data.

Another Eng-Tips question asked how to approximate ice area over a freeze-thaw cycle using a function based on a sine or cosine curve.  The screen-shot below shows three alternatives:

Using built in Excel functions requires a separate function for the freeze and thaw part of the cycle:


I have incorporated these in a short user defined function (UDF), which returns a column array of the full data range. (See Using Array Functions and UDFs if you are not familiar with array functions).

Function ScaleSin(DatRange As Variant, Outx As Variant)
Dim Inc0x As Double, Inc0y As Double, Inc1x As Double, Inc1y As Double
Dim Dec0x As Double, Dec0y As Double, Dec1x As Double, Dec1y As Double
Dim NumX As Long, i As Long, ResA() As Double, OutXA() As Double, DX As Double, DY As Double
Dim Pi As Double
Pi = Atn(1) * 4

DatRange = DatRange.Value2
Inc0x = DatRange(1, 1)
Inc0y = DatRange(1, 2)
Inc1x = DatRange(2, 1)
Inc1y = DatRange(2, 2)
Dec0x = DatRange(3, 1)
Dec0y = DatRange(3, 2)
Dec1x = DatRange(4, 1)
Dec1y = DatRange(4, 2)

Outx = Outx.Value2

NumX = UBound(Outx)
ReDim ResA(1 To NumX, 1 To 1)
ReDim OutXA(1 To NumX, 1 To 1)

i = 1
Do While i <= NumX
    Do While Outx(i, 1) < Inc0x
        ResA(i, 1) = Inc0y
        i = i + 1
    DX = Inc1x - Inc0x
    DY = Inc1y - Inc0y
    Do While Outx(i, 1) < Inc1x
        OutXA(i, 1) = (Outx(i, 1) - Inc0x) / DX * Pi - Pi / 2
        ResA(i, 1) = Inc0y + DY * (Sin(OutXA(i, 1)) + 1) / 2
        i = i + 1
    DX = Dec0x - Inc1x
    DY = Dec0y - Inc1y
    Do While Outx(i, 1) < Dec0x
        ResA(i, 1) = Inc1y + DY * (Outx(i, 1) - Inc1x) / DX
        i = i + 1
    DX = Dec1x - Dec0x
    DY = Dec0y - Dec1y
    Do While Outx(i, 1) < Dec1x
        OutXA(i, 1) = (Outx(i, 1) - Dec0x) / DX * Pi + Pi / 2
        ResA(i, 1) = Dec1y + DY * (Sin(OutXA(i, 1)) + 1) / 2
        i = i + 1
    Do While i <= NumX
        ResA(i, 1) = Dec1y
        i = i + 1
    i = i + 1

ScaleSin = ResA
End Function

An alternative approach suggested at the Eng-Tips discussion is to use a Sigmoid function of the form:

{\displaystyle S(x)={\frac {1}{1+e^{-x}}}.}

I have written another UDF to return such a function:

Function Sigmoid(xA As Variant, Optional a As Double = 1, Optional b As Double = 1, Optional c As Double = 1, _
            Optional d As Double = 1, Optional f As Double = -5, Optional t As Double = 0)
Dim Z As Double, NumX As Long, x As Double, i As Long, ResA() As Double

    xA = xA.Value2
    If IsArray(xA) Then
        NumX = UBound(xA)
        NumX = 1
    End If
    ReDim ResA(1 To NumX, 1 To 1)
    For i = 1 To NumX
        If NumX = 1 Then
            x = d * (xA - f)
            x = d * (xA(i, 1) - f)
        End If
        If x >= 0 Then
            ResA(i, 1) = a / (b + c * Exp(-x)) + t
            Z = Exp(x)
            ResA(i, 1) = a * Z / (b + c * Z) + t
        End If
    Next i
    Sigmoid = ResA
End Function

The sigmoid function also returns an array, but must be entered separately for the freeze and thaw part of the cycle.

Examples of the use of both functions applied to freeze-thaw data are given in the download file: Freeze-sin.xlsb

As usual, the download file includes full open-source code.


Posted in Arrays, Curve fitting, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , | 1 Comment

Counting non-pecked chicks

Download file:  Binary

This post is based on a recent discussion at Cosmic Horizons looking at numerical methods to solve the following problem:

The answer to the question is straightforward:

With a little thought, you should be able to realise that the answer is 25. For any particular chick, there are four potential out comes, each with equal probability. Either the chick is

  • pecked from the left
  • pecked from the right
  • pecked from left and right
  • not pecked at all
Only one of these options results in the chick being unpecked, and so the expected number of chicks unpecked in a circle of 100 is one quarter of this number, or 25.

But finding a theoretical solution to the distribution is not so simple.  A numerical simulation on the other hand can be set up in Python in a few lines of code.  Quoting Cosmic Horizons:

… if we treat a 0 as “chick pecks to the left”, and 1 as “check pecks to the right”, then if we choose a random integer between 0 and 2100-1,  and represent it as a binary number, then that will be a random sampling of the pecking order (pecking order, get it!) As an example, all chicks peck to the left would be 100 0s in binary, whereas all the chicks peck to the right would be 100 1s in binary.

I have adapted the Python code provided at Cosmic Horizons so that it can be called from Excel ( via xlwings) as a user defined function (UDF).  My version also allows the number of chicks and the number of repeats to be passed as function arguments (updated 18 Jun 17, unused xx array removed):

from bitarray import bitarray
from random import randint
import numpy as np

def CountPecks(nchick, nruns):
    nmax = 2**nchick
    nstore = np.zeros(nchick)

    s1 = bitarray('001')
    s2 = bitarray('011')

    for n in range(0, nruns):
        nn = randint(0, nmax-1)
        a = bitarray( "{0:b}".format(nmax+nn) )

        a[0] = a[ len(a)-1 ]

        v1 =
        v2 =

        nindex = len(v1) + len(v2)
        nstore[nindex] = nstore[nindex] + 1

    return nstore

Results from Excel for 100 chicks are:

The UDF is entered in cell C6 (=py_CountPecks, C3, C4), then entered as an array function:

  • Select the number of cells required for the output array (C6:C105).
  • Press Edit (F2)
  • Press Ctrl-Shift-Enter.

Having entered the function, the number of chicks and/or the number of repeats can be changed by entering the values in cells C3 and C4:

The function returns an array of length equal to the specified number of chicks, but note that the number of un-pecked chicks can never be more than half the total:

A similar algorithm can be set up directly on the spreadsheet (see screenshot below):

  • Column B contains functions:  =RANDBETWEEN(0,1)
  • Column C checks for sequences corresponding to an un-pecked chick, 0,x,1: =IF(AND(B4=0,B6=1),1,0)
  • Cell C3 counts the total number of un-pecked: =SUM(C5:C104)

A short VBA routine then repeats the calculation the specified number of times, and stores the results in column E:

Sub CountPecks()
Dim num As Long, i As Long, Counta(1 To 100, 1 To 1) As Long, Val As Long

Application.ScreenUpdating = False
num = [NRuns1]
For i = 1 To num
    Val = [Unpecked1]
    Counta(Val, 1) = Counta(Val, 1) + 1
Next i

[Res_1] = Counta
Application.ScreenUpdating = True
End Sub


This is simple to set up, but much slower than the Python version, since the spreadsheet generates 100 separate single digit values, rather than a single 100 digit binary value, as used in the Python code.

The VBA performance can be improved by using a similar approach to the Python code, but it is limited by the Excel Dec2Bin function, which is limited to a maximum decimal value of 511, or 9 binary digits.  The screen-shot below shows how a long binary string can be generated with repeated use of this function (see the download file for details).

A further improvement in the VBA speed can be gained by using the DecToBin UDF (previously described here), which will work with a decimal value up to 2^31-1, or 31 binary digits. This allows the 100 digit binary value to be generated from 3 31 digit values, plus one 7 digit value (see rows 21 to 24 below and in the download file).

The examples shown above, plus full VBA and Python code, can be found in Binary

The Python functions requires Python and xlwings to be installed.  The easiest way to install both is with Anaconda Python.

Posted in Excel, Link to Python, Maths, Newton, UDFs, VBA, xlwings | Tagged , , , , , , , | Leave a comment