"Rolling up" periods

[cdipaolo]

To simplify, I have a spreadsheet with a row that has dates (i.e. 1/2001, 2/2001, 3/2001, etc.) and a row with earnings figures (i.e. $5000, $4000, etc.). I have a column for each month (1-120...start date is configured elsewhere). I am trying to write a function or macro that returns quarterly or annual results.

I'm thinking I would have to look at the first date to decide how many months are in the first quarter (i.e. if the first month is 2 (Feb), then I would have 2 months in the first quarter....and then the other quarters would always be the following 3 months. Similar thing for annual summing. Has anyone seen or created a function like this before? Any tips or pitfalls that people have would be helpful. Thanks!

 

[SkipVought]

HI,

If your dates are REALLY Excel dates, you can summarize your data using the Pivot Table Wizard and then Group the Data Pivot Field using the Data/Group & Outline.../Group and select how you want the grouping to be arranged, months, quarters etc. Pretty slick! Skip,
SkipAndMary1017@mindspring.com

 

[xlbo]

Hey Skip - good to see you're back - you could leave a few questions for the rest of us to answer tho' ;-) Rgds
~Geoff~

 

[SkipVought]

Hey Geoff,

Thanx for the jab ;-) -- good to be back.

cdipaolo,

I can send you a sample snippet if you have never used Pivot Tables and Group before. Skip,
SkipAndMary1017@mindspring.com