I have an excel spreadsheet that has 50,000 rows and all info ins in one
column. I would like to consolidate it to 10,000. When I go to B1 and put in
=average(A1:A2500) it averages fine. However when copying this code and
pasting it in B2 the formula turns into =average(A2:A2501). I would like to
have these numbers increment by 10 instead of 1. Is this possible? Please let
me know!

So the desired formula in B2 would range from a10:a2510? If you could
post your desired results for b2, b3, b4 we can probably figure out the
right way to go.

=AVERAGE(OFFSET($A$1:$A$2500,(ROW()-1)*10,0))

HTH
Kostis Vezerides


Danny wrote:
I have an excel spreadsheet that has 50,000 rows and all info ins in one
column. I would like to consolidate it to 10,000. When I go to B1 and put in
=average(A1:A2500) it averages fine. However when copying this code and
pasting it in B2 the formula turns into =average(A2:A2501). I would like to
have these numbers increment by 10 instead of 1. Is this possible? Please let
me know!

Thank you so much. It worked perfectly!

"vezerid" wrote:

=AVERAGE(OFFSET($A$1:$A$2500,(ROW()-1)*10,0))

HTH
Kostis Vezerides


Danny wrote:
I have an excel spreadsheet that has 50,000 rows and all info ins in one
column. I would like to consolidate it to 10,000. When I go to B1 and put in
=average(A1:A2500) it averages fine. However when copying this code and
pasting it in B2 the formula turns into =average(A2:A2501). I would like to
have these numbers increment by 10 instead of 1. Is this possible? Please let
me know!

In B1 enter
=AVERAGE(INDIRECT("A"&1+10*(ROW()-1)&":A"&2500+10*(ROW()-1)))
and copy down the column.
This will average the average ranges
A1:A2500
A11:A2510
A21:A2520
A31:A2530
A41:A2540
A51:A2550

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Danny" wrote in message
...
I have an excel spreadsheet that has 50,000 rows and all info ins in one
column. I would like to consolidate it to 10,000. When I go to B1 and put
in
=average(A1:A2500) it averages fine. However when copying this code and
pasting it in B2 the formula turns into =average(A2:A2501). I would like
to
have these numbers increment by 10 instead of 1. Is this possible? Please
let
me know!

Try this non-volatile:

=AVERAGE(INDEX(A:A,10*ROWS(1:1)-9):INDEX(A:A,10*ROWS($1:1)+2490))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Danny" wrote in message
...
I have an excel spreadsheet that has 50,000 rows and all info ins in one
column. I would like to consolidate it to 10,000. When I go to B1 and put
in
=average(A1:A2500) it averages fine. However when copying this code and
pasting it in B2 the formula turns into =average(A2:A2501). I would like
to
have these numbers increment by 10 instead of 1. Is this possible? Please
let
me know!

Hi RD

=AVERAGE(INDEX(A:A,10*ROWS(1:1)-9):INDEX(A:A,10*ROWS($1:1)+2490))

Much nicer solution but I think you may be missing one $ sign

=AVERAGE(INDEX(A:A,10*ROWS($1:1)-9):INDEX(A:A,10*ROWS($1:1)+2490))


--
Regards

Roger Govier


"RagDyer" wrote in message
...
Try this non-volatile:

=AVERAGE(INDEX(A:A,10*ROWS(1:1)-9):INDEX(A:A,10*ROWS($1:1)+2490))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Danny" wrote in message
...
I have an excel spreadsheet that has 50,000 rows and all info ins in
one
column. I would like to consolidate it to 10,000. When I go to B1 and
put in
=average(A1:A2500) it averages fine. However when copying this code
and
pasting it in B2 the formula turns into =average(A2:A2501). I would
like to
have these numbers increment by 10 instead of 1. Is this possible?
Please let
me know!

That seems to always be my problem ... losing dollars!<bg

Thanks Roger.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Govier" wrote in message
...
Hi RD

=AVERAGE(INDEX(A:A,10*ROWS(1:1)-9):INDEX(A:A,10*ROWS($1:1)+2490))

Much nicer solution but I think you may be missing one $ sign

=AVERAGE(INDEX(A:A,10*ROWS($1:1)-9):INDEX(A:A,10*ROWS($1:1)+2490))


--
Regards

Roger Govier


"RagDyer" wrote in message
...
Try this non-volatile:

=AVERAGE(INDEX(A:A,10*ROWS(1:1)-9):INDEX(A:A,10*ROWS($1:1)+2490))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Danny" wrote in message
...
I have an excel spreadsheet that has 50,000 rows and all info ins in one
column. I would like to consolidate it to 10,000. When I go to B1 and
put in
=average(A1:A2500) it averages fine. However when copying this code and
pasting it in B2 the formula turns into =average(A2:A2501). I would like
to
have these numbers increment by 10 instead of 1. Is this possible?
Please let
me know!