average range of data with missing cells

[mdavid800]

Hi there

I was looking for some help

I have a long data range which consists of solar radiation measurements .To this data i am averaging every 5 readings (i.e. taking 5 minute averages of my data). To do this I am using the code attached below.

 Sub Average_5min()
Dim i As Long
For i = 2 To 43173 Step 5
    Range("N" & Rows.Count).End(xlUp).Offset(1).Value = WorksheetFunction.Average(Range("F" & i).Resize(5))
Next i
End Sub

However the problem I have come up against is that some of the data is missing thus it is putting my averages off slightly by the end.

Besides the column with the measurements I have columns with the hour and minute the value was recorded.

I was wondering if any of you had an idea of how to set the VBA code attached to see if the values are between a range and then average they values.

For example if my solar radiation is in column F and the minute it was recorded is in column E I would like the code to look at the minute value column and average every 5 minutes. So for the example below it would average from minute 0 to minute 4 and display a value then it would average from minute 5 to minute 9 and give me a value i.e. notice how minute 7 & 8 are missing so this would only be the average of 3 cells were the first average was the average of 5 cells .

(E) (F)

0 10
1 11
2 12
3 19
4 20
5 25
6 26
9 11

Does anybody have any idea how this might be achieve I would need to go up to minute 60 and then reset back to zero for the next hour. I am totally stumped by this one any help is much appreciated.

David

[Bob Phillips]

[vba]


Sub Average_5min()
Dim StartMin As Long
Dim Lastrow As Long
Dim i As Long
Dim j As Long

Lastrow = Cells(Rows.Count, "E").End(xlUp).Row
For i = 2 To Lastrow
j = i
StartMin = Cells(i, "E").Value
Do While Cells(j, "E").Value <= StartMin + 5 And j <= Lastrow
j = j + 1
Loop
Range("N" & Rows.Count).End(xlUp).Offset(1).Value = Application.Average(Cells(i, "F").Resize(j - i))
i = j - 1
Next i
End Sub
[/vba]

[mdavid800]

Hi Xld

this seems to be doing the job but at the end it averages all the cells down to the last row. I would like it to stop at minute 60 and then do the same procedure for the next hour and every subsequent hour .

currently it averages every 5 cells up until minute 54 and then averages the next 30000 cells all in one go

any ideas.

David

[Bob Phillips]

David,

Can you post me a workbook to play with, makes it easier for me?

[mdavid800]

Hi Xld

Hopefully you will find the workbook attached.

as you can see the minute column has some missing minutes the code you gave me before works well but as the minute count resets to zero at the end of the hour the code stops working as it continues to increase by one. I.e. at the start of the second hour in the minute column i have 0 as its the start of a new hour but the code is looking for 61 , i can not for the life of me figure out how to fix it.

Your help is really appreciated

Regards
David

Solar radiation belfast workbook1.xlsm