Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I setup a rolling 13 week spreadsheet?
I need a spreadsheet where I can plug daily sales/expense figures on one
page. Then that info auto loads into a monthly report and then that information populates a rolling 13 week report to provide figures for my resturant. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I setup a rolling 13 week spreadsheet?
This doesn't solve all of your problem but maybe it will be enough to get you
there. We'll stay on one sheet, with just the sales figures and a rolling report. A1:A6 has the dates. B1:B6 has the sales. D and E will be the rolling report. Put a starting date in D1 -- I used 12/31/2006. Format it with MMM-YY so it doesn't show the day. Put the following formula in D2: =EOMONTH(D1,1) this will add a month. Copy this down for your 13 months. In E1 put the following formula: =SUMPRODUCT((MONTH(D1)=MONTH($A$1:$A$6))*(YEAR(D1) =YEAR($A$1:$A$6))*$B$1:$B$6) Copy that down for your 13 months. This will check the month and year in D against the month and year in A and add the qualifying numbers. "cshel" wrote: I need a spreadsheet where I can plug daily sales/expense figures on one page. Then that info auto loads into a monthly report and then that information populates a rolling 13 week report to provide figures for my resturant. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with 12 week rolling average | Excel Worksheet Functions | |||
Rolling 12 week calculations | Excel Worksheet Functions | |||
rolling twelve week trend chart | Charts and Charting in Excel | |||
Create a rolling 12 week graph | Charts and Charting in Excel | |||
Calculation using rolling 4 week amounts | Excel Discussion (Misc queries) |