repeating function q

[Andrew]

I have a column of data where I need to select a group of X number of cells
each time and obtain an average. When I copy the formula down it only copies
the formula with one additional space down, but I want it to jump over the
cells that have already been averaged. Example formuala is: Average(B1:B5),
when copied down it goes (B2:B6), but I want (B6:10). Im sure this is easy
but I dont know the shortcut.

 

[Pete_UK]

Put this in your first cell:

=AVERAGE(INDIRECT("B"&ROW(A1)*5-4&":B"&ROW(A1)*5))

then copy down. If you have a different increment than 5, then change
both 5s to what it is (x) and the 4 to x-1.

Hope this helps.

Pete

 

[smartin]

I have a column of data where I need to select a group of X number of cells
each time and obtain an average. When I copy the formula down it only copies
the formula with one additional space down, but I want it to jump over the
cells that have already been averaged. Example formuala is: Average(B1:B5),
when copied down it goes (B2:B6), but I want (B6:10). Im sure this is easy
but I dont know the shortcut.

A couple ideas.

This will give the averages in consecutive rows. If you want something
other than groups of 5, change the two 5's in the formula:

=AVERAGE(OFFSET($B$1,5*(ROW()-1),0,5,1))


This will give averages on every 5th row. Again, change the 5's (two of
them) to something else as needed.

=IF(MOD(ROW(),5)=0,AVERAGE(B1:B5),"")

 

[Andrew]

Im working in down in columns

A couple ideas.

This will give the averages in consecutive rows. If you want something
other than groups of 5, change the two 5's in the formula:

=AVERAGE(OFFSET($B$1,5*(ROW()-1),0,5,1))


This will give averages on every 5th row. Again, change the 5's (two of
them) to something else as needed.

=IF(MOD(ROW(),5)=0,AVERAGE(B1:B5),"")

 

[Andrew]

This is getting closer but im working in columns. Also I am trying to skip
over ten cells not five.

 

[smartin]

Hi Andrew,

Both of my formulae (and Chip's and Pete's as well) are intended to be
placed in a column, and will average a column of values.

You mentioned averaging 10 values, not 5, so simply replace the "5"s in
my formulae to "10"s, as I mentioned.