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

Taming Symbols in Excel

You can enter an ASCII character in Excel (if you know the ASCII code) by holding  down the Alt key and entering the decimal code value on the numeric key pad.  This has two problems:

  • It doesn’t work in Excel for the extended Unicode character set.
  • Depending on your regional settings, you may get inconsistent results.

This post presents some alternatives for entering symbols from the vast Unicode collection in Excel.  Open source VBA code and examples can be found in:


The Unichar function was introduced in Excel 2013.  This function returns the Unicode symbol for any given decimal code value.  In the Unicode spreadsheet I have used this function to generate a table of values and symbols for any of the lists of mathematical operators and symbols given by Wikipedia at Mathematical Unicode Symbols.

The screen-shot below shows the list of pre-defined tables (click on any image for full size view):

Enter 1-16 in cell A26 to generate one of the 16 pre-defined tables:

Or enter any hex value greater than 16 (up to the upper limit of Unicode values) to generate a table of symbols starting from that value:

The Unichar function allows Excel to generate any Unicode symbol, but it does require looking up (or learning) the correct decimal code for any symbol you might need.

For those using Excel 2010 or earlier, or if you don’t have access to the code value for the symbol you want, you can insert Unicode symbols from the Insert-Symbol dialog, on the Insert Tab, but this is a slow process, especially if you need to search for the particular symbol you want.  A more convenient approach was suggested at an Eng-Tips discussion, linking to  It is possible to add any desired symbol to the Excel auto-correct list (see the excel-tips link for details), so symbols can be easily generated by entering an easy to remember short-cut code, such as (deg) for the degree symbol.

Adding a large number of symbols to the aut0-correct list is also pretty laborious though, so I have combined this feature with the UniChar function and some VBA code to allow any number of short-cuts and symbols to be added automatically:

Clicking the “Update Auto-correct” button on the download spreadsheet will update the list (for those with Excel 2013 or later), and the short-cuts listed will then be available from any Excel spreadsheet, or any other Office application.  Note that:

  • The Unicode spreadsheet list includes 24 mathematical operators, plus the Greek alphabet in upper and lower case.
  • The macro automatically surrounds the short-cuts with (), so for instance to generate the sum symbol enter: (sum).
  • To extend or edit the list, just enter the short-cut and the symbol decimal code value, and adjust the named range “UCList” to the new extent of the two columns.  The symbol in the third column is for reference, and to check that the code number is correct, but is not required for the macro to work.
  • Symbols may also be removed from the Auto-correct list by adding them to the UCListold range, adjusting the named range extent, and clicking the Remove-Auto-correct button.

The spreadsheet shows an example of the revised auto-correct in operation.  Entering:

Cos(30(deg)) . (alpha)(+-) (int) ((beta) * x(^2)) (>=) (omega_)

displays as:

Cos(30°) . α ± ∫(β * x²) ≥ Ω

If an unintended auto-correct is applied just press Ctrl-Z immediately, the text will then revert to the characters actually entered.  This applies to all auto-corrects incidentally, including such annoying ones as removing the second upper case character when you enter units such as MPa.

As an alternative to entering the short-cuts including the surrounding (), the Unicode spreadsheet also includes a UCode user defined function (UDF), that will convert all text in the UCList table to the associated symbol (in Excel 2013 and later). So if:

Cos(30deg).alpha+- int (beta * x^2) >= omega_

is entered in cell H16, it can be converted to:

Cos(30°).α± ∫ (β * x²) ≥ Ω

with the function: =UCode(H16).

To convert the UDF to a text string: press Edit (F2), then Re-calculate (F9) and enter.

This UDF of course only works in the Unicode spreadsheet (or if the function VBA code is copied to another spreadsheet).  Note that the UDF does not use the Excel Auto-correct list, it uses the UCList range on the spreadsheet.  If you don’t want to change the Auto-correct list, the UDF will still work.

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

Green grow the laurels

from Sandy Denny:

Not to be confused with:


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

3DFrame update

The update to the Glob_to_loc3 and Loc_to_glob3 functions (and associated functions) has now been transferred to the 3DFrame spreadsheet, which will now give correct results when beams are defined in the global negative Z direction.  The revised spreadsheet (including full open source code and compiled solver files) may be downloaded from:

See 3DFrame with spring releases for more details, including examples, and 3D Frames, axes and stiffness matrices for information on local axis conventions used, and details of the procedure for generating the 3D stiffness matrix.


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

Update to Glob_to_loc3 and Loc_to_glob3 functions

Following a comment here I have updated the Glob_to_loc3 and Loc_to_glob3 functions to deal with some problems when the local longitudinal axis is parallel to the global Y or Z axes.  The IP2 spreadsheet, including the revised functions, can be downloaded from:

The screen shots below show documentation for the functions, and an example of results with a beam parallel to the Z axis:

The documentation has been edited to clarify the difference between the axis type options:

The same example is used for both functions, with a beam parallel to the global Z axis, with the direction from Node 1 to Node 2 in the negative Z direction:


The function also has an option to return the rotation matrix, R0 (Out = 1):

The download file has the input for the Glob_to_loc function linked to the Glob_to_loc3 input, and the associated Gamma angle, to show that the results are consistent.  The input for these (or any other example functions) may of course be freely modified.

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

Working with milliseconds in Excel

A recent post at Eng-Tips looked for a solution to problems with working with times in Excel, when time differences are required to the nearest millisecond.

The problem is that although times may be entered as a time/date value, and formatted to display milliseconds, the format bar displays the time to the nearest second, and if you press F2 to edit a value, the time value is actually rounded to the second.  Pressing F2 then enter not only changes the displayed value, it changes the actual number:

The simplest way to avoid the problem is to enter the number as text, by starting with ‘ .  Pressing F2 then Enter will not change the text, but simple arithmetic will work:

Using text does have a few disadvantages though:

  • The time is rounded to the nearest millisecond, so if greater precision is required, this is not a suitable solution.
  • It is not possible to include days in the text, although an hours value greater than 24 is interpreted correctly.
  • The text may be misinterpreted if it is not entered in full.  For instance ‘8:23 is interpreted as 8 hours 23 minutes, but ‘8:23.0 is interpreted as 8 minutes 23 seconds.

An alternative approach is to convert the time to a date value in a formula, which will not be rounded by Excel.  A simple user defined function (UDF) that will do the calculation is shown below:

Function msTime(Secs As Double, Optional Mins As Double, _ 
Optional Hours As Double, Optional days As Double) As Double
Const SecsinDay As Long = 86400, MinsinDay As Long = 1440, HoursinDay As Long = 24

    msTime = days + Hours / HoursinDay + Mins / MinsinDay + Secs / SecsinDay

End Function

Examples of usage of the UDF are shown in the screenshot below.  Note that:

  • Differences of less than 1 millisecond are retained
  • Days may be included if required
  • All the arguments other than seconds are optional
  • The values may be entered as cell references, or directly as values, as for a built-in Excel function

A spreadsheet including the examples shown above, and all three lines of VBA code, may be downloaded from msTime.xlsb

Posted in Computing - general, Excel, UDFs, VBA | Tagged , , , , , , | 1 Comment