count based on whether numbers within a given range repeat 6 times in a row

[JennyReed]

Hi.
I have a data set of hourly wind values over a number of years. I have divided the wind into speed groups - for example:
range 1: >=0 - <=1m/s
range 2: >1 - <=3m/s
range 3: >3 - <=5m/s
range 4: >5 etc.

I would like to count if there is an occurrence of when the wind blew for 6 hours - or longer within the highest range (>5m/s).
i.e. did the wind blow at 5m/s or higher for 6 or more hours in a row at any time throughout the data set?
(is the data >5m/s for 6 rows of data).
For the same occurrence, I would like to know for how many hours the wind blew at >5cm/s .

For example:
5.5
6.5
5.2
6.0
7.8
5.6
9.0
1.2

Would give 1 occurrence with a total of 7 hrs (wind blew >5m/s for 7 data points in a row).

I then need to somehow 'delete' that occurrence from the data set so that I can then count how many occurrences there are when the wind blew for 6hrs or more in the next range (>3m/s). The 'deletion' is so that I do not double count.
For example, the wind could blow for 5 hrs between 3 - 5 m/s and then suddenly blow at 5.5m/s and the again at 4m/s - that would could as an (one) occurrence for range 3 (>3m/s).
I would again like to count the total number of hrs the wind blew for each occurrence at this range.
For example
3.5
3.5
3.2
6.0
3.8
3.6
2.2
This would give 1 occurrence for 6 hrs where the wind blew >3 m/s.

Again, I would then need to 'delete' this data so that I could move to the next lower range (i.e. >1m/s) without double counting.

Any idea how to achieve this ?

The final part is to this do this per month of the year. I.e., the same analysis for when the column of data for the month of the year = 1 for Jan, 2 for Feb etc.

Any help is much appreciated.
Jenny

 

[XLfriend777]

Hi JennyReed - Hope this helps get you started. I just played with one option on the high end looking for consecutive values > 5. Let me know if you have any questions.

Sub JennyReed()
Dim x As Integer
x = 1

Dim MatchFound As String
MatchFound = "No"

While x < 20
    If Cells(x, 1).Value > 5 And Cells(x, 1).Offset(1, 0).Value > 5 And Cells(x, 1).Offset(2, 0).Value > 5 And Cells(x, 1).Offset(3, 0).Value > 5 And Cells(x, 1).Offset(4, 0).Value > 5 And Cells(x, 1).Offset(5, 0).Value > 5 Then
    
    MatchFound = "Yes"
    End If
x = x + 1
Wend

MsgBox MatchFound

End Sub