Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MH MH is offline
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 913
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 913
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 913
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
rolling 3 month tracking grizzly6969 Excel Discussion (Misc queries) 3 June 9th 09 12:06 AM
Rolling 13 Month Charts Aurora Charts and Charting in Excel 1 January 17th 08 04:12 PM
12 month Rolling Total Need Help Excel Worksheet Functions 0 September 22nd 06 03:19 PM
How do I set up 12-month rolling month cells in excel? jbh Excel Discussion (Misc queries) 2 November 30th 05 09:12 PM
3 month rolling chart Shanin Charts and Charting in Excel 4 September 2nd 05 06:40 PM


All times are GMT +1. The time now is 07:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"