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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

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