average

D

Danny

I have a very longer list (column) of numbers for two years, by day. Each
day contains 12 numeric entries. I need to determine the daily average of
these numbers. While an average formula can simply be used, it has to be
re-entered for every 12 rows 730 times (365x2). Is there a more efficent way
to write the average formula and then copy? Basically the first 12 numbers
are averaged, then the next 12 down the column, then the next 12, etc.

Thanks in advance,

Danny
 
H

Harlan Grove

Danny wrote...
I have a very longer list (column) of numbers for two years, by day. Each
day contains 12 numeric entries. I need to determine the daily average of
these numbers. While an average formula can simply be used, it has to be
re-entered for every 12 rows 730 times (365x2). Is there a more efficent way
to write the average formula and then copy? Basically the first 12 numbers
are averaged, then the next 12 down the column, then the next 12, etc.

If your data were in a range named Data, and the topmost average were
to appear in cell X99 with the subsequent averages below it, you could
try

X99:
=AVERAGE(INDEX(Data,12*ROWS(X$99:X99)-11):INDEX(Data,12*ROWS(X$99:X99)))

and fill X99 down into X100:X828.
 
D

Danny

Harlan,

Thanks for the help. Unfortunatley I double posted (slightly different) do
to a power failure causing me to think the original post did not get out.

I tried Biff's suggestion of AVERAGE(OFFSET(A$1,(ROWS($1:1)-1)*12,,12)) and
it worked.

I will try your solution and get back.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top