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
(E-Mail Removed)
"kathi" <(E-Mail Removed)> wrote in message
news:01A12D86-2531-4707-BA94-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:01A12D86-2531-4707-BA94-(E-Mail Removed)...
> 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 D2200
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
> (E-Mail Removed)
> "kathi" <(E-Mail Removed)> wrote in message
> news:01A12D86-2531-4707-BA94-(E-Mail Removed)...
> >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
> (E-Mail Removed)
> "kathi" <(E-Mail Removed)> wrote in message
> news:01A12D86-2531-4707-BA94-(E-Mail Removed)...
> >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
> (E-Mail Removed)
> "kathi" <(E-Mail Removed)> wrote in message
> news:01A12D86-2531-4707-BA94-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:05F544BC-5DB7-440C-A2DA-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:05F544BC-5DB7-440C-A2DA-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:54659BF1-ECF2-4ADC-8CD3-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:05F544BC-5DB7-440C-A2DA-(E-Mail Removed)...
> > > 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))
> > >
> >
> >
> >