Formula to extract info from every n row

Here is probably one of the easiest formulas I've used to do this:

=AVERAGE(OFFSET($A$1,(ROW()-ROW($D$1))*3,,3,))

$A$1 is the cell where the data to average begins.

$D$1 is the cell where the first average formula is entered, or just in the same row, it doesn't matter.

3 is the number of rows between an average. Change both to the desired number.

Update

This is an updated version of the above formula that should work for your data set.

=AVERAGE(OFFSET($B$2,(ROW()-ROW($D$2))*24,,24,))

Let me know if this is what you were looking for :)

The formula below can replace the formula in C2 of your worksheet from where it can be copied down. It will produce a fatal error if copied into the first row.

=IF(INT($A2)=INT(SUM($A1)),"", AVERAGEIFS($B:$B,$A:$A,">="&INT($A2),$A:$A,"<"&INT($A2)+1))

The IF statement uses INT($A2) to extract the day from the date in column A. INT(SUM($A1)) returns zero if A1 doesn't contain a date or other number when the plain INT($A1) would return an error. The effect is that an average is drawn whenever the date changes. Therefore it doesn't matter how many entries there are per day.

The AVERAGEIFS function averages values between the date in column A (extracted from the date/time value there) and the next day, where the exact date value is included (>=) and the next one excluded (<).

My formula specifies all columns as absolute which prevents it from being copied horizontally. The private rule I implement is that all addresses should be absolute unless copying, horizontally or vertically, is intended. In the case of this fomrula vertical copying is intended. Therefore row numbers are relative in my formula, indicating that it supports vertical copying.