Rolling 12 month total

  • Thread starter Thread starter MH
  • Start date Start date
M

MH

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.
 
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)))
 
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.
Click to expand...


Assuming that the cells E17-E21 are blanks, try the following formula:

=SUMPRODUCT((E5:E33)*(ROW(E5:E33)>=LARGE(ROW(E5:E33)*((E5:E33)<>""),12)))

Hope this helps / Lars-Åke
 
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)))
Click to expand...


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
 
Assuming that the cells E17-E21 are blanks, try the following formula:

=SUMPRODUCT((E5:E33)*(ROW(E5:E33)>=LARGE(ROW(E5:E33)*((E5:E33)<>""),12)))

Hope this helps / Lars-Åke
Click to expand...


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
 
E5-E16 holds the monthly totals.
E22-E33 holds the montly totals.
Click to expand...

So what's in the cells between those 2 ranges? What's in E17:E21? Are there
any numeric values in those cells?
 
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!
=====================================================


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.
 
Back
Top