average
 

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

average
 

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.

average
 

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


"Harlan Grove" wrote:

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.