Cumulative Increment by 7 (Weekly Drag Down)

[kggunes]

Hello,

I could not find suitable excel function to sum cells cumulatively with an increasing option by 7. To put on an example;

Sheet 1
A
1
2
3
.
.
34
35
.
.

I want to do this via drag down option so on Sheet 2 when I drag down cells it should make a cumulative sum which is adding 7 by 7.

Sheet 2
A
7
14
28
.
.

Is that possible to do it w/o using VBA in Excel 2007-2010? I encountered over the internet; some raw function nested in an index function, but not quite clear..

Thanks for the help in advance!

 

[GlennUK]

Drag with your right mouse button, and when the context menu appears choose series... and enter a 7 for the step.

 

[kggunes]

Drag with your right mouse button, and when the context menu appears choose series... and enter a 7 for the step.

Hi Glenn,

I am not sure if because I am dividing the sum's in my cell; series option is not active(grey).

Formula : =SUM(Sheet1!$A$1:AXX)/SUM(Sheet1!$B1$:BXX)

so simply I want to increase range of A and B by 7 via dragging..

 

[GlennUK]

Sorry, I misunderstood the question.

This may do it:

=SUM(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROWS($A$1:$A1)))/SUM(Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROWS($A$1:$A1)))

change the ROWS referenced range to point to the row where the formula is first used, then copy down.

 

[kggunes]

I'm sorry but I can not increase the cell range by 7 with "rows" function..

=SUM(Sheet1!$A$1:INDEX(Sheet1!$A:$A;ROWS($A$1:$A7)))
=SUM(Sheet1!$A$1:INDEX(Sheet1!$A:$A;ROWS($A$1:$A14)))
=SUM(Sheet1!$A$1:INDEX(Sheet1!$A:$A;ROWS($A$1:$A28)))
=SUM(Sheet1!$A$1:INDEX(Sheet1!$A:$A;ROWS($A$1:$A35)))

it should look like this when I drag down, but i don't know how to do it?

 

[GlennUK]

That's not what should be happening, but I made a mistake in my formula:

=SUM(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROWS($A$1:$A1)*7))/SUM(Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROWS($A$1:$A1)*7))

 

[Jonmo1]

Try

=SUM(Sheet1!A$1:INDEX(Sheet1!A:A,ROWS(A$1:A1)*7))

To add the Second Range and devide...

=SUM(Sheet1!A$1:INDEX(Sheet1!A:A,ROWS(A$1:A1)*7))/SUM(Sheet1!B$1:INDEX(Sheet1!B:B,ROWS(A$1:A1)*7))

 

[kggunes]

thanks all working!