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
 
R

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
 
D

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

Roger Govier said:
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
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top