Rolling 12 week calculations

[Guest]

I am trying to establish a 12 week rolling sum of cells on multiple
worksheets (1st qtr, 2nd, 3rd, & 4th) that will display on a Summary
worksheet. I am thinking this , On the Summary sheet I have a =today cell,
which will correspond to a column heading. I want it to calculate the past 12
weeks from the date "Today", which may span 2 worksheets.
My brain is not working right at the moment, so any ideas would be
appreciated. If more info is required, please reply and I will fill in the
blanks. Thanks!!

 

[Bob Phillips]

Maybe something like

=SUMPRODUCT(--(Qtr1!A2:A20>B2-84),--(Qtr1!A2:A20<=B2),--(Qtr1!B2:B20))+
SUMPRODUCT(--(Qtr2!A2:A20>B2-84),--(Qtr2!A2:A20<=B2),--(Qtr2!B2:B20))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[bda75]

You may try using dynamic names:

Solution 1
1. One a row, fill in the dates ( 1st of each month in date format).
2. Name the area 'dates'
3. In a cell named 'reporting_date', enter the date of reporting ( 1st
of the current month in date format).
4. Create the named range 'rolling_12mth' with the following formula:
=OFFSET(INDEX(dates,1,MATCH(Sheet1!reporting_date,dates)),0,0,1,-12)

Solution 2
1, 2, 3 same as sol 1
4 . create a static named range where all your mnth data reside: ex
mydata
This should be the same dimension as the range 'dates' but probably
a few rows below it
5. the for a year to date sum:
=SUMPRODUCT((mydata)*(YEAR(dates)=YEAR(reporting_date)))

example here: http://www.box.net/public/v6627zsg91