I have no idea on the negative number Kathi, maybe it is because some of the
dates in D are blank (in the other line I suggest using E1:E500 instead of
D1:D500-C1:C500).

The other thing is trying to get the quarter. Each quarter is 3 months, so
the first thought is to divide the month number by 3 to identify the
quarter. However, Jan divided by gives 1/3, Feb gives 2/3 etc. So we use INT
to get a whole number, but that gives 0 for Jan, 0 for Feb, 1 for Mar. By
adding 2 to the month number before doing an INT, we are getting 1 for Jan,
Feb and Mar, 2 for Apr, May, Jun, etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
This appears to work but it gives me a negative nubmer?????? Do you know
why?
And if you could be so kind to explain I still don't understand the
MONTH()+2/3? I like to know what it is I'm doing/asking for. THANKS
AGAIN.

"Bob Phillips" wrote:

If you want year as well


=AVERAGE(IF((INT((MONTH(B2:B20)+2)/3)=4)*(YEAR(B2:B20=2004)),C2:C20-B2:B20))

for 4th QTR FY04


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
Forgive me if I'm being dense. I'm trying to comprehend. Okay, so
when I
want the average of the days open for a single fiscal year? How do I
get
the
answer for 1st quarter Fiscal Year 2004, 2nd Quarter 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))