Macro help needed to Average Values from column C if Column A is same day and Column B is same hour, with variable # entries of both :(

[amandabstewart]

Data is set up as a combined date/time in column A. I have a macro running to separate that into 2 separate columns, and then turn the time of day into a 1 or 2 digit hour code (ie 24h time) as shown below

I need it to average the values for cage 1 that are within the day and hour and repeat to the end. Not shown is the width of the sheet--it continues as such to Cage 12.

so, here I would need it to return 982.3785 as the average for cage 1 that occurred on the 3rd at the 10th hour
for the next group, I need the average of the 3 values sown for the 3rd in the 11th hour. Data might be 1 observation, 2, or 3, but not more than 3 readings within an hour.

Is there any way to do this?

 

[offthelip]

You haven't said where you want the averages put so I have put them into column E:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
Range(Cells(1, 5), Cells(lastrow, 5)) = ""
outarr = Range(Cells(1, 5), Cells(lastrow, 5))

curdate = inarr(2, 1)
curtime = inarr(2, 3)
cnt = 0
Sum = 0
For i = 2 To lastrow
  If inarr(i, 1) = curdate And inarr(i, 3) = curtime Then
   Sum = Sum + inarr(i, 4)
   cnt = cnt + 1
  Else
   outarr(i - 1, 1) = Sum / cnt
   Sum = inarr(i, 4)
   cnt = 1
   curdate = inarr(i, 1)
   curtime = inarr(i, 3)
  End If
Next i
   outarr(i - 1, 1) = Sum / cnt

Range(Cells(1, 5), Cells(lastrow, 5)) = outarr

End Sub

 

[amandabstewart]

You haven't said where you want the averages put so I have put them into column E:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
Range(Cells(1, 5), Cells(lastrow, 5)) = ""
outarr = Range(Cells(1, 5), Cells(lastrow, 5))

curdate = inarr(2, 1)
curtime = inarr(2, 3)
cnt = 0
Sum = 0
For i = 2 To lastrow
  If inarr(i, 1) = curdate And inarr(i, 3) = curtime Then
   Sum = Sum + inarr(i, 4)
   cnt = cnt + 1
  Else
   outarr(i - 1, 1) = Sum / cnt
   Sum = inarr(i, 4)
   cnt = 1
   curdate = inarr(i, 1)
   curtime = inarr(i, 3)
  End If
Next i
   outarr(i - 1, 1) = Sum / cnt

Range(Cells(1, 5), Cells(lastrow, 5)) = outarr

End Sub

Thanks! I ended up doing a very dumb averageifs statement but will replace it with this! thanks soooooo much!!!!