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:

=(IF(DOY<DOY_FS,-1,IF(DOY>DOY_FE,1,SIN((DOY-F_Mid)/F_Days*PI())))+1)/2*A_max
=(IF(DOY<DOY_MS,1,IF(DOY>DOY_ME,-1,COS((DOY-DOY_MS)/M_Days*PI())))+1)/2*A_max

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
    Loop
    
    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
    Loop
    
    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
    Loop
    
    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
    Loop
    
    Do While i <= NumX
        ResA(i, 1) = Dec1y
        i = i + 1
    Loop
    i = i + 1
Loop

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)
    Else
        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)
        Else
            x = d * (xA(i, 1) - f)
        End If
        
        If x >= 0 Then
            ResA(i, 1) = a / (b + c * Exp(-x)) + t
        Else
            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.

 

Advertisements
This entry was posted in Arrays, Curve fitting, Excel, Maths, Newton, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

One Response to Plotting Freeze-Thaw Data …

  1. Pingback: #Excel Super Links #79 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s