rolling weeks formula

[@Homeonthecouch]

Hi all,
I have a sheet that has data in cells A1-A10 and will continue on through
A11, A12 etc as the weeks go on and data is added.
I want to make a sum of lets say A2-A6 this week, but next week A3-A7 and so
on, like a rolling week data.
Is there a way of doing this simple sum without having to SUM(A2-A6) and
editing it the following week to SUM(A3-A7)?

Any help is appreciated

Andrew

 

[Roger Govier]

Hi Andrew

One way
Using cell B1 to hold the starting week number
=SUM(INDEX(A:A,B1):INDEX(A:A,B1+3))
Just alter the value in B1 to give a different range

 

[Don Guillett]

This may not be what you want but

try this (correct for word wrap) where col a is dates and col b is data to
sum and c1 is the weeknumber such as 1 or 7

=SUMPRODUCT(--(INT(($A$8:$A$366-DATE(YEAR($A$8:$A$366),1,1)-WEEKDAY($A$8:$A$366))/7)+2=C1),$B$8:$B$366)

 

[@Homeonthecouch]

Thanks Roger

That works just fine.

Andrew

Hi Andrew

One way
Using cell B1 to hold the starting week number
=SUM(INDEX(A:A,B1):INDEX(A:A,B1+3))
Just alter the value in B1 to give a different range

 

[Guest]

Sorry, I made an error in the formula. You would subtract 6 not 7.
=SUM(INDIRECT("C"&B11-6&":C"&B11))