Getting all combinations of hourly data (without repetition) for calculating daily average.

[Garluis]

Hey guys.

I'm trying to create a code on vba that generates all the daily averages for different combinations of hourly data, starting with the full 24 hour data, and then averaging all the combinations of that day with 1 hour less, then all the combinations with 2 hour less, and so on untill 8 hour less, and that for every day of my years-long database.

I've already created a code for all the daily averages of 1 hour less, but i'm struggling creating the combination code for 2+ hours less.

For example:

- For day 1 calculate the average of 24 data (resulting =1 value per day), then continue for day 2, and that for all the days (or 24 consecutive data);
- Then all the averages using 1 hour less per day (resulting=24 values per day 'cause of the combinations), then continue for day 2, and that for all the days (or 24 consecutive data);
- Then all the averages using 2 hour less per day ( resulting = 276 values per day 'cause of the combinations without repeating), then continue for day 2, and that for all the days (or 24 consecutive data);
- etc.

The purpose is to calculate all the averages using all the combinations possible (without repeating) for a given X hour(s) less (first daily average with 0 hours less, then daily averages of all combinations with 1 hour less, and so on), not a resulting from 0 to 8 hour less at once. I need to analyze every X hour less events independently.

Here is the code. The data needed is on column B, starting in B4 till lets say B125.

Private Sub CommandButton1_Click()
Dim Data() As Double
x = 1
b = 4
For Z = 28 To 125 Step 24
For a = b - 1 To Z
For row = b To Z
ReDim Data(row)
Data(row) = Val(Cells(row, 2))
If row = a Then
Sum = Sum
c = c
TextBox1 = TextBox1 & vbCrLf
Else
Sum = Sum + Data(row)
c = c + 1
TextBox1 = TextBox1 & vbCrLf & Data(row)
End If
Next row
average = Sum / c
TextBox1 = TextBox1 & vbCrLf & vbCrLf & "Average =" & vbTab & average & vbCrLf
Cells(1, 5) = Val(average)
Sum = 0
c = 0
Next a
b = Z
TextBox1 = TextBox1 & vbCrLf & vbCrLf & "*****" & vbCrLf & " End of Day:" & vbTab & x & vbCrLf & vbCrLf & "******" & vbCrLf
x = x + 1
Next Z
End Sub


THANKS IN ADVANCE!