I have time in Col A and values in Col B. I'm trying to get the average every 15 minutes but the number of row in each 15 minute block is variable.
I'm using this code:
Sub fifteenminaverage() LastRow = Range("A" & Rows.Count).End(xlUp).Row output = False i = 2 Do Until i > LastRow If output = False Then firstOutput = "B" & i maxTime = Range("A" & i).Value + TimeValue("00:15") output = True End If If Range("A" & i).Value > maxTime Then i = i - 1 Range("C" & i).Value = "=AVERAGE(" & firstOutput & ":B" & i & ")" output = False End If i = i + 1 Loop i = i - 1 Range("C" & i).Value = "=AVERAGE(" & firstOutput & ":B" & i & ")" End Sub
However, sometimes is does not work and calculates an average with an extra cell?
I've attached a small example. On row 65 the average should of been inserted in row 64. It is out by one.
Would really appreciate it if somebody could advise?