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)
