Rolling Sums question

[gray67]

Hi - this is my first post after having watched the video podcasts for a bit. Here's my situation.

I have a number of territories that represent a selling team. Each territory has 24 months of sales data associated with it. I need to build a formula that will allow me to dynamically calculate a Rolling 3 or Rolling 6 or Rolling 12 month sum or average. The raw data is arranged like so:

TERR 1 MONTH 24 SALES
TERR 1 MONTH 23 SALES
.
.
.
TERR 1 MONTH 1 SALES
TERR 2 MONTH 24 SALES
.
.
.

Month 24 is the oldest month while month 1 is the most recent month. Ideally, the formula would create rolling sums (or averages) of the required period and would automatically know when the territory ID changes so I can just copy/paste the thing down my spreadsheet.

I've tried to do this via pivot tables to minimal success. I figure I need to pivot it regardless just because any given territory may not have any sales in a particular month, and thus I would not have a row for that terr/month combination.

Any ideas? Thanks in advance.

 

[gray67]

To further clarify - the net result of the formula for any given territory would be to take the 24 months of individual data and spit out 22 months of rolling data.