excel formula to calculate average of consecutive 24 hour blocks?

Question

Microsoft Excel 2010, input file of thousands of rows of hourly data, thus there are 24 values (rows) per day.

Let's assume that my data starts at B1, and let's assume that I put my average value for Day 1 into C1. The content of the C1 cell will be =AVERAGE(A1:A24), so far so good.

The question is what do I need to put into the C2 cell to make it so that the content of the C2 cell will become the average of A:25 through A:48 in such a way that I can copy the same formula down the C column and it appropriately fills in C3 as average of A:49 through A:72 and so on?

Answer 1

A variation on Excellll's approach.....

Try this formula in C1 copied down

=AVERAGE(OFFSET(A$1,(ROWS(C$1:C1)-1)*24,0,24))

....or....one drawback with the above is that it might be hard to understand what the formula is actually doing: This approach will put the explicit AVERAGE formulas in each cell:

Paste this formula in C1 and copy down

="=AVERAGE(A"&(ROWS(C$1:C1)-1)*24+1&":A"&ROWS(C$1:C1)*24&")"

That will create all the formulas as text...

Now convert those to actual formulas by doing this

Select column > right click > Copy > right click > Paste Special > Values > OK > Text to columns > Finish

Answer 2

If it can work in your context, I would consider a PivotTable to do this.

Assuming that your hours are in proper excel date-time format (if not, create a new column and make a formula to transform it to date-time):

• Select A1 to B5000 (or as far as you think you'll go eventually)
• Create a PivotTable and place it in column C
• Add A-Hours in the Row Labels area
• Group it by days (right-click on a date, select Group, then pick Days)
• Add B-Data in the Values area and set it to average instead of sum.

This solution is:

• simple to implement
• easy to understand
• flexible (can be moved, filtered, summarized by month, etc.)
• error-proof/worry-free: if you're missing a row or somehow break the offset, it will still calculate your daily averages properly based on the actual data instead of relying on row number assumptions. You can even add an extra Count in the values area just to confirm you do have 24 values on every day.