average formula?

[excel0003]

I like to average data by 5 rows.

example,
Column:
A1:2
A2:3
A3:4
A4:5
A5:3
A6:8
A7:9
A8:4
A9:2
A10:5
A11:6
.
AN:
I want to know the formula that calculate Averager of A1 to A5, next from A6 to A10, next A11 to a15.......automatically. moving average (increment by 5)

[davesexcel]

is this in one cell??

=AVERAGE(A1:A5)&" "&AVERAGE(A6:A10)&" "&AVERAGE(A11:A15)

[James Hamilton]

In cell B5, enter the formula =AVERAGE(a1:a5)

Then copy this formula to cell B10, B15, etc.

James

"excel0003" wrote:

>
> I like to average data by 5 rows.
>
> example,
> Column:
> A1:2
> A2:3
> A3:4
> A4:5
> A5:3
> A6:8
> A7:9
> A8:4
> A9:2
> A10:5
> A11:6
> .
> AN:
> I want to know the formula that calculate Averager of A1 to A5, next
> from A6 to A10, next A11 to a15.......automatically. moving average
> (increment by 5)
>
>
> --
> excel0003
> ------------------------------------------------------------------------
> excel0003's Profile: http://www.excelforum.com/member.php...o&userid=37914
> View this thread: http://www.excelforum.com/showthread...hreadid=574434
>
>

[excel0003]

is this in one cell??

=AVERAGE(A1:A5)&" "&AVERAGE(A6:A10)&" "&AVERAGE(A11:A15)

I have 1 column but 1000 rows.
i want to get,
1st cell =average a1 to a5
2nd cell=average of a6 to a10

continue to
last cell= average A(1000-5) to a1000.


will appreciate

[RagDyer]

Enter this anywhere, and copy down as needed:

=AVERAGE(INDEX(A:A,5*ROWS($1:1)-4):INDEX(A:A,5*ROWS($1:1)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"excel0003" <[email protected]> wrote
in message news:[email protected]...
>
> davesexcel Wrote:
>> is this in one cell??
>>
>> =AVERAGE(A1:A5)&" "&AVERAGE(A6:A10)&" "&AVERAGE(A11:A15)
>
>
> I have 1 column but 1000 rows.
> i want to get,
> 1st cell =average a1 to a5
> 2nd cell=average of a6 to a10
>
> continue to
> last cell= average A(1000-5) to a1000.
>
>
> will appreciate
>
>
> --
> excel0003
> ------------------------------------------------------------------------
> excel0003's Profile:
> http://www.excelforum.com/member.php...o&userid=37914
> View this thread: http://www.excelforum.com/showthread...hreadid=574434
>

[excel0003]

i want to copy paste to b1, b2, b3 , not b5, b10,.. i will appreciate

In cell B5, enter the formula =AVERAGE(a1:a5)

Then copy this formula to cell B10, B15, etc.
i want to copy paste to b1, b2, b3 , not b5, b10,.. i will appreciate

James

"excel0003" wrote:

>
> I like to average data by 5 rows.
>
> example,
> Column:
> A1:2
> A2:3
> A3:4
> A4:5
> A5:3
> A6:8
> A7:9
> A8:4
> A9:2
> A10:5
> A11:6
> .
> AN:
> I want to know the formula that calculate Averager of A1 to A5, next
> from A6 to A10, next A11 to a15.......automatically. moving average
> (increment by 5)
>
>
> --
> excel0003
> ------------------------------------------------------------------------
> excel0003's Profile: http://www.excelforum.com/member.php...o&userid=37914
> View this thread: http://www.excelforum.com/showthread...hreadid=574434
>
>

[excel0003]

RD,

it works
thank you very much.


pk

Enter this anywhere, and copy down as needed:

=AVERAGE(INDEX(A:A,5*ROWS($1:1)-4):INDEX(A:A,5*ROWS($1:1)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"excel0003" <[email protected]> wrote
in message news:[email protected]...
>
> davesexcel Wrote:
>> is this in one cell??
>>
>> =AVERAGE(A1:A5)&" "&AVERAGE(A6:A10)&" "&AVERAGE(A11:A15)
>
>
> I have 1 column but 1000 rows.
> i want to get,
> 1st cell =average a1 to a5
> 2nd cell=average of a6 to a10
>
> continue to
> last cell= average A(1000-5) to a1000.
>
>
> will appreciate
>
>
> --
> excel0003
> ------------------------------------------------------------------------
> excel0003's Profile:
> http://www.excelforum.com/member.php...o&userid=37914
> View this thread: http://www.excelforum.com/showthread...hreadid=574434
>

[T Kirtley]

I think the jist is that you want consecutive rows with averages of
non-overlapping 5-row ranges; a total of 200 rows of averages to summarize
1000 rows of data.

James' approach certainly will work, but if you want want to avoid a lot of
copying and pasting try the following formula:

=AVERAGE(OFFSET(A$1,(ROW(C1)-ROW(C$1))*5,0,5,1))

This is assuming the data is from A1:A1000 and the averages are listed from
C1 down to C200

Hope that helps,

TK

"excel0003" wrote:

>
> davesexcel Wrote:
> > is this in one cell??
> >
> > =AVERAGE(A1:A5)&" "&AVERAGE(A6:A10)&" "&AVERAGE(A11:A15)
>
>
> I have 1 column but 1000 rows.
> i want to get,
> 1st cell =average a1 to a5
> 2nd cell=average of a6 to a10
>
> continue to
> last cell= average A(1000-5) to a1000.
>
>
> will appreciate
>
>
> --
> excel0003
> ------------------------------------------------------------------------
> excel0003's Profile: http://www.excelforum.com/member.php...o&userid=37914
> View this thread: http://www.excelforum.com/showthread...hreadid=574434
>
>