How to Create Rainflow Counting Algorithm in Excel: 3 Methods

Method 1 – Establish a Data Model for Rainflow Counting Theorem

Create three columns titled Indicator, Damage-weight, and Time Interval.

Establish a Data Model For Rainflow Counting Theorem

Build two information boxes for the Maximum Indicator Read and Total Time Amount.

Make another two information boxes for the Time Accretion and Damage Origin.

Generate two columns named Indicator and Damage-weight for the reversal array.

Ceate an output table consisting of four columns called Indicator, Field, Mean Value, and Cycle Count.

Input the load throughout the C column, in this demo, Damage-weight.

Type 0 in cell B5 and choose the B6 cell.
Input the following equation in the Formula bar.

=IF(C6<>"",B5+1,"")

Hit Enter to see the outcome.

The AutoFill Handle icon and drag it to B20.

Type 3 and 75 are in the Time Accretion and Damage Origin boxes.

Choose the D5 cell, and write the formula in the bar.

=IF(C5<>"",B5*$D$25,"")

Hit Enter or Tab key to see the result.

Use the AutoFill Handle symbol and significantly move it to the D20 cell to get the intended output.

Choose D22, and write the following formula in the bar.

=MAX(B5:B20)

Press Enter to get the result.

Select D23, and type the below procedure.

=MAX(D5:D20)

Tap Enter.

Method 2 – Open Visual Basic for Applications Window to Build Rainflow Algorithm in Excel

Navigate to the Developer tab and click on the Visual Basic icon.

The VBA window will appear.
Go to the Insert tab and choose the Module option.

Method 3 – Run Excel VBA Code to Determine Rainflow Cycles

After the Module box opens up, input the code below in the box for the primary procedure called SoftekoRainflowAlgorithm.

Public Sub SoftekoRainflowAlgorithm()
Dim loadSoftekoData() As Double, NoiseSoftekoData As Long, counter As Long
Dim softekoSwitch() As Double, softekoNoiseOrigin As Double
Dim softekoCycleCounting() As Double
Worksheets("RainflowCountingAlgorithm").Activate
NoiseSoftekoData = Range("D22").Value
softekoNoiseOrigin = Range("D26").Value
ReDim loadSoftekoData(0 To NoiseSoftekoData)
For counter = 0 To NoiseSoftekoData
loadSoftekoData(counter) = Cells(counter + 6, 3).Value
Next counter
Call softekoPeaksValleys(loadSoftekoData(), NoiseSoftekoData, softekoNoiseOrigin, softekoSwitch())
Call softekoRainflowCalculator(softekoSwitch(), softekoCycleCounting())
End Sub

Click Save icon or press Ctrl + S with the xlsm extension.

Open the same Module box and append the following code to make available the softekoPeaksValleys procedure.

Public Sub softekoPeaksValleys(loadSoftekoData() As Double, NoiseSoftekoData As Long, softekoNoiseOrigin As Double, softekoSwitch() As Double)
Dim Maximum As Double, Minimum As Double, counter As Long, anotherCounter As Long
Dim route As Integer
Dim regionalSwitch()
anotherCounter = 0
route = 0
Maximum = loadSoftekoData(0)
Minimum = Maximum
ReDim regionalSwitch(0 To NoiseSoftekoData)
For counter = 1 To NoiseSoftekoData
    Select Case route
        Case 0
            If loadSoftekoData(counter) > Maximum Then
                Maximum = loadSoftekoData(counter)
            ElseIf loadSoftekoData(counter) < Minimum Then
                Minimum = loadSoftekoData(counter)
            End If
            If Maximum - loadSoftekoData(0) >= softekoNoiseOrigin Then
                regionalSwitch(0) = Minimum
                route = 1
                anotherCounter = 1
            ElseIf loadSoftekoData(0) - Minimum >= softekoNoiseOrigin Then
                regionalSwitch(0) = Maximum
                route = -1
                anotherCounter = 1
            End If
        Case 1
            If loadSoftekoData(counter) > Maximum Then
                Maximum = loadSoftekoData(counter)
            ElseIf Maximum - loadSoftekoData(counter) >= softekoNoiseOrigin Then
                regionalSwitch(anotherCounter) = Maximum
                route = -1
                Minimum = loadSoftekoData(counter)
                anotherCounter = anotherCounter + 1
            End If
        Case -1
            If loadSoftekoData(counter) < Minimum Then
                Minimum = loadSoftekoData(counter)
            ElseIf loadSoftekoData(counter) - Minimum >= softekoNoiseOrigin Then
                regionalSwitch(anotherCounter) = Minimum
                route = 1
                Maximum = loadSoftekoData(counter)
                anotherCounter = anotherCounter + 1
            End If
    End Select
    If counter = NoiseSoftekoData Then
        If route = 1 Then
            regionalSwitch(anotherCounter) = Maximum
        ElseIf route = -1 Then
            regionalSwitch(anotherCounter) = Minimum
        End If
    End If
