MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find quarterly dates in dynamic array containing monthly data


Posted by Jean Elliott on March 13, 2001 2:10 AM

Hi

I have a live DDE feed giving me monthly data for the past 54 months in an array. I need to do analysis on quarterly periods, so i have to pick out the quarters dynamically into another section, from where I can do calculations and graphs. Any ideas, or should i be a bit clearer on my explanation?

Any suggestions would be appreciated.

Regards,
Jean


Posted by Dave Hawley on March 13, 2001 3:11 AM

Jean, assuming your dates are in Column A you could use a formula like this:
=IF(AND(WEEKNUM(A1)>=1,WEEKNUM(A1)<14),A1)

This will return the date in A1 if it is within Quarter 1.


Dave


OzGrid Business Applications

Posted by Mark W. on March 13, 2001 6:00 AM

Jean, in a PivotTable you can group date values
by quarter. Have you considered using PivotTables
to summarize your data? The use of a PivotTable
you also support your charting needs.

Posted by Mark W. on March 13, 2001 8:37 AM

Jean, if you don't want to go the PivotTable route
I'd recommend that you use....

=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)

Posted by Mark W. on March 13, 2001 10:48 AM

...Or, if you prefer...

=VLOOKUP(MONTH(A1),{1,1;4,2;7,3;10,4},2)