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
|