Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rolling average | Excel Worksheet Functions | |||
in a pivot table, can the average include blank entries? | Excel Discussion (Misc queries) | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions | |||
Average the Last Five Cells in a Column | Excel Worksheet Functions |