AVERAGEIF AND SUMIF AND COUNTIF

I have a column with open date (B), column with closed date (C), column with
count of days opened (D).
I need to calculate the average number of days each were opened in the 1st,
2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal year.
I have been physicallly typing in each cell reference that has an opening
date int he first quarter, but would like to be able to say SUM COLUMN D ONLY
IF COLUMN B ">=10/01/2004" also "<=12/31/2004".
THEN COUNTIF COLUMN B ONLY IF ">=10/01/2004" ALSO "<=12/31/2004"

adapt this array formula to your criteria. Don't forget to use
ctrl+shift+enter for entering/editing.

=AVERAGE(IF(ChecksA>=O1,ChecksA<O2,ChecksD))

=AVERAGE(IF(a2:a200>=O1,a2:a200<O2,d2:d200))

Don Guillett
SalesAid Software
[email protected]
"kathi" <[email protected]> wrote in message
news:[email protected]...
>I have a column with open date (B), column with closed date (C), column
>with
> count of days opened (D).
> I need to calculate the average number of days each were opened in the
> 1st,
> 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
> year.
> I have been physicallly typing in each cell reference that has an opening
> date int he first quarter, but would like to be able to say SUM COLUMN D
> ONLY
> IF COLUMN B ">=10/01/2004" also "<=12/31/2004".
> THEN COUNTIF COLUMN B ONLY IF ">=10/01/2004" ALSO "<=12/31/2004"

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" <[email protected]> wrote in message
news:[email protected]...
> I have a column with open date (B), column with closed date (C), column
with
> count of days opened (D).
> I need to calculate the average number of days each were opened in the
1st,
> 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
year.
> I have been physicallly typing in each cell reference that has an opening
> date int he first quarter, but would like to be able to say SUM COLUMN D
ONLY
> IF COLUMN B ">=10/01/2004" also "<=12/31/2004".
> THEN COUNTIF COLUMN B ONLY IF ">=10/01/2004" ALSO "<=12/31/2004"

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

