Averaging every 24th row, and posting the result to an adjacent column

TommyC

New Member
Joined
Apr 10, 2013
Messages
8
I have a long list of hourly values in Column C and am interested in generating averages of blocks of 24 consecutive cells. I have searched via Google and found a couple formulas that don't quite work. I also tried to search Mr. Excel and got no hits on this task.

The pattern I'd like is to average, say, C31:C54 and place the result in Cell D54.
Then average C55:C78 and place the result in Cell D78, and so forth.

The examples I've seen placed all the averages in consecutive rows within an adjacent column, but I think I'd like to have them spread out in association with the 24-row blocks they represent. I hope that makes sense.

Any help is appreciated.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
If I understand your question correctly you should be able to -

First: create an Average() formula on the first row to sum the 24 cells next to and above it.

Second: Scroll down another 24 cells and repeat step 1 with a new Average () formula to take the 24 cells above that one.

Third: Now if you Highlight the column with the average formulas, start 24 cells above the first formula so the step 1's average formula would be the 25 cell you highlight with 24 blank cells above it. Continue to highlight until you have selected the second average formula. you should now have either 48 or 50 cells highlighted depending on if you put the average formula underneath or next to the 24th cell that will be averaged.

Fourth: Now that you have all the cells highlighted. Grab the small green box in the bottom right corner of the highlighted portion and drag it down for as far as you want to average. There should be an average formula popping up every 24th cell and only averaging the 24 above it.

Hope that helps. Sorry if I made it sound more confusing than it really is.
 
Upvote 0
Easier yet. Don't drag the green box from step 4, just double click it if is in Column D or in the cell directly to the right of what you are trying to average. It should autofill as far as your list goes.
 
Upvote 0
Thanks for your prompt reply, Excelstudent 33. What you outlined seems easier than calculating averaged for 24 cells at a time doing down Column C.

Not trying to 2nd guess you but I must wonder if you’ve seen any methods such as these, which are “totally automated”:

applying formula repeatedly to blocks of cells
excel formula to calculate average of consecutive 24 hour blocks? - Super User

I tried the 2nd approach unsuccessfully; it seems rather complicated (over my head).

Any thoughts about these approaches to accomplish this task?
 
Upvote 0
Assuming your very first hourly value is in cell C7, place the following formula in cell D7 and copy down as needed:

=IF(MOD(ROW(),24)=6,AVERAGE(OFFSET(C7,-23,0,24,1)),"")

If your very first hourly value is in another cell, adjust the 6 accordingly.
 
Last edited:
Upvote 0
Try to post a scaled-down sample and pretend that you want the average for 4 cells blocks...

Row\Col
C​
D​
30​
4
31​
2​
32​
2​
33​
2​
34​
2​
2​
35​
2​
36​
2​
37​
2​
38​
8​
3.5​
39​
1​
40​
1​
41​
3​
42​
6​
2.75​
43​
8​
44​
20​
45​
30​
46​
40​
24.5​

In D31 enter and copy down:

=IF(MOD(ROW()-ROW($D$31)+1,$D$30)=0,IFERROR(AVERAGE(OFFSET($C31,-($D$30-1),0,$D$30)),""),"")

Change D30 to 24 for your data. You might want to ditch the IFERROR call for more speed.
 
Upvote 0
Since the first block of cells to average are C31 -> C54, I used the formula in Post 6 as follows:
=IF(MOD(ROW(),24)=30,AVERAGE(OFFSET(C31,-23,0,24,1)),"")

Since 6 is one less than 7 (as inC7), I used C31 and 30. I tried to follow the pattern.

When applied, nothing happened (no average appeared anywhere). Again though, I'd like the average to appear in Cell D54.
 
Upvote 0
Sorry, I should have provided a more comprehensive description.
The number is calculated as =MOD(very_first_row,24)-1.

For cell D31, there is no need to change the '6', so the formula is:

=IF(MOD(ROW(),24)=6,AVERAGE(OFFSET(C31,-23,0,24,1)),"")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top