average range of data with missing cells (VBA)

[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 and average all the values in column F when minute is between a range of 5 i.e.( 1 to 5 , then 6 to 10 and so on). Therefore when I am missing a value say the 4th minute I will get the average of 4 cells. But for the 5minutes after I will have the correct average and will not be 1 minute off.

I hope this is clear , does anybody have any idea how to do this , it doesn’t need to use the code ive been using already , if its no use.

Thank you in advance