# 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.

• 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
For counter = 0 To NoiseSoftekoData
loadSoftekoData(counter) = Cells(counter + 6, 3).Value
Next counter
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
Minimum = Maximum
ReDim regionalSwitch(0 To NoiseSoftekoData)
For counter = 1 To NoiseSoftekoData
Select Case route
Case 0
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
ElseIf Maximum - loadSoftekoData(counter) >= softekoNoiseOrigin Then
regionalSwitch(anotherCounter) = Maximum
route = -1
anotherCounter = anotherCounter + 1
End If
Case -1
ElseIf loadSoftekoData(counter) - Minimum >= softekoNoiseOrigin Then
regionalSwitch(anotherCounter) = Minimum
route = 1
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
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())

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
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())
End If
End If
Wend
End If
End If
Next counter
If InitialIndicator < counter Then
softekoIndicator(0) = InitialIndicator
softekoIndicator(1) = nearestHighIndicator(softekoIndicator(0), ActivityCheck())
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
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
counter = softeko - 1
If ActivityCheck(counter) = True Then
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
counter = softeko + 1
If ActivityCheck(counter) = True Then
Else
counter = counter + 1
End If
Wend
nearestHighIndicator = counter
End Function``````
• Press  Ctrl + S  or click the Save icon.

• Go to the Developer tab and click on the Macros symbol.

• The Macros window appears.
• Choose the main procedure called SoftekoRainflowAlgorithm.
• Hit the Run button.

• Get the array reversal like the one below.

• Provide the desired output, like the following.

Please click on the link below this paragraph if you want a free copy of the sample workbook referenced in the presentation.

## Related Articles

<< Go Back to Algorithm in Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF