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.

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


On Sep 4, 11:58*pm, Andrew wrote:
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.

Andrew wrote:
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),"")

Im working in down in columns

"smartin" wrote:

Andrew wrote:
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),"")

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

"Chip Pearson" wrote:

Try a formula like

=SUM(OFFSET($A$1,((ROW(E3)-ROW($E$3))*5),0,5))

Change the reference $A$1 to the first cell of the data and the reference to
E3 and $E$3 to the first cell that contains the formula. Then, copy the
formula down as far as you need to go.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Andrew" wrote in message
...
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.

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.

Andrew wrote:
Im working in down in columns

"smartin" wrote:

Andrew wrote:
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),"")