AVERAGE DAYS OPEN IF BETWEEN DATES
 

I am still not comprehending why I can not get the formulas for averaging
number of days an invoice was open to work. I can not figure this out. I
was hoping for some assistance. With the formula I am using I am getting an
answer of 32 but the true average is 55. It is not working and I can't
figure out why.
=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1))*(INT((MONTH( $C$1:$C$500)+2)/3)=$G$2),$E$1:$E$500))
A1:J1 ARE THE YEARS
A2:J2 ARE THE QUARTERS

AVERAGE DAYS OPEN IF BETWEEN DATES
 

I found an error in the formula and this works for me

=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1)*(INT((MONTH($ C$1:$C$500)+2)/3)=$G$2),$E
$1:$E$500))

as an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
I am still not comprehending why I can not get the formulas for averaging
number of days an invoice was open to work. I can not figure this out. I
was hoping for some assistance. With the formula I am using I am getting
an
answer of 32 but the true average is 55. It is not working and I can't
figure out why.

=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1))*(INT((MONTH( $C$1:$C$500)+2)/3)=$G$2),$
E$1:$E$500))
A1:J1 ARE THE YEARS
A2:J2 ARE THE QUARTERS

AVERAGE DAYS OPEN IF BETWEEN DATES
 

Sorry but it is still not working for me.
{=AVERAGE(IF((YEAR('Copy Link'!$C$1:$C$500)=$C$32)*(INT((MONTH('Copy
Link'!$C$1:$C$500)+2)/3)=$C$33,'Copy Link'!$N$1:$N$500))}

"Bob Phillips" wrote:

I found an error in the formula and this works for me

=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1)*(INT((MONTH($ C$1:$C$500)+2)/3)=$G$2),$E
$1:$E$500))

as an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
I am still not comprehending why I can not get the formulas for averaging
number of days an invoice was open to work. I can not figure this out. I
was hoping for some assistance. With the formula I am using I am getting
an
answer of 32 but the true average is 55. It is not working and I can't
figure out why.

=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1))*(INT((MONTH( $C$1:$C$500)+2)/3)=$G$2),$
E$1:$E$500))
A1:J1 ARE THE YEARS
A2:J2 ARE THE QUARTERS

AVERAGE DAYS OPEN IF BETWEEN DATES
 

It did for me Kathi. What does the data look like?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
Sorry but it is still not working for me.
{=AVERAGE(IF((YEAR('Copy Link'!$C$1:$C$500)=$C$32)*(INT((MONTH('Copy
Link'!$C$1:$C$500)+2)/3)=$C$33,'Copy Link'!$N$1:$N$500))}

"Bob Phillips" wrote:

I found an error in the formula and this works for me


=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1)*(INT((MONTH($ C$1:$C$500)+2)/3)=$G$2),$E
$1:$E$500))

as an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
I am still not comprehending why I can not get the formulas for
averaging
number of days an invoice was open to work. I can not figure this
out. I
was hoping for some assistance. With the formula I am using I am
getting
an
answer of 32 but the true average is 55. It is not working and I
can't
figure out why.


=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1))*(INT((MONTH( $C$1:$C$500)+2)/3)=$G$2),$
E$1:$E$500))
A1:J1 ARE THE YEARS
A2:J2 ARE THE QUARTERS

AVERAGE DAYS OPEN IF BETWEEN DATES
 

Tried to email you the worksheet as an attachment but keep getting failure
notices. The data looks like this....'Copy Link' Sheet has
A B C D E F G
E5S040001 October 1, 2003 October 14, 2003 PARKER YB5 Y 14
E5S040015 November 5, 2003 ERVIN KCI K 2

COLUMN F IS =LEFT(J22,1) TO GIVE ME THE FIRST LETTER OF COLUMN E
COLUMN G IS =MAX(IF(ISBLANK(D23),TODAY(),D23)-C23,1)+1 TO COUNT THE DAYS OPEN

Trends Analysis Sheet has formulas to give me the comparisons between fiscal
years by quarters. And I also need to report the number of days each was
open during each quarter for a comparison.

My personal email is and I can send you the worksheet
if you wish to send me an email that I can communicate with.





"Bob Phillips" wrote:

It did for me Kathi. What does the data look like?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
Sorry but it is still not working for me.
{=AVERAGE(IF((YEAR('Copy Link'!$C$1:$C$500)=$C$32)*(INT((MONTH('Copy
Link'!$C$1:$C$500)+2)/3)=$C$33,'Copy Link'!$N$1:$N$500))}

"Bob Phillips" wrote:

I found an error in the formula and this works for me


=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1)*(INT((MONTH($ C$1:$C$500)+2)/3)=$G$2),$E
$1:$E$500))

as an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" wrote in message
...
I am still not comprehending why I can not get the formulas for
averaging
number of days an invoice was open to work. I can not figure this
out. I
was hoping for some assistance. With the formula I am using I am
getting
an
answer of 32 but the true average is 55. It is not working and I
can't
figure out why.


=AVERAGE(IF((YEAR($C$1:$C$500)=$G$1))*(INT((MONTH( $C$1:$C$500)+2)/3)=$G$2),$
E$1:$E$500))
A1:J1 ARE THE YEARS
A2:J2 ARE THE QUARTERS