How to take average of every twelfth cell in a column

U

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?
 
J

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...
 
L

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?
Click to expand...


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

T. Valko

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

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

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))
 
Top