I have read many similar questions but can't figure out my specific scenario. I have a repeating 8 row pattern of which I need to find the average of all of the first 5 rows of this 8 row pattern (row % 8 < 5), starting with B12. The cells are in the format mm:ss.
I also want to exclude all values of 5:00 (stored in J2) or greater. Here's what I have so far (getting a div/0 error) :
=ArrayFormula(AVERAGE(if(AND(B12:B685 < J2, mod(row(B12:B685) - 12,8)< 5),B12:B685)))
The sheet is here https://docs.google.com/spreadsheets/d/1iou9TSpX3UpfZNY4anQLoHPwVZ3qEvLvoISWsVt-0RU (See I3) if you want to see what I'm working with
Fix J2 so the format is the same as column A, then try this formula:
=AVERAGE( QUERY(A12:B, "select B, A where A > date '2000-1-1' And B < timeofday '" & TEXT(J2, "hh:mm:ss") & "'"))
The QUERY() is grabbing all items in column B where the Date in column A is more recent that Jan 1, 2000 to remove the blank and non-date values in Column A and also all values of B that are less than the entry in cell J2.