Microsoft Excel is one of the most valuable tools available. Using Excelâ€™s capabilities and tools, a dataset can be utilized in an almost limitless way. This article describes the steps required to construct the rain-flow counting algorithm in Microsoft Excel. The Rainfall Counting Theorem demonstrates how to calculate the number of damage cycles from a load-time reading. In light of this, the following lesson will focus on constructing the Rainflow Counting Algorithm in Excel.

**Table of Contents**hide

## What Is Rainflow Counting Algorithm?

You can determine the number of stress rounds from a given time series using the **Rainflow Theorem**. In terms of stress, the depletion cycles represent cyclical relaxation and tension. **Matsuishi** and **Endo**â€˜s **Rainflow** **Counting** methodology was the first widely used method for extracting cyclic loading setbacks or processes. Utilizing the **Rainflow Counting Algorithm**, we may examine and assess its stress component by keeping track of the number of cycles inside a stress signal.

## How to Create Rainflow Counting Algorithm in Excel (With Easy Steps)

If the appropriate steps are not followed, establishing the **Rainflow Counting Algorithm** in Excel could prove to be a difficult task. This post will walk you through the two procedures required to build the rain-flow counting method, which determines the number of damage cycles based on a **Weight-Time** reading. We will get started on this experiment by putting together a dataset first. The algorithm will be constructed with the help of **Excel VBA** code. If you listen carefully to these instructions, you will pick up the method very quickly.

### Step 1: Establish a Data Model for Rainflow Counting Theorem

The first and foremost step is to create a dataset for illustration purposes. In this article, we will consider the below dataset. The dataset we will work with has three columns titled **Indicator**, **Damage-weight,** and **Time Interval**. We will make other necessary information throughout the following steps, like **Maximum Indicator** and **Total Time Amount**. After that, we have to create another two information boxes named **Time Accretion** and **Damage Origin**. Moreover, two columns will be in need, titled **Indicator** and **Damage-weight**, for the reversal array. Lastly, we will demonstrate the other four columns, **Indicator**, **Field**, **Mean Value,** and **Cycle Count**, to get the output.

- First, create three columns titled
**Indicator**,**Damage-weight,**and**Time Interval**.

- Second, build two information boxes for the
**Maximum Indicator Read**and**Total Time Amount**.

- After that, make another two information boxes for the
**Time Accretion**and**Damage Origin**.

- Later, generate two columns named
**Indicator**and**Damage-weight**for the reversal array.

- Lastly, create an output table consisting of four columns called
**Indicator**,**Field**,**Mean Value,**and**Cycle Count**.

- At this point, input the load throughout the
**C**column, in this demo,**Damage-weight**.

- Next, type
**0**in cell**B5**and choose the**B6Â**cell. - After that, input the following equation in the
**FormulaÂ**bar.

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

- Now, hit
**Enter**to see the outcome.

- Utilize the
**AutoFill Handle**icon and drag it to**B20**.

- Type
**3**and**75**are in the**Time Accretion**and**Damage Origin**boxes, respectively.

- Now, choose the
**D5**cell, and write the formula in the bar.

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

- Hit the
**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.

- Presently, choose
**D22**, and write the following formula in the bar.

`=MAX(B5:B20)`

- Press
**Enter**to get the result.

- Likewise, select
**D23**, and type the below procedure.

`=MAX(D5:D20)`

- After that, tap
**Enter**.

**Read More: **How to Make Decision Tree Algorithm in Excel

### Step 2: Open Visual Basic for Applications Window to Build Rainflow Algorithm in Excel

The second step will open the **Visual Basic for Applications** window to write **VBA** code in Excel. To achieve this, we have to go to the **Developer** tab. After that, from the **Code** group, we must choose **Visual Basic**.

- First, navigate to the
**Developer**tab and click on the**Visual BasicÂ**icon.

- Consequently, the VBA window will appear.
- After that, go to the
**Insert**tab and choose the**ModuleÂ**option.

**Read More: **How to Build Lottery Prediction Algorithm in Excel

### Step 3: Run Excel VBA Code to Determine Rainflow Cycles

In the last step of this context, we will write some **VBA **code to initiate the algorithm to determine the number of cycles from **load-time** reading. Here, we will declare a public procedure named **SoftekoRainflowAlgorithm **which will call other necessary procedures like **softekoPeaksValleys **and **softekoRainflowCalculator**. In addition, we will build two public functions called **NearestLowIndicator **and **nearestHighIndictor **to conclude.

- 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
```

- Later, click on the
**Save**icon or pressÂ**Ctrl + SÂ**with theÂ**xlsmÂ**extension.

- Next, 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
```

- Then, pressÂ
**Ctrl + SÂ**or the**SaveÂ**symbol.

- After that, to utilize another procedure called
**softekoRainflowCalculator**, 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
```

- Then, either useÂ
**Ctrl + SÂ**or the**SaveÂ**icon.

- Eventually, 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
```

- Then, pressÂ
**Ctrl+SÂ**or the**SaveÂ**symbol.

- Open the
**Module**box once more 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
```

- Then, either pressÂ
**Ctrl + SÂ**or click the**SaveÂ**icon.

- Now, go to the
**Developer**tab and click on the**MacrosÂ**symbol.

- Consequently, the
**Macros**window appears. - Later, choose the main procedure called
**SoftekoRainflowAlgorithm.** - After that, hit the
**RunÂ**button.

- Due to this, we will get the array reversal like the below one.

- Finally, it will provide the desired output, like the following.

**Read More: **How to Create Betting Algorithm in Excel

**Download Practice Workbook**

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

## Conclusion

You can establish the **Rainflow Counting Algorithm** in Excel by following our steps. If you have any other ideas or better methods, please let me know.