Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 month total
Hi,
I have searched to message boards and can't quite find what i'm looking for. I'm trying to keep a 12 month running tally (When I enter the information for a new month, the oldest month drops out keeping only 12 months of information) I currently have a worksheet with 2 matrixes of information. A5-A16 holds the months of 2008 (Jan, Feb,...) and E5-E16 holds the monthly totals. A22-A33 hold the months of 2009 and E22-E33 holds the montly totals. In a cell (F39 on my worksheet) I'd like to keep the 12 month rolling total such that when I enter the data for June, the total in F39 will update for the period of Jun09-Jul08. Thanks for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 month total
Hi,
If the data were contiguous one would use something like =SUM(OFFSET(C1,COUNT(C:C)-1,,-12)) in your case try =SUM(E22:E33,OFFSET($E$5,COUNT(E22:E33),,12-COUNT(E22:E33))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "MH" wrote: Hi, I have searched to message boards and can't quite find what i'm looking for. I'm trying to keep a 12 month running tally (When I enter the information for a new month, the oldest month drops out keeping only 12 months of information) I currently have a worksheet with 2 matrixes of information. A5-A16 holds the months of 2008 (Jan, Feb,...) and E5-E16 holds the monthly totals. A22-A33 hold the months of 2009 and E22-E33 holds the montly totals. In a cell (F39 on my worksheet) I'd like to keep the 12 month rolling total such that when I enter the data for June, the total in F39 will update for the period of Jun09-Jul08. Thanks for any help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 month total
On Mon, 29 Jun 2009 09:48:01 -0700, MH
wrote: Hi, I have searched to message boards and can't quite find what i'm looking for. I'm trying to keep a 12 month running tally (When I enter the information for a new month, the oldest month drops out keeping only 12 months of information) I currently have a worksheet with 2 matrixes of information. A5-A16 holds the months of 2008 (Jan, Feb,...) and E5-E16 holds the monthly totals. A22-A33 hold the months of 2009 and E22-E33 holds the montly totals. In a cell (F39 on my worksheet) I'd like to keep the 12 month rolling total such that when I enter the data for June, the total in F39 will update for the period of Jun09-Jul08. Thanks for any help. Assuming that the cells E17-E21 are blanks, try the following formula: =SUMPRODUCT((E5:E33)*(ROW(E5:E33)=LARGE(ROW(E5:E3 3)*((E5:E33)<""),12))) Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 month total
On Mon, 29 Jun 2009 10:18:01 -0700, Shane Devenshire
wrote: Hi, If the data were contiguous one would use something like =SUM(OFFSET(C1,COUNT(C:C)-1,,-12)) in your case try =SUM(E22:E33,OFFSET($E$5,COUNT(E22:E33),,12-COUNT(E22:E33))) This formula does unfortunately not work when December data comes. When there is data in all of E22-E33 there is a #REF! error generated. Lars-Åke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 month total
On Mon, 29 Jun 2009 17:23:36 GMT, Lars-Åke Aspelin
wrote: On Mon, 29 Jun 2009 09:48:01 -0700, MH wrote: Hi, I have searched to message boards and can't quite find what i'm looking for. I'm trying to keep a 12 month running tally (When I enter the information for a new month, the oldest month drops out keeping only 12 months of information) I currently have a worksheet with 2 matrixes of information. A5-A16 holds the months of 2008 (Jan, Feb,...) and E5-E16 holds the monthly totals. A22-A33 hold the months of 2009 and E22-E33 holds the montly totals. In a cell (F39 on my worksheet) I'd like to keep the 12 month rolling total such that when I enter the data for June, the total in F39 will update for the period of Jun09-Jul08. Thanks for any help. Assuming that the cells E17-E21 are blanks, try the following formula: =SUMPRODUCT((E5:E33)*(ROW(E5:E33)=LARGE(ROW(E5:E 33)*((E5:E33)<""),12))) Hope this helps / Lars-Åke If you want to allow any data in E17-E21 try this modified formula: =SUM(E22:E33)+IF(COUNT(E22:E33)<12,SUMPRODUCT((E5: E16)*(ROW(E5:E16)=LARGE(ROW(E5:E16),12-COUNT(E22:E33))))) Hope this helps / Lars-Åke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 month total
E5-E16 holds the monthly totals.
E22-E33 holds the montly totals. So what's in the cells between those 2 ranges? What's in E17:E21? Are there any numeric values in those cells? -- Biff Microsoft Excel MVP "MH" wrote in message ... Hi, I have searched to message boards and can't quite find what i'm looking for. I'm trying to keep a 12 month running tally (When I enter the information for a new month, the oldest month drops out keeping only 12 months of information) I currently have a worksheet with 2 matrixes of information. A5-A16 holds the months of 2008 (Jan, Feb,...) and E5-E16 holds the monthly totals. A22-A33 hold the months of 2009 and E22-E33 holds the montly totals. In a cell (F39 on my worksheet) I'd like to keep the 12 month rolling total such that when I enter the data for June, the total in F39 will update for the period of Jun09-Jul08. Thanks for any help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 month total
=SUM(E22:E33,OFFSET($E$5,COUNT(E22:E33),,12-COUNT(E22:E33)))
When the range E22:E33 is full of numbers that formula will return a #REF! error. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, If the data were contiguous one would use something like =SUM(OFFSET(C1,COUNT(C:C)-1,,-12)) in your case try =SUM(E22:E33,OFFSET($E$5,COUNT(E22:E33),,12-COUNT(E22:E33))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "MH" wrote: Hi, I have searched to message boards and can't quite find what i'm looking for. I'm trying to keep a 12 month running tally (When I enter the information for a new month, the oldest month drops out keeping only 12 months of information) I currently have a worksheet with 2 matrixes of information. A5-A16 holds the months of 2008 (Jan, Feb,...) and E5-E16 holds the monthly totals. A22-A33 hold the months of 2009 and E22-E33 holds the montly totals. In a cell (F39 on my worksheet) I'd like to keep the 12 month rolling total such that when I enter the data for June, the total in F39 will update for the period of Jun09-Jul08. Thanks for any help. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling 12 month total
This will work *only* if E17 to E21 are populated with TEXT, or they're
empty: =SUM(INDEX(E5:E33,LARGE(INDEX(ROW(1:29)*(ISNUMBER( E5:E33)),),12)):E33) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "MH" wrote in message ... Hi, I have searched to message boards and can't quite find what i'm looking for. I'm trying to keep a 12 month running tally (When I enter the information for a new month, the oldest month drops out keeping only 12 months of information) I currently have a worksheet with 2 matrixes of information. A5-A16 holds the months of 2008 (Jan, Feb,...) and E5-E16 holds the monthly totals. A22-A33 hold the months of 2009 and E22-E33 holds the montly totals. In a cell (F39 on my worksheet) I'd like to keep the 12 month rolling total such that when I enter the data for June, the total in F39 will update for the period of Jun09-Jul08. Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rolling 3 month tracking | Excel Discussion (Misc queries) | |||
Rolling 13 Month Charts | Charts and Charting in Excel | |||
12 month Rolling Total | Excel Worksheet Functions | |||
How do I set up 12-month rolling month cells in excel? | Excel Discussion (Misc queries) | |||
3 month rolling chart | Charts and Charting in Excel |