Thank you for your time. I am not sure I completely understand...the D2:D200
reference is what is being averaged and the C cell references are the
criteria, correct?
I adapted it to this ...........
{=AVERAGE(IF('Sheet 1'!$B$1:$B$500>="10/01/2004",'Sheet
1'!$B$1:$B$500<="12/31/2004",'Sheet 1'!$D$1:$D$500))}
but am getting the same result regardless of the dates I put in. Can you
help with that?
I have also tried =AVERAGE(IF('Sheet 1'!$B$1:$B$500>="10/01/2004",'Sheet
1'!$B$1:$B$500<="12/31/2004"),'Sheet 1'!$D$1:$D$500) but it also gives me the
same result no matter what dates are entered. And thanks for the reminder
about the "control+ shift+enter"

"Don Guillett" wrote:

> adapt this array formula to your criteria. Don't forget to use
> ctrl+shift+enter for entering/editing.
>
> =AVERAGE(IF(ChecksA>=O1,ChecksA<O2,ChecksD))
>
> =AVERAGE(IF(a2:a200>=O1,a2:a200<O2,d2:d200))
>
> Don Guillett
> SalesAid Software
> [email protected]
> "kathi" <[email protected]> wrote in message
> news:[email protected]...
> >I have a column with open date (B), column with closed date (C), column
> >with
> > count of days opened (D).
> > I need to calculate the average number of days each were opened in the
> > 1st,
> > 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
> > year.
> > I have been physicallly typing in each cell reference that has an opening
> > date int he first quarter, but would like to be able to say SUM COLUMN D
> > ONLY
> > IF COLUMN B ">=10/01/2004" also "<=12/31/2004".
> > THEN COUNTIF COLUMN B ONLY IF ">=10/01/2004" ALSO "<=12/31/2004"
>
>
>

Also, the checksA is completely foreign to me if you could explain. Thanks
again.


"Don Guillett" wrote:

> adapt this array formula to your criteria. Don't forget to use
> ctrl+shift+enter for entering/editing.
>
> =AVERAGE(IF(ChecksA>=O1,ChecksA<O2,ChecksD))
>
> =AVERAGE(IF(a2:a200>=O1,a2:a200<O2,d2:d200))
>
> Don Guillett
> SalesAid Software
> [email protected]
> "kathi" <[email protected]> wrote in message
> news:[email protected]...
> >I have a column with open date (B), column with closed date (C), column
> >with
> > count of days opened (D).
> > I need to calculate the average number of days each were opened in the
> > 1st,
> > 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
> > year.
> > I have been physicallly typing in each cell reference that has an opening
> > date int he first quarter, but would like to be able to say SUM COLUMN D
> > ONLY
> > IF COLUMN B ">=10/01/2004" also "<=12/31/2004".
> > THEN COUNTIF COLUMN B ONLY IF ">=10/01/2004" ALSO "<=12/31/2004"
>
>
>

=AVERAGE(IF('Sheet 1'!$B$1:$B$500>="10/01/2004",'Sheet
1'!$B$1:$B$500<="12/31/2004",'Sheet 1'!$D$1:$D$500)
I forgot to check notify me of replies so I'm just sending again for that.
Thanks.
Kathi

"Don Guillett" wrote:

> adapt this array formula to your criteria. Don't forget to use
> ctrl+shift+enter for entering/editing.
>
> =AVERAGE(IF(ChecksA>=O1,ChecksA<O2,ChecksD))
>
> =AVERAGE(IF(a2:a200>=O1,a2:a200<O2,d2:d200))
>
> Don Guillett
> SalesAid Software
> [email protected]
> "kathi" <[email protected]> wrote in message
> news:[email protected]...
> >I have a column with open date (B), column with closed date (C), column
> >with
> > count of days opened (D).
> > I need to calculate the average number of days each were opened in the
> > 1st,
> > 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
> > year.
> > I have been physicallly typing in each cell reference that has an opening
> > date int he first quarter, but would like to be able to say SUM COLUMN D
> > ONLY
> > IF COLUMN B ">=10/01/2004" also "<=12/31/2004".
> > THEN COUNTIF COLUMN B ONLY IF ">=10/01/2004" ALSO "<=12/31/2004"
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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))
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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))
> >
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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))
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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))
> > >
> >
> >
> >

THANK YOU!

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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))
> > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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))
> > >
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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))
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Should be where e7 and e8 have your desired dates.
=AVERAGE(IF((CHECKS!$A$7:$A$291>=E7)*(CHECKS!$A$7:$A$291<=E8),CHECKS!$D$7:$D$291))
or
AVERAGE(IF((CHECKS!$A$7:$A$291>=DATEVALUE("10/1/2005"))*(CHECKS!$A$7:$A$291<=DATEVALUE
("10/5/2005")),CHECKS!$D$7:$D$291))

The checksA was a defined name for the range.

--
Don Guillett
SalesAid Software
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> adapt this array formula to your criteria. Don't forget to use
> ctrl+shift+enter for entering/editing.
>
> =AVERAGE(IF(ChecksA>=O1,ChecksA<O2,ChecksD))
>
> =AVERAGE(IF(a2:a200>=O1,a2:a200<O2,d2:d200))
>
> Don Guillett
> SalesAid Software
> [email protected]
> "kathi" <[email protected]> wrote in message
> news:[email protected]...
>>I have a column with open date (B), column with closed date (C), column
>>with
>> count of days opened (D).
>> I need to calculate the average number of days each were opened in the
>> 1st,
>> 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
>> year.
>> I have been physicallly typing in each cell reference that has an opening
>> date int he first quarter, but would like to be able to say SUM COLUMN D
>> ONLY
>> IF COLUMN B ">=10/01/2004" also "<=12/31/2004".
>> THEN COUNTIF COLUMN B ONLY IF ">=10/01/2004" ALSO "<=12/31/2004"
>
>