I posted this yesterday but when I pasted everyones versions of their formula
nothing worked. Here is what I have tried:
=AVERAGE(OFFSET($B$1,24*(ROW()-1),0,24,1)). If I want 24 rows in a column
do what do I put in the ROW ( ) parens. Is this right? See below for
explanation of issue




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.

Assuming you want your averages in column B then
1. Enter your normal average formual in B1
1. Enter 24 in G1
2. put this in B2
=AVERAGE(OFFSET(INDIRECT("A"&($G$1*(ROW()-1))),$G$1,0,1,1))

and copy down.

"Andrew" wrote:

I posted this yesterday but when I pasted everyones versions of their formula
nothing worked. Here is what I have tried:
=AVERAGE(OFFSET($B$1,24*(ROW()-1),0,24,1)). If I want 24 rows in a column
do what do I put in the ROW ( ) parens. Is this right? See below for
explanation of issue




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 posted this yesterday but when I pasted everyones versions of their formula
nothing worked. Here is what I have tried:
=AVERAGE(OFFSET($B$1,24*(ROW()-1),0,24,1)). If I want 24 rows in a column
do what do I put in the ROW ( ) parens. Is this right? See below for
explanation of issue

Nothing goes in the row() parens. ROW() returns the row number where the
formula is pasted.

So in C1, the formula would evaluate as

=AVERAGE(OFFSET($B$1,24*(1-1),0,24,1))
=AVERAGE(OFFSET($B$1,0,0,24,1))
=AVERAGE(B1:B24)

In C2 it would evaluate like so:
=AVERAGE(OFFSET($B$1,24*(2-1),0,24,1))
=AVERAGE(OFFSET($B$1,24,0,24,1))
=AVERAGE(B25:B49)

What we're doing is moving the reference for the average by 24 rows from
B1 each time, starting with 0 offset (24*row()-1). The 2nd "24" in the
formula says how "high" the range should be, i.e., 24 rows.

Take a look at the OFFSET function in Help for a bit more explanation.

[snipped]