C D E
1 August 2, 2004 August 6, 2004 4
2 August 6, 2004 November 9, 2005 460
3 August 6, 2004 565
4 August 20, 2004 June 13, 2005 297
5 August 30, 2004 October 19, 2004 50
6 September 7, 2004 September 27, 2004 20
I have open dates in C and close dates in D and I have the formula
{=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} in Column E that gives me the
number of days the file was open.
These dates begin in 09/2003 until present and are added onto daily. I need
to AVERAGE the number of days open (D-C:C) for each quarter of each fiscal
year. {=AVERAGE(IF(C1:C500>="10/01/2003"<="12/31/2003"),D1500-C1:C500)} but
it would definitely be easier if I could instead use
{=AVERAGE(IF(INT((MONTH(C1:C500)+2)/3)=4,D1500-C1:C500))} however, I don't
understand how to get results for each individual 1st QTR FY04, 2nd QTR FY04,
3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR
FY05, 1st QTR FY06, 2nd QTR FY06, etc...

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$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$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

I have answered pretty comprehensively in your original thread.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" <(E-Mail Removed)> wrote in message
news:F58E03BF-5385-45F8-8A41-(E-Mail Removed)...
> C D E
> 1 August 2, 2004 August 6, 2004 4
> 2 August 6, 2004 November 9, 2005 460
> 3 August 6, 2004 565
> 4 August 20, 2004 June 13, 2005 297
> 5 August 30, 2004 October 19, 2004 50
> 6 September 7, 2004 September 27, 2004 20
> I have open dates in C and close dates in D and I have the formula
> {=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} in Column E that gives me the
> number of days the file was open.
> These dates begin in 09/2003 until present and are added onto daily. I
need
> to AVERAGE the number of days open (D-C:C) for each quarter of each
fiscal
> year. {=AVERAGE(IF(C1:C500>="10/01/2003"<="12/31/2003"),D1500-C1:C500)}
but
> it would definitely be easier if I could instead use
> {=AVERAGE(IF(INT((MONTH(C1:C500)+2)/3)=4,D1500-C1:C500))} however, I
don't
> understand how to get results for each individual 1st QTR FY04, 2nd QTR
FY04,
> 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th
QTR
> FY05, 1st QTR FY06, 2nd QTR FY06, etc...
>

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$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$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$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

sorry but apparently not comprehensively enough for me to comprehend, as I
said I'm dense. I don't understand. I can not get the formulas for
averaging the days opened to work. I can not figure out why. I was hoping
for some assistance. But I don't comprehend. With the formula I am getting
an answer of 32 but the true average is 55. It is not working and I can't
figure out why.

"Bob Phillips" wrote:

> I have answered pretty comprehensively in your original thread.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "kathi" <(E-Mail Removed)> wrote in message
> news:F58E03BF-5385-45F8-8A41-(E-Mail Removed)...
> > C D E
> > 1 August 2, 2004 August 6, 2004 4
> > 2 August 6, 2004 November 9, 2005 460
> > 3 August 6, 2004 565
> > 4 August 20, 2004 June 13, 2005 297
> > 5 August 30, 2004 October 19, 2004 50
> > 6 September 7, 2004 September 27, 2004 20
> > I have open dates in C and close dates in D and I have the formula
> > {=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} in Column E that gives me the
> > number of days the file was open.
> > These dates begin in 09/2003 until present and are added onto daily. I
> need
> > to AVERAGE the number of days open (D-C:C) for each quarter of each
> fiscal
> > year. {=AVERAGE(IF(C1:C500>="10/01/2003"<="12/31/2003"),D1500-C1:C500)}
> but
> > it would definitely be easier if I could instead use
> > {=AVERAGE(IF(INT((MONTH(C1:C500)+2)/3)=4,D1500-C1:C500))} however, I
> don't
> > understand how to get results for each individual 1st QTR FY04, 2nd QTR
> FY04,
> > 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th
> QTR
> > FY05, 1st QTR FY06, 2nd QTR FY06, etc...
> >
>
>
>