rolling weeks formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

@Homeonthecouch - 14 Dec 2006 14:14 GMT

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

Reply to this Message

Roger Govier - 14 Dec 2006 14:31 GMT

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

Signature

Regards

Roger Govier

> Hi all,
> I have a sheet that has data in cells A1-A10 and will continue on
[quoted text clipped - 7 lines]
>
> Andrew

Reply to this Message

@Homeonthecouch - 14 Dec 2006 15:01 GMT

Thanks Roger

That works just fine.

Andrew

> Hi Andrew
>
[quoted text clipped - 13 lines]
>>
>> Andrew

Reply to this Message

Don Guillett - 14 Dec 2006 15:01 GMT

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)

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi all,
> I have a sheet that has data in cells A1-A10 and will continue on through
[quoted text clipped - 7 lines]
>
> Andrew

Reply to this Message