How to take average of every twelfth cell in a column

[uiowa]

Hi,

I am struggling to figure out how to take average of every twelfth cell in a
column containing a very
list of numbers.

Can anyone help me?

 

[JasonS]

One thing you can do is write out an AVERAGE function and inside the
paranthesis you would have to select your desired cells by holding down the
Ctrl button...

now, depending on the other data on that wrksht, you may be able to write an
IF function that will average out the correct functions... but this would be
impossible for me to know how you would construct this without seeing the
data...

 

[Lars-Åke Aspelin]

Hi,

I am struggling to figure out how to take average of every twelfth cell in a
column containing a very
list of numbers.

Can anyone help me?

Try this formula:

=SUMPRODUCT(OFFSET(A1,0,0,x*12),--(MOD(ROW(OFFSET(A1,0,0,x*12)),12)=1))/x

where you should replace "x" in three places with the number of cells
that you want to take the average of.
Replace A1 with location of the first number.

Hope this helps / Lars-Åke

 

[T. Valko]

How about telling us what the first few cell addresses are so we can see the
pattern.

 

[dhstein]

If F4 contains the value "12" then you can use this Array formula:

=(SUM((IF(MOD(ROW(A1:A40),$F$4)=0,1,0)*(A1:A40))))/(SUM((IF(MOD(ROW(A1:A40),$F$4)=0,1,0)*1)))


Array formulas are entered by typing the formula and instead of hitting
"Enter" key - hit "CTRL + SHIFT + ENTER" simultaneously. You can learn about
array formulas here:

http://www.cpearson.com/excel/ArrayFormulas.aspx

 

[T. Valko]

=(SUM((IF(MOD(ROW(A1:A40),$F$4)=0,1,0)*(A1:A40))))/(SUM((IF(MOD(ROW(A1:A40),$F$4)=0,1,0)*1)))

You can reduce that to:

Array entered.

=AVERAGE(IF(MOD(ROW(A1:A40),F4)=0,A1:A40))