Next counter
ReDim softekoSwitch(0 To anotherCounter)
For counter = 0 To anotherCounter
    softekoSwitch(counter) = regionalSwitch(counter)
    Cells(counter + 4, 14).Value = counter
    Cells(counter + 4, 15).Value = softekoSwitch(counter)
Next counter
For counter = anotherCounter + 1 To 1.5 * anotherCounter
    Cells(counter + 4, 14).Value = ""
    Cells(counter + 4, 15).Value = ""
Next counter
End Sub

Press Ctrl + S or the Save symbol.

Utilize another procedure called softekoRainflowCalculator, and paste the code below in the same Module box.

Public Sub softekoRainflowCalculator(softekoSwitch() As Double, softekoCycleCounting() As Double)
Dim softekoField As Double, softekoEarlierField As Double, softekoIndicator() As Long
Dim NoiseSoftekoData As Double, counter As Long, ActivityCheck() As Boolean
Dim tempCycleCashe() As Double
Dim anotherCounter As Long, InitialIndicator As Long
Dim completeTask As Boolean
NoiseSoftekoData = UBound(softekoSwitch())
ReDim softekoIndicator(0 To 2)
ReDim ActivityCheck(0 To NoiseSoftekoData)
ReDim tempCycleCashe(1 To 1.1 * NoiseSoftekoData, 0 To 2)
For counter = 0 To NoiseSoftekoData
    ActivityCheck(counter) = True
Next counter
anotherCounter = 1
InitialIndicator = 0
softekoIndicator(0) = 0
softekoIndicator(1) = 1
softekoEarlierField = Abs(softekoSwitch(1) - softekoSwitch(0))
For counter = 2 To NoiseSoftekoData
    softekoIndicator(2) = counter
    softekoField = Abs(softekoSwitch(softekoIndicator(2)) - softekoSwitch(softekoIndicator(1)))
    If softekoField < softekoEarlierField Then
        softekoIndicator(0) = softekoIndicator(1)
        softekoIndicator(1) = softekoIndicator(2)
        softekoEarlierField = softekoField
    ElseIf softekoIndicator(0) = InitialIndicator Then
        tempCycleCashe(anotherCounter, 0) = softekoEarlierField
        tempCycleCashe(anotherCounter, 1) = (softekoSwitch(softekoIndicator(0)) + softekoSwitch(softekoIndicator(1))) / 2  'mean stress
        tempCycleCashe(anotherCounter, 2) = 0.5
        ActivityCheck(softekoIndicator(0)) = False
        anotherCounter = anotherCounter + 1
        InitialIndicator = softekoIndicator(1)
        softekoIndicator(0) = softekoIndicator(1)
        softekoIndicator(1) = softekoIndicator(2)
        softekoEarlierField = softekoField
    Else
        tempCycleCashe(anotherCounter, 0) = softekoEarlierField
        tempCycleCashe(anotherCounter, 1) = (softekoSwitch(softekoIndicator(0)) + softekoSwitch(softekoIndicator(1))) / 2  'mean stress
        tempCycleCashe(anotherCounter, 2) = 1
        ActivityCheck(softekoIndicator(0)) = False
        ActivityCheck(softekoIndicator(1)) = False
        anotherCounter = anotherCounter + 1
        softekoIndicator(1) = NearestLowIndicator(softekoIndicator(2), ActivityCheck())
        If softekoIndicator(1) = InitialIndicator Then
            softekoIndicator(0) = softekoIndicator(1)
            softekoIndicator(1) = softekoIndicator(2)
            softekoEarlierField = Abs(softekoSwitch(softekoIndicator(1)) - softekoSwitch(softekoIndicator(0)))
        Else
            softekoIndicator(0) = NearestLowIndicator(softekoIndicator(1), ActivityCheck())
            completeTask = False
            
            While completeTask = False
                softekoEarlierField = Abs(softekoSwitch(softekoIndicator(1)) - softekoSwitch(softekoIndicator(0)))
                softekoField = Abs(softekoSwitch(softekoIndicator(2)) - softekoSwitch(softekoIndicator(1)))
                If softekoField < softekoEarlierField Then
                    softekoIndicator(0) = softekoIndicator(1)
                    softekoIndicator(1) = softekoIndicator(2)
                    softekoEarlierField = softekoField
                    completeTask = True
                ElseIf softekoIndicator(0) = InitialIndicator Then
                    tempCycleCashe(anotherCounter, 0) = softekoEarlierField
                    tempCycleCashe(anotherCounter, 1) = (softekoSwitch(softekoIndicator(0)) + softekoSwitch(softekoIndicator(1))) / 2  'mean stress
                    tempCycleCashe(anotherCounter, 2) = 0.5
                    ActivityCheck(softekoIndicator(0)) = False
                    anotherCounter = anotherCounter + 1
                    InitialIndicator = softekoIndicator(1)
                    softekoIndicator(0) = softekoIndicator(1)
                    softekoIndicator(1) = softekoIndicator(2)
                    softekoEarlierField = softekoField
                    completeTask = True
                Else
                    tempCycleCashe(anotherCounter, 0) = softekoEarlierField
                    tempCycleCashe(anotherCounter, 1) = (softekoSwitch(softekoIndicator(0)) + softekoSwitch(softekoIndicator(1))) / 2  'mean stress
                    tempCycleCashe(anotherCounter, 2) = 1
                    ActivityCheck(softekoIndicator(0)) = False
                    ActivityCheck(softekoIndicator(1)) = False
                    anotherCounter = anotherCounter + 1
                    softekoIndicator(1) = NearestLowIndicator(softekoIndicator(2), ActivityCheck())
                    If softekoIndicator(1) = InitialIndicator Then
                        softekoIndicator(0) = softekoIndicator(1)
                        softekoIndicator(1) = softekoIndicator(2)
                        softekoEarlierField = Abs(softekoSwitch(softekoIndicator(1)) - softekoSwitch(softekoIndicator(0)))
                        completeTask = True
                    Else
                        softekoIndicator(0) = NearestLowIndicator(softekoIndicator(1), ActivityCheck())
                    End If
                End If
            Wend
        End If
    End If
Next counter
If InitialIndicator < counter Then
    softekoIndicator(0) = InitialIndicator
    softekoIndicator(1) = nearestHighIndicator(softekoIndicator(0), ActivityCheck())
    completeTask = False
    While completeTask = False
        tempCycleCashe(anotherCounter, 0) = Abs(softekoSwitch(softekoIndicator(1)) - softekoSwitch(softekoIndicator(0)))
        tempCycleCashe(anotherCounter, 1) = (softekoSwitch(softekoIndicator(0)) + softekoSwitch(softekoIndicator(1))) / 2
        tempCycleCashe(anotherCounter, 2) = 0.5
        If softekoIndicator(1) = NoiseSoftekoData Then
            completeTask = True
        Else
            softekoIndicator(0) = softekoIndicator(1)
            softekoIndicator(1) = nearestHighIndicator(softekoIndicator(0), ActivityCheck())
            anotherCounter = anotherCounter + 1
        End If
    Wend
End If
ReDim softekoCycleCounting(1 To anotherCounter, 0 To 2)
For counter = 1 To anotherCounter
    softekoCycleCounting(counter, 0) = tempCycleCashe(counter, 0)
    softekoCycleCounting(counter, 1) = tempCycleCashe(counter, 1)
    softekoCycleCounting(counter, 2) = tempCycleCashe(counter, 2)
    Cells(counter + 3, 20).Value = counter
    Cells(counter + 3, 21).Value = softekoCycleCounting(counter, 0)
    Cells(counter + 3, 22).Value = softekoCycleCounting(counter, 1)
    Cells(counter + 3, 23).Value = softekoCycleCounting(counter, 2)
Next counter
End Sub

Use Ctrl + S or the Save icon.

Open the Module box again and add the code below for a Public Function called NearestLowIndicator.

Public Function NearestLowIndicator(softeko As Long, ActivityCheck() As Boolean) As Long
Dim completeTask As Boolean, counter As Long
completeTask = False
counter = softeko - 1
While completeTask = False
    If ActivityCheck(counter) = True Then
        completeTask = True
    Else
        counter = counter - 1
    End If
Wend
NearestLowIndicator = counter
End Function

Press Ctrl+S or the Save symbol.

Open the Module box again and enter the following code for another Public Function named nearestHighIndicator.

Public Function nearestHighIndicator(softeko As Long, ActivityCheck() As Boolean) As Long
Dim completeTask As Boolean, counter As Long
completeTask = False
counter = softeko + 1
While completeTask = False
    If ActivityCheck(counter) = True Then
        completeTask = True
    Else
        counter = counter + 1
    End If
Wend
nearestHighIndicator = counter
End Function