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 quot;gt;=10/01/2004quot; also quot;lt;=12/31/2004quot;.
THEN COUNTIF COLUMN B ONLY IF quot;gt;=10/01/2004quot; ALSO quot;lt;=12/31/2004quot;
adapt this array formula to your criteria. Don't forget to use
ctrl shift enter for entering/editing.
=AVERAGE(IF(ChecksAgt;=O1,ChecksAlt;O2,ChecksD))
=AVERAGE(IF(a2:a200gt;=O1,a2:a200lt;O2,d2:d200))
Don Guillett
SalesAid Software
quot;kathiquot; gt; wrote in message
...
gt;I have a column with open date (B), column with closed date (C), column
gt;with
gt; count of days opened (D).
gt; I need to calculate the average number of days each were opened in the
gt; 1st,
gt; 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
gt; year.
gt; I have been physicallly typing in each cell reference that has an opening
gt; date int he first quarter, but would like to be able to say SUM COLUMN D
gt; ONLY
gt; IF COLUMN B quot;gt;=10/01/2004quot; also quot;lt;=12/31/2004quot;.
gt; THEN COUNTIF COLUMN B ONLY IF quot;gt;=10/01/2004quot; ALSO quot;lt;=12/31/2004quot;
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)
quot;kathiquot; gt; wrote in message
...
gt; I have a column with open date (B), column with closed date (C), column
with
gt; count of days opened (D).
gt; I need to calculate the average number of days each were opened in the
1st,
gt; 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
year.
gt; I have been physicallly typing in each cell reference that has an opening
gt; date int he first quarter, but would like to be able to say SUM COLUMN D
ONLY
gt; IF COLUMN B quot;gt;=10/01/2004quot; also quot;lt;=12/31/2004quot;.
gt; THEN COUNTIF COLUMN B ONLY IF quot;gt;=10/01/2004quot; ALSO quot;lt;=12/31/2004quot;
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:B20gt;=10/01/2003 AND
ALSO IF B2:B20lt;=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
quot;Bob Phillipsquot; 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$500gt;=quot;10/01/2004quot;,'Sheet
1'!$B$1:$B$500lt;=quot;12/31/2004quot;,'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$500gt;=quot;10/01/2004quot;,'Sheet
1'!$B$1:$B$500lt;=quot;12/31/2004quot;),'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 quot;control shift enterquot;
quot;Don Guillettquot; wrote:
gt; adapt this array formula to your criteria. Don't forget to use
gt; ctrl shift enter for entering/editing.
gt;
gt; =AVERAGE(IF(ChecksAgt;=O1,ChecksAlt;O2,ChecksD))
gt;
gt; =AVERAGE(IF(a2:a200gt;=O1,a2:a200lt;O2,d2:d200))
gt;
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;kathiquot; gt; wrote in message
gt; ...
gt; gt;I have a column with open date (B), column with closed date (C), column
gt; gt;with
gt; gt; count of days opened (D).
gt; gt; I need to calculate the average number of days each were opened in the
gt; gt; 1st,
gt; gt; 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
gt; gt; year.
gt; gt; I have been physicallly typing in each cell reference that has an opening
gt; gt; date int he first quarter, but would like to be able to say SUM COLUMN D
gt; gt; ONLY
gt; gt; IF COLUMN B quot;gt;=10/01/2004quot; also quot;lt;=12/31/2004quot;.
gt; gt; THEN COUNTIF COLUMN B ONLY IF quot;gt;=10/01/2004quot; ALSO quot;lt;=12/31/2004quot;
gt;
gt;
gt;
Also, the checksA is completely foreign to me if you could explain. Thanks
again.quot;Don Guillettquot; wrote:
gt; adapt this array formula to your criteria. Don't forget to use
gt; ctrl shift enter for entering/editing.
gt;
gt; =AVERAGE(IF(ChecksAgt;=O1,ChecksAlt;O2,ChecksD))
gt;
gt; =AVERAGE(IF(a2:a200gt;=O1,a2:a200lt;O2,d2:d200))
gt;
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;kathiquot; gt; wrote in message
gt; ...
gt; gt;I have a column with open date (B), column with closed date (C), column
gt; gt;with
gt; gt; count of days opened (D).
gt; gt; I need to calculate the average number of days each were opened in the
gt; gt; 1st,
gt; gt; 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
gt; gt; year.
gt; gt; I have been physicallly typing in each cell reference that has an opening
gt; gt; date int he first quarter, but would like to be able to say SUM COLUMN D
gt; gt; ONLY
gt; gt; IF COLUMN B quot;gt;=10/01/2004quot; also quot;lt;=12/31/2004quot;.
gt; gt; THEN COUNTIF COLUMN B ONLY IF quot;gt;=10/01/2004quot; ALSO quot;lt;=12/31/2004quot;
gt;
gt;
gt;
=AVERAGE(IF('Sheet 1'!$B$1:$B$500gt;=quot;10/01/2004quot;,'Sheet
1'!$B$1:$B$500lt;=quot;12/31/2004quot;,'Sheet 1'!$D$1:$D$500)
I forgot to check notify me of replies so I'm just sending again for that.
Thanks.
Kathi
quot;Don Guillettquot; wrote:
gt; adapt this array formula to your criteria. Don't forget to use
gt; ctrl shift enter for entering/editing.
gt;
gt; =AVERAGE(IF(ChecksAgt;=O1,ChecksAlt;O2,ChecksD))
gt;
gt; =AVERAGE(IF(a2:a200gt;=O1,a2:a200lt;O2,d2:d200))
gt;
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;kathiquot; gt; wrote in message
gt; ...
gt; gt;I have a column with open date (B), column with closed date (C), column
gt; gt;with
gt; gt; count of days opened (D).
gt; gt; I need to calculate the average number of days each were opened in the
gt; gt; 1st,
gt; gt; 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal
gt; gt; year.
gt; gt; I have been physicallly typing in each cell reference that has an opening
gt; gt; date int he first quarter, but would like to be able to say SUM COLUMN D
gt; gt; ONLY
gt; gt; IF COLUMN B quot;gt;=10/01/2004quot; also quot;lt;=12/31/2004quot;.
gt; gt; THEN COUNTIF COLUMN B ONLY IF quot;gt;=10/01/2004quot; ALSO quot;lt;=12/31/2004quot;
gt;
gt;
gt;
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)
quot;kathiquot; gt; wrote in message
...
gt; Thank you so much for the response. I think this is the type of formula
I
gt; was looking for but if you could just clarify in my mind, please. I
gt; understand =AVERAGE(IF but am not clear on why finding the MONTH then
gt; rounding down (INT than adding 2) and dividing by /3) and this
gt; part really confuses me =4, I understand that finding the difference
gt; between relates to the average number of days open but am not clear how
???
gt; If were more clear then I could definitely adapte this for y use. So for
my
gt; use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20gt;=10/01/2003
AND
gt; ALSO IF B2:B20lt;=12/31/2003
gt; so if there is a way to define the 1st or 2nd etc. quarter other than this
I
gt; could really use that.
gt; THANKS AGAIN FOR YOUR TIME
gt;
gt; quot;Bob Phillipsquot; wrote:
gt; Kathi,
gt; I am not sure what to do when the start date is in one quarter and the end
gt; date is in another, but assuming you count from the start date quarter,
try
gt; this
gt; =AVERAGE(IF(INT((MONTH(B2:B20) 2)/3)=4,C2:C20-B2:B20))
gt;
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..........
quot;Bob Phillipsquot; wrote:
gt; Kathi,
gt;
gt; You mentioned that you wanted to average by quarter, so the
gt; INT((MONTH(B2:B20) 2)/3) works out the quarter for each date in B2:B20. The
gt; =4 is just an example testing for quarter 4, Oct, Nov, Dec.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;kathiquot; gt; wrote in message
gt; ...
gt; gt; Thank you so much for the response. I think this is the type of formula
gt; I
gt; gt; was looking for but if you could just clarify in my mind, please. I
gt; gt; understand =AVERAGE(IF but am not clear on why finding the MONTH then
gt; gt; rounding down (INT than adding 2) and dividing by /3) and this
gt; gt; part really confuses me =4, I understand that finding the difference
gt; gt; between relates to the average number of days open but am not clear how
gt; ???
gt; gt; If were more clear then I could definitely adapte this for y use. So for
gt; my
gt; gt; use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20gt;=10/01/2003
gt; AND
gt; gt; ALSO IF B2:B20lt;=12/31/2003
gt; gt; so if there is a way to define the 1st or 2nd etc. quarter other than this
gt; I
gt; gt; could really use that.
gt; gt; THANKS AGAIN FOR YOUR TIME
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; Kathi,
gt; gt; I am not sure what to do when the start date is in one quarter and the end
gt; gt; date is in another, but assuming you count from the start date quarter,
gt; try
gt; gt; this
gt; gt; =AVERAGE(IF(INT((MONTH(B2:B20) 2)/3)=4,C2:C20-B2:B20))
gt; gt;
gt;
gt;
gt;
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)
quot;kathiquot; gt; wrote in message
...
gt; Forgive me if I'm being dense. I'm trying to comprehend. Okay, so when I
gt; want the average of the days open for a single fiscal year? How do I get
the
gt; answer for 1st quarter Fiscal Year 2004, 2nd Quarter FY04, 3rd QTR FY04,
4th
gt; QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR FY05, 1st QTR
gt; FY06, 2nd QTR FY06, etc..........
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Kathi,
gt; gt;
gt; gt; You mentioned that you wanted to average by quarter, so the
gt; gt; INT((MONTH(B2:B20) 2)/3) works out the quarter for each date in B2:B20.
The
gt; gt; =4 is just an example testing for quarter 4, Oct, Nov, Dec.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;kathiquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Thank you so much for the response. I think this is the type of
formula
gt; gt; I
gt; gt; gt; was looking for but if you could just clarify in my mind, please. I
gt; gt; gt; understand =AVERAGE(IF but am not clear on why finding the MONTH
then
gt; gt; gt; rounding down (INT than adding 2) and dividing by /3) and
this
gt; gt; gt; part really confuses me =4, I understand that finding the
difference
gt; gt; gt; between relates to the average number of days open but am not clear
how
gt; gt; ???
gt; gt; gt; If were more clear then I could definitely adapte this for y use. So
for
gt; gt; my
gt; gt; gt; use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF
B2:B20gt;=10/01/2003
gt; gt; AND
gt; gt; gt; ALSO IF B2:B20lt;=12/31/2003
gt; gt; gt; so if there is a way to define the 1st or 2nd etc. quarter other than
this
gt; gt; I
gt; gt; gt; could really use that.
gt; gt; gt; THANKS AGAIN FOR YOUR TIME
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; Kathi,
gt; gt; gt; I am not sure what to do when the start date is in one quarter and the
end
gt; gt; gt; date is in another, but assuming you count from the start date
quarter,
gt; gt; try
gt; gt; gt; this
gt; gt; gt; =AVERAGE(IF(INT((MONTH(B2:B20) 2)/3)=4,C2:C20-B2:B20))
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
留言列表
