Averaging consecutive groups of cells

Hi,
I have been trying to work out how to do this but haven't had any luck. It seems so simple so I'm sure there's an easy way to do it!

I have data in 2 columns, one date and time, and the second hourly readings for a year, and I want to get an average for each day in the third column.. I can't work out how to move the row references down 24 cells as they just move down one cell.

I've used =AVERAGE(B2:B25) to get day 1, but is there any way of telling it to do =AVERAGE(B26:B49) to populate the cell below, instead of the usual =AVERAGE(B3:B26) ?

Any help would be much appreciated

Thanks

OR alternatively

=AVERAGE(OFFSET($B$2,(ROW(A1)-1)*24,0,24,1))


Regards

Dav

Thanks for the advice, both methods work fine and solve the problem.

Hello,

I am sorry for my lack of excel skills. But I unsuccessfully tried to play with the above answers and I am still blocked...

My situation is very similar though.

On Column A, I have time (day, hours minutes) and on columns B to L different measurements values. Now the trick it that the measurements were made by burst of 10 (1 per second) every 15 minutes and I would like to average the 10 measurements for every 15 minutes. I guess there is not much to change from the above formulas but I am blocked...

Below is an example of the first rows of my table...

Thank you for your help, it is really appreciated (the instrument software can average based on the raw data only and those have been deleted!)

12/13/15 14:00 65.63 41.5 49.19 43.44 26.869 0.3 -0.5 146.6 -61.79 -16.69 3.19
12/13/15 14:00 63.85 37.8 50.43 39.17 26.871 0.3 -0.5 143.1 -60.06 -16.37 3.19
12/13/15 14:00 60.22 37.5 47.76 36.69 26.872 0.3 -0.5 141.2 -57.32 -13.9 3.2
12/13/15 14:00 62.68 40.3 47.82 40.52 26.873 0.3 -0.5 141.1 -60.72 -11.65 3.19
12/13/15 14:00 65.98 37.6 52.3 40.23 26.873 0.3 -0.5 141.1 -62.84 -15.14 3.19
12/13/15 14:00 62.62 38.8 48.78 39.26 26.875 0.3 -0.5 141.1 -60.14 -13.1 3.19
12/13/15 14:00 64.61 37.1 51.56 38.93 26.873 0.3 -0.5 141.1 -61.32 -15.35 3.19
12/13/15 14:00 63.55 38.9 49.48 39.87 26.875 0.3 -0.5 141.1 -61.06 -13.24 3.19
12/13/15 14:00 63.74 37.7 50.4 39.02 26.875 0.3 -0.5 141.1 -60.79 -14.42 3.19
12/13/15 14:00 64.53 36.7 51.73 38.58 26.875 0.3 -0.5 141.1 -61.09 -15.66 3.19
12/13/15 14:15 169.33 168.8 -166.11 32.88 27.127 0 -0.5 175.1 -18.49 167.51 3.19
12/13/15 14:15 163.41 231.3 -102.12 -127.56 27.128 0.1 -0.5 162.5 145.74 56.49 3.19
12/13/15 14:15 91.22 86.8 5.15 91.07 27.129 0.1 -0.5 158.9 -83.98 27.02 3.19
12/13/15 14:15 392.53 4.7 391.23 31.89 27.129 0.2 -0.5 153.7 -187.27 -312.36 3.19
12/13/15 14:15 79.6 254.9 -20.69 -76.86 27.13 0.3 -0.5 143.7 70.94 -27.6 3.19
12/13/15 14:15 233.22 304.5 132.03 -192.25 27.13 0.4 -0.4 135.7 44.67 -225.5 3.19
12/13/15 14:15 274.97 327.6 232.09 -147.46 27.13 0.4 -0.4 129.5 -82.56 -252.55 3.19
12/13/15 14:15 93.66 87.1 4.73 93.54 27.132 0.5 -0.4 125.1 -52.7 67.46 3.19
12/13/15 14:15 119.13 98.7 -17.98 117.77 27.132 0.6 -0.4 117.9 -37.7 108.34 3.19
12/13/15 14:15 116.8 273.3 6.64 -116.61 27.132 0.6 -0.3 109.8 32.28 -108.73 3.19