software

C D E
1August 2, 2004August 6, 2004 4
2August 6, 2004November 9, 2005460
3August 6, 2004565
4August 20, 2004 June 13, 2005297
5August 30, 2004 October 19, 2004 50
6September 7, 2004 September 27, 2004 20
I have open dates in C and close dates in D and I have the formula
{=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} in Column E that gives me the
number of days the file was open.
These dates begin in 09/2003 until present and are added onto daily. I need
to AVERAGE the number of days open (D-C:C) for each quarter of each fiscal
year. {=AVERAGE(IF(C1:C500gt;=quot;10/01/2003quot;lt;=quot;12/31/2003quot;),D1500-C1:C500)} but
it would definitely be easier if I could instead use
{=AVERAGE(IF(INT((MONTH(C1:C500) 2)/3)=4,D1500-C1:C500))} however, I don't
understand how to get results for each individual 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...
Does your 1st quarter start at 1st January?

You could use something like this for 4th quarter of 2005

=AVERAGE(IF(quot;Qquot;amp;INT((MONTH(C$1:C$500) 2)/3)amp;TEXT(C$1:C$500,quot;-yyquot;)=quot;Q4-05quot;,D$1$500-C$1:C$500))

confirmed with CTRL SHIFT ENTER

even easier if you put your quarters in one column, e.g. in Y1 quot;Q1-03quot;,
in Y2 quot;Q2-03quot; etc. then use this formula in Z1 and copy formula down
column

=AVERAGE(IF(quot;Qquot;amp;INT((MONTH(C$1:C$500) 2)/3)amp;TEXT(C$1:C$500,quot;-yyquot;)=Y1,D$1$500-C$1:C$500))

confirmed with CTRL SHIFT ENTER--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=515284I have answered pretty comprehensively in your original thread.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;kathiquot; gt; wrote in message
...
gt; C D E
gt; 1 August 2, 2004 August 6, 2004 4
gt; 2 August 6, 2004 November 9, 2005 460
gt; 3 August 6, 2004 565
gt; 4 August 20, 2004 June 13, 2005 297
gt; 5 August 30, 2004 October 19, 2004 50
gt; 6 September 7, 2004 September 27, 2004 20
gt; I have open dates in C and close dates in D and I have the formula
gt; {=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} in Column E that gives me the
gt; number of days the file was open.
gt; These dates begin in 09/2003 until present and are added onto daily. I
need
gt; to AVERAGE the number of days open (D-C:C) for each quarter of each
fiscal
gt; year. {=AVERAGE(IF(C1:C500gt;=quot;10/01/2003quot;lt;=quot;12/31/2003quot;),D1500-C1:C500)}
but
gt; it would definitely be easier if I could instead use
gt; {=AVERAGE(IF(INT((MONTH(C1:C500) 2)/3)=4,D1500-C1:C500))} however, I
don't
gt; understand how to get results for each individual 1st QTR FY04, 2nd QTR
FY04,
gt; 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th
QTR
gt; FY05, 1st QTR FY06, 2nd QTR FY06, etc...
gt;
No, fiscal year quarters start with (FY2004)1st QTR 10/01/2003-12/31/2003,
FY04 2nd QTR 01/01/2004-03/30/2004, 3rd QTR FY04 04/01/2004-06/30/2004, 4th
QTR FY04 07/01/2004-09/30/2004........etc...........

quot;daddylonglegsquot; wrote:

gt;
gt; Does your 1st quarter start at 1st January?
gt;
gt; You could use something like this for 4th quarter of 2005
gt;
gt; =AVERAGE(IF(quot;Qquot;amp;INT((MONTH(C$1:C$500) 2)/3)amp;TEXT(C$1:C$500,quot;-yyquot;)=quot;Q4-05quot;,D$1$500-C$1:C$500))
gt;
gt; confirmed with CTRL SHIFT ENTER
gt;
gt; even easier if you put your quarters in one column, e.g. in Y1 quot;Q1-03quot;,
gt; in Y2 quot;Q2-03quot; etc. then use this formula in Z1 and copy formula down
gt; column
gt;
gt; =AVERAGE(IF(quot;Qquot;amp;INT((MONTH(C$1:C$500) 2)/3)amp;TEXT(C$1:C$500,quot;-yyquot;)=Y1,D$1$500-C$1:C$500))
gt;
gt; confirmed with CTRL SHIFT ENTER
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=515284
gt;
gt;


Perhaps it would be easier to adopt a slightly different approach. You
could use another column to establish the Quarter of each date in
C1:C500, e.g. in X1 copied down to X500

=quot;Qquot;amp;MOD(INT((MONTH(C1) 2)/3),4) 1amp;TEXT(EDATE(C1,3),quot;-yyquot;)

which should give you the correct quarters, e.g. 12/12/2003 gives
quot;Q1-04quot;

then use this formula in Z1

=AVERAGE(IF(X$1:X$500=Y1,D$1$500-C$1:C$500))

confirmed with CTRL SHIFT ENTER

where Y1 contains quot;Q1-04quot; or similar

Note for the first formula above that EDATE is part of Analysis ToolPak
add-in, if you can't use that try this formula instead

=quot;Qquot;amp;MOD(INT((MONTH(C1) 2)/3),4) 1amp;TEXT(DATE(YEAR(C1),MONTH(C1) 3,1),quot;-yyquot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=515284sorry but apparently not comprehensively enough for me to comprehend, as I
said I'm dense. I don't understand. I can not get the formulas for
averaging the days opened to work. I can not figure out why. I was hoping
for some assistance. But I don't comprehend. With the formula I am getting
an answer of 32 but the true average is 55. It is not working and I can't
figure out why.

quot;Bob Phillipsquot; wrote:

gt; I have answered pretty comprehensively in your original thread.
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; C D E
gt; gt; 1 August 2, 2004 August 6, 2004 4
gt; gt; 2 August 6, 2004 November 9, 2005 460
gt; gt; 3 August 6, 2004 565
gt; gt; 4 August 20, 2004 June 13, 2005 297
gt; gt; 5 August 30, 2004 October 19, 2004 50
gt; gt; 6 September 7, 2004 September 27, 2004 20
gt; gt; I have open dates in C and close dates in D and I have the formula
gt; gt; {=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} in Column E that gives me the
gt; gt; number of days the file was open.
gt; gt; These dates begin in 09/2003 until present and are added onto daily. I
gt; need
gt; gt; to AVERAGE the number of days open (D-C:C) for each quarter of each
gt; fiscal
gt; gt; year. {=AVERAGE(IF(C1:C500gt;=quot;10/01/2003quot;lt;=quot;12/31/2003quot;),D1500-C1:C500)}
gt; but
gt; gt; it would definitely be easier if I could instead use
gt; gt; {=AVERAGE(IF(INT((MONTH(C1:C500) 2)/3)=4,D1500-C1:C500))} however, I
gt; don't
gt; gt; understand how to get results for each individual 1st QTR FY04, 2nd QTR
gt; FY04,
gt; gt; 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th
gt; QTR
gt; gt; FY05, 1st QTR FY06, 2nd QTR FY06, etc...
gt; gt;
gt;
gt;
gt;