Rolling Sums question

gray67

New Member
Joined
Mar 17, 2009
Messages
6
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

gray67

New Member
Joined
Mar 17, 2009
Messages
6
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,389
Messages
5,836,972
Members
430,464
Latest member
nickburrett

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top