Does your 1st quarter start at 1st January?

You could use something like this for 4th quarter of 2005

=AVERAGE(IF("Q"&INT((MONTH(C$1:C$500)+2)/3)&TEXT(C$1:C$500,"-yy")="Q4-05",D$1:D$500-C$1:C$500))

confirmed with CTRL+SHIFT+ENTER

even easier if you put your quarters in one column, e.g. in Y1 "Q1-03",
in Y2 "Q2-03" etc. then use this formula in Z1 and copy formula down
column

=AVERAGE(IF("Q"&INT((MONTH(C$1:C$500)+2)/3)&TEXT(C$1:C$500,"-yy")=Y1,D$1:D$500-C$1:C$500))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=515284

No, fiscal year quarters start with (FY2004)1st QTR 10/01/2003-12/31/2003,
FY04 2nd QTR 01/01/2004-03/30/2004, 3rd QTR FY04 04/01/2004-06/30/2004, 4th
QTR FY04 07/01/2004-09/30/2004........etc...........

"daddylonglegs" wrote:


Does your 1st quarter start at 1st January?

You could use something like this for 4th quarter of 2005

=AVERAGE(IF("Q"&INT((MONTH(C$1:C$500)+2)/3)&TEXT(C$1:C$500,"-yy")="Q4-05",D$1:D$500-C$1:C$500))

confirmed with CTRL+SHIFT+ENTER

even easier if you put your quarters in one column, e.g. in Y1 "Q1-03",
in Y2 "Q2-03" etc. then use this formula in Z1 and copy formula down
column

=AVERAGE(IF("Q"&INT((MONTH(C$1:C$500)+2)/3)&TEXT(C$1:C$500,"-yy")=Y1,D$1:D$500-C$1:C$500))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=515284

Perhaps it would be easier to adopt a slightly different approach. You
could use another column to establish the Quarter of each date in
C1:C500, e.g. in X1 copied down to X500

="Q"&MOD(INT((MONTH(C1)+2)/3),4)+1&TEXT(EDATE(C1,3),"-yy")

which should give you the correct quarters, e.g. 12/12/2003 gives
"Q1-04"

then use this formula in Z1

=AVERAGE(IF(X$1:X$500=Y1,D$1:D$500-C$1:C$500))

confirmed with CTRL+SHIFT+ENTER

where Y1 contains "Q1-04" or similar

Note for the first formula above that EDATE is part of Analysis ToolPak
add-in, if you can't use that try this formula instead

="Q"&MOD(INT((MONTH(C1)+2)/3),4)+1&TEXT(DATE(YEAR(C1),MONTH(C1)+3,1),"-yy")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=515284