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

[TommyC]

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.

 

[Tetra201]

The number is calculated as =MOD(very_first_row,24)-1.

Correction: the number is calculated as =MOD(very_first_row-1,24).

 

[Aladin Akyurek]

[...]
When applied, nothing happened (no average appeared anywhere). Again though, I'd like the average to appear in Cell D54.

Try the suggestion of post #7. It's robust... 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)),""),"")

where D30 = 24.

For more speed, remove IFERROR...

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

 

[Tetra201]

No need to overload the original formula with extraneous $D$30, $D$31. Without them, the formula is slimmer and works faster.

In cell D31 enter =IF(MOD(ROW(),24)=6,AVERAGE(OFFSET(C31,-23,0,24,1)),"") then copy down as needed.

 

[Aladin Akyurek]

Try the suggestion of post #7. It's robust... 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)),""),"")

where D30 = 24.

For more speed, remove IFERROR...

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

The following takes out the volatile ROW() for more speed while staying robust...

In D31 enter and copy down:

=IF(MOD(ROWS($D$31:D31),$D$30)=0,AVERAGE(OFFSET($C31,-($D$30-1),0,$D$30)),"")

D30 still houses the desired block size.

 

[Tetra201]

No need to overcomplicate the original formula with almost half a dozen cell references to achieve the same result.

In cell D31 enter =IF(MOD(ROW(),24)=6,AVERAGE(OFFSET(C31,-23,0,24,1)),"") then copy down as needed.

 

[TommyC]

I pasted the formula in Post #10 directly into Cell D54. It returned a value of '0' (which is correct for the corresponding cells to be averaged in Column C; they were all zero). When I double clicked the 'x' in the lower right-hand corner of Cell D54 (to apply the formula to the entire Column D), it only went down about 165 cells, and changed the formatting of values in Column C from integer to real w/ 3 decimal places.

I'm wondering if there's an easy way to attach the Excel file and maybe someone could take a stab at this. That might be quicker in the long run.
I appreciate all the help.

 

[Aladin Akyurek]

I pasted the formula in Post #10 directly into Cell D54. It returned a value of '0' (which is correct for the corresponding cells to be averaged in Column C; they were all zero). When I double clicked the 'x' in the lower right-hand corner of Cell D54 (to apply the formula to the entire Column D), it only went down about 165 cells, and changed the formatting of values in Column C from integer to real w/ 3 decimal places.

I'm wondering if there's an easy way to attach the Excel file and maybe someone could take a stab at this. That might be quicker in the long run.
I appreciate all the help.

Enter the suggested formula (the one I suggested) and copy down.

Thus: Enter 24 in D30.

In D31 enter and copy down:

=IF(MOD(ROWS($D$31:D31),$D$30)=0,AVERAGE(OFFSET($C31,-($D$30-1),0,$D$30)),"")

This will give you what you want.

 

[Tetra201]

I pasted the formula in Post #10 directly into Cell D54. It returned a value of '0' (which is correct for the corresponding cells to be averaged in Column C; they were all zero).

OK -- apparently, the formula does work as expected.

When I double clicked the 'x' in the lower right-hand corner of Cell D54 (to apply the formula to the entire Column D), it only went down about 165 cells...

This could happen if you have truly empty cells in column C.

... changed the formatting of values in Column C from integer to real w/ 3 decimal places.

This is really strange, and I have not been able to reproduce it.

 

[TommyC]

The correct formula that achieved my desired result when applied to Cell D54 is this one:

=IF(MOD(ROW(),24)=6,AVERAGE(INDIRECT("C"&ROW()-23&":C"&ROW())),"")

The only remaining mystery is this:
When I copied the formula all the way down [COLOR=blue !important][FONT=inherit !important][COLOR=blue !important][FONT=inherit !important]Column [/FONT][COLOR=blue !important][FONT=inherit !important]D[/FONT][/COLOR][/FONT][/COLOR][/COLOR], it erased all the horizontal lines, as well as the vertical lines bordering [COLOR=blue !important][FONT=inherit !important][COLOR=blue ! important][FONT=inherit ! important]Column [/FONT][COLOR=blue ! important][FONT=inherit ! important]E[/FONT][/COLOR][/FONT][/COLOR][/COLOR].

 

[Tetra201]

Glad you found a solution that worked for you.

When I copied the formula all the way down, it erased all the horizontal lines, as well as the vertical lines bordering.

This can happen if your original cell, which was copied down, is filled with white color -- just apply "No Fill" to it.