Filling series with averge function of cell blocks

[Prevost]

Hi There. I need to take the average of cells in column A, but I need to take them for rows 1-5, 6-10, 11-15....etc. Does anyone know how I can quickly do this with a fill? Or a work around for this? I tried manually typing in for a few cells AVERAGE(A1:A5) then AVERAGE(A6:A10) and so on but it doesn't seem to work. Does anyone have any ideas? Thanks!

 

[jim may]

Excel 2012
	A	B	C
1	439		242
2	306		254.8
3	176		361.2
4	107		400.4
5	182
6	191
7	141
8	285
9	458
10	199
11	236
12	261
13	483
14	335
15	491
16	290
17	490
18	478
19	482
20	262
Sheet1

Cell Formulas
Range	Formula
C1	=AVERAGE(OFFSET($A$1,(5*ROW())-5,0,5,1))
C2	=AVERAGE(OFFSET($A$1,(5*ROW())-5,0,5,1))
C3	=AVERAGE(OFFSET($A$1,(5*ROW())-5,0,5,1))
C4	=AVERAGE(OFFSET($A$1,(5*ROW())-5,0,5,1))

 

[Prevost]

Thank you. Can you please explain the formula? This is what I need, I just would like a better understanding and how to make it work for different rows and columns...Thanks!

 

[jim may]

The offset() function has 5 arguments:
1) Your Base Cell (reference)
2) The number of rows you wish to move (Either up(-) or down(+)
3) The number of columns you wish to move (Either left (-) or right(+)
Optional:
4) The row size (#) of the range of cells you wish to capture
5) The column size (#) of the range of cells you wish to capture

Hope that helps, also see the HELP info on the OFFSET() function...

 

[Prevost]

Perfect, thank you! I was able to get it to work for multiple sheets.

 

[jim may]

Good and Glad for you. Thanks for letting us know. Jim

 

[undercookdpork]

Hello, adding onto what prevost asked, what if I want to average starting from arbitrary row? For example, if the A column extended out to 20000 and I wanted to start from row 13771 averaging the first 50 rows, in 900 row increment. So the first average would be E13771:E13820 and then next average would be E14671:E14720 and so on. Would the formula be "=AVERAGE(OFFSET($A$13771,(900+ROW()+0,0,50,1))"? I've tried it and it didn't work. Any help would be appreciated!

 

[jim may]

[TABLE="width: 792"]
<tbody>[TR]
[TD]Into a column of your choice (Any Column Other Than Column E) but definitely on Row 13771 enter:

=IF(AND(MOD(ROW()+900-13771,900)=0,ROW()-ROW()<=50),AVERAGE(OFFSET($E13771,0,0,50,1)),"")

and Copy Down to Row 20,000
[/TD]
[/TR]
</tbody>[/TABLE]

 

[undercookdpork]

Thanks!! The formula did work and I can follow it, but I have to ask, is there a way in which I can put all averages in a series of subsequent rows? so F13771 would have average(e13771:e13820) and F13772 would have average(e14671:e14720) and so on. I've been trying to find a way to automated addition to the index of the range of averages by 900, but have been failing to do so.

 

[jim may]

In Cell F13771 enter

=AVERAGE(OFFSET($E$13771,900*(ROW()-13771),0,50,1))

and Copy Down several rows...