Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy a formula with the cell increasing by increments? | Excel Discussion (Misc queries) | |||
Copy formula down in rows but change column increments | Excel Discussion (Misc queries) | |||
Copy a formula to the right increasing the rows from another works | Excel Worksheet Functions | |||
How do I copy a formula with increasing worksheet numbers | Excel Discussion (Misc queries) | |||
How do I sum a formula and paste it in 7 cell increments? | Excel Discussion (Misc queries) |