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 in E I have the formula
{=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} to give me the number of days the
file was open. These dates begin in 2003 until present day and are added
onto daily. I need to AVERAGE the days open (D-C) for each quarter of each
fiscal year.
=AVERAGE(IF(C1:C500="10/01/2003"<="12/31/2003"),D1:D500-C1:C500) but it
would definitely be easier if I could instead use
{=AVERAGE(IF(INT((MONTH(C1:C500)+2)/3)=4,D1:D500-C1:C500))} however, I don't
understand how to get 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...

"Bob Phillips" wrote:

Kathi,

You mentioned that you wanted to average by quarter, so the
INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The
=4 is just an example testing for quarter 4, Oct, Nov, Dec.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
Thank you so much for the response. I think this is the type of formula
I
was looking for but if you could just clarify in my mind, please. I
understand =AVERAGE(IF but am not clear on why finding the MONTH then
rounding down (INT than adding +2) and dividing by /3) and this
part really confuses me =4, I understand that finding the difference
between relates to the average number of days open but am not clear how
???
If were more clear then I could definitely adapte this for y use. So for
my
use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003
AND
ALSO IF B2:B20<=12/31/2003
so if there is a way to define the 1st or 2nd etc. quarter other than this
I
could really use that.
THANKS AGAIN FOR YOUR TIME

"Bob Phillips" wrote:
Kathi,
I am not sure what to do when the start date is in one quarter and the end
date is in another, but assuming you count from the start date quarter,
try
this
=AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20))

Kathi,

Enter

2004 in G1, 2004 in H1, 2004 in I1, 2004 in J1, 2005 in K1, 2005 in L1,
etc., then
1 in G2, 2 in H2, 3 in I2, 4 in J2, 1 in K2, 2 in L2 etc.

Then in G3 add

=AVERAGE(IF((YEAR($C$1:$C$500=G$1))*(INT((MONTH($C $1:$C$500)+2)/3)=G$2),$E$1
:$E$500))

and copy down and across


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
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 in E I have the formula
{=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} to give me the number of days
the
file was open. These dates begin in 2003 until present day and are added
onto daily. I need to AVERAGE the days open (D-C) for each quarter of
each
fiscal year.
=AVERAGE(IF(C1:C500="10/01/2003"<="12/31/2003"),D1:D500-C1:C500) but it
would definitely be easier if I could instead use
{=AVERAGE(IF(INT((MONTH(C1:C500)+2)/3)=4,D1:D500-C1:C500))} however, I
don't
understand how to get 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...

"Bob Phillips" wrote:

Kathi,

You mentioned that you wanted to average by quarter, so the
INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20.
The
=4 is just an example testing for quarter 4, Oct, Nov, Dec.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
Thank you so much for the response. I think this is the type of
formula
I
was looking for but if you could just clarify in my mind, please. I
understand =AVERAGE(IF but am not clear on why finding the MONTH
then
rounding down (INT than adding +2) and dividing by /3) and
this
part really confuses me =4, I understand that finding the
difference
between relates to the average number of days open but am not clear
how
???
If were more clear then I could definitely adapte this for y use. So
for
my
use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF
B2:B20=10/01/2003
AND
ALSO IF B2:B20<=12/31/2003
so if there is a way to define the 1st or 2nd etc. quarter other than
this
I
could really use that.
THANKS AGAIN FOR YOUR TIME

"Bob Phillips" wrote:
Kathi,
I am not sure what to do when the start date is in one quarter and the
end
date is in another, but assuming you count from the start date
quarter,
try
this
=AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20))