Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
you could use the indirect function –  soandos May 25 '12 at 19:22
3  
See this previous question (and my solution): superuser.com/a/300388/76571 –  Excellll May 25 '12 at 19:25
add comment

2 Answers

up vote 2 down vote accepted

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

share|improve this answer
add comment

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.