Incremental Sequences

[squidnum1]

Hello everyone,

I am trying to figure out if there is a way to continue this as a sequence without using a macro:

=A5
=A10
=A15
=A20
=A25
and so on...

And if there is a way... would it also work with this:
=SUM(A5:A10)
=SUM(A11:15)
=SUM(A16:A20)
=SUM(A21:A25)
and so on...

Any help would be greatly appreciated!

Thanks so much!

 

[MrKowz]

Welcome to the forums!

Try:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">35</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">15</td><td style="text-align: right;;">85</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">20</td><td style="text-align: right;;">110</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=INDIRECT(<font color="Blue">"A" &ROW(<font color="Red"></font>)*5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=SUM(<font color="Blue">INDIRECT(<font color="Red">"A" & ROW(<font color="Green"></font>)*5 &":A" & (<font color="Green">ROW(<font color="Purple"></font>)*5</font>)+4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

Hello everyone,

I am trying to figure out if there is a way to continue this as a sequence without using a macro:

=A5
=A10
=A15
=A20
=A25
and so on...

And if there is a way... would it also work with this:
=SUM(A5:A10)
=SUM(A11:15)
=SUM(A16:A20)
=SUM(A21:A25)
and so on...

Any help would be greatly appreciated!

Thanks so much!

Ok, the first part is easy enough...

Let's assume you want the results starting in cell D2 then copied down the column.

Enter this formula in D2 then copy down as needed:

=INDEX(A:A,ROWS(D$2:D2)*5)

For your 2nd question...

There's one small "glitch"...

=SUM(A5:A10) = 6 cells
=SUM(A11:15) = 5 cells
=SUM(A16:A20) = 5 cells
=SUM(A21:A25) = 5 cells

Is that the correct pattern?

 

[squidnum1]

My bad... the first one should have said
=SUM(A6:A10)
and increasing by 5

 

[T. Valko]

Welcome to the forums!

Try:

Excel 2003

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C1</TH><TD style="TEXT-ALIGN: left">=INDIRECT("A" &ROW()*5)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D1</TH><TD style="TEXT-ALIGN: left">=SUM(INDIRECT("A" & ROW()*5 &":A" & (ROW()*5)+4))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

So what happens if they don't want the formulas entered on row 1?

ROW() is asking for problems!

ROWS(...) rules!

 

[MrKowz]

Then I suppose you could use:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">5</td><td style="text-align: right;;">35</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">10</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">85</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">20</td><td style="text-align: right;;">110</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C13</th><td style="text-align:left">=INDIRECT(<font color="Blue">"A" &ROW(<font color="Red">A1</font>)*5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D13</th><td style="text-align:left">=SUM(<font color="Blue">INDIRECT(<font color="Red">"A" & ROW(<font color="Green">A1</font>)*5 &":A" & (<font color="Green">ROW(<font color="Purple">A1</font>)*5</font>)+4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

My bad... the first one should have said
=SUM(A6:A10)
and increasing by 5

Ok, try this...

Assume you want the results starting in cell D2 and then copied down the column.

Entered in D2 and copied down as neeeded:

=SUM(OFFSET(A$6,ROWS(D$2:D2)*5-5,,5))

Here's how it breaks out:

• D2: =SUM(A6:A10)
• D3: =SUM(A11:A15)
• D4: =SUM(A16:A20)
• D5: =SUM(A21:A25)
• etc
• etc

 

[T. Valko]

Then I suppose you could use:

Excel 2003

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C13</TH><TD style="TEXT-ALIGN: left">=INDIRECT("A" &ROW(A1)*5)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D13</TH><TD style="TEXT-ALIGN: left">=SUM(INDIRECT("A" & ROW(A1)*5 &":A" & (ROW(A1)*5)+4))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Until you insert a new row 1 then you have problems!

Trust me (or not ), ROWS(...) is the way to go!

ROW(A1) = 1
ROWS(A$1:A1) = 1

If you insert a new row 1 then:

ROW(A2) = 2
ROWS(A$2:A2) = 1

 

[MrKowz]

Good point! Thanks for the tip.

 

[T. Valko]

Good point! Thanks for the tip.

I used to do the same thing until someone set me straight! Now it's automatic.