Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 week calculations
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 week calculations
Maybe something like
=SUMPRODUCT(--(Qtr1!A2:A20B2-84),--(Qtr1!A2:A20<=B2),--(Qtr1!B2:B20))+ SUMPRODUCT(--(Qtr2!A2:A20B2-84),--(Qtr2!A2:A20<=B2),--(Qtr2!B2:B20)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Throwme A Frigginbone" wrote in message ... 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!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 week calculations
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_d ate))) example he http://www.box.net/public/v6627zsg91 Bob Phillips wrote: Maybe something like =SUMPRODUCT(--(Qtr1!A2:A20B2-84),--(Qtr1!A2:A20<=B2),--(Qtr1!B2:B20))+ SUMPRODUCT(--(Qtr2!A2:A20B2-84),--(Qtr2!A2:A20<=B2),--(Qtr2!B2:B20)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Throwme A Frigginbone" wrote in message ... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a rolling 12 week graph | Charts and Charting in Excel | |||
Macro to copy cells to rows below | Excel Discussion (Misc queries) | |||
Macro to insert copied cells | Excel Discussion (Misc queries) | |||
Calculation using rolling 4 week amounts | Excel Discussion (Misc queries) | |||
user defined function | Excel Worksheet Functions |