mdavid800
08-02-2011, 09:35 AM
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
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