How do I copy a formula with the cell increasing by increments
 

In regards to what Jaxboo wrote below:

To start RagDyer, you were correct on Jaxboo question, but now I have more
difficult problem.

I would like to take a simple Sum formula such as Sum(b1:e6) and copy it
into
the next cell in an increment so the next cell (below) will read
sum(b7:e12)

How would you answer the above?




"RagDyer" wrote:

A non-volatile approach:

=SUM(INDEX(B:B,6*ROWS($1:1)-5):INDEX(B:B,6*ROWS($1:1)))

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Elkar" wrote in message
...
You can use the INDIRECT and ROW functions to do this. For example,
assuming
your first formula will be placed in Row 1, you'd use:

=SUM(INDIRECT("B"&ROW()*6-5&":B"&ROW()*6))

HTH,
Elkar


"Jaxboo" wrote:

I would like to take a simple Sum formula such as Sum(b1:b6) and copy it
into
the next cell in an increment so the next cell (below) will read
sum(b7:b12)

How would I do this? I have a large number of cells I will be doing this
with and really do not want to have to go back and forth between
worksheets
to select the cells.

How do I copy a formula with the cell increasing by increments
 

Assuming you are starting in row 1;

1 sum(b1:e6)
2 sum(b7:e12)

can be created easily by using indirect, and calculating the row numbers
based on current row number, e.g.

=indirect("sum(b" & ((row(a1)-1)*6)+1 & ":e" & (row(a1)*6)& ")"
entered into row a, so when you drag it down it should update a1 to a2 to a3
etc.

aircode, but I believe that's it. If not, you may want to post followup
questions to microsoft.public.excel.worksheet.functions

HTH,
Keith

"dgb82" wrote in message
...
In regards to what Jaxboo wrote below:

To start RagDyer, you were correct on Jaxboo question, but now I have more
difficult problem.

I would like to take a simple Sum formula such as Sum(b1:e6) and copy it
into
the next cell in an increment so the next cell (below) will read
sum(b7:e12)

How would you answer the above?




"RagDyer" wrote:

A non-volatile approach:

=SUM(INDEX(B:B,6*ROWS($1:1)-5):INDEX(B:B,6*ROWS($1:1)))

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Elkar" wrote in message
...
You can use the INDIRECT and ROW functions to do this. For example,
assuming
your first formula will be placed in Row 1, you'd use:

=SUM(INDIRECT("B"&ROW()*6-5&":B"&ROW()*6))

HTH,
Elkar


"Jaxboo" wrote:

I would like to take a simple Sum formula such as Sum(b1:b6) and copy
it
into
the next cell in an increment so the next cell (below) will read
sum(b7:b12)

How would I do this? I have a large number of cells I will be doing
this
with and really do not want to have to go back and forth between
worksheets
to select the cells.