AVERAGE DAYS OPEN IF BETWEEN DATES

[kathi]

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

[Bob Phillips]

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" <kathi@discussions.microsoft.com> wrote in message
news:43F6738E-FA6D-46D9-8832-7BB0899ECF85@microsoft.com...
> 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

[kathi]

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" <kathi@discussions.microsoft.com> wrote in message
> news:43F6738E-FA6D-46D9-8832-7BB0899ECF85@microsoft.com...
> > 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
>
>
>

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kathi" <kathi@discussions.microsoft.com> wrote in message
news:5D9359DA-F7F4-4753-AEDE-3FEF64A3E420@microsoft.com...
> 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" <kathi@discussions.microsoft.com> wrote in message
> > news:43F6738E-FA6D-46D9-8832-7BB0899ECF85@microsoft.com...
> > > 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
> >
> >
> >

[kathi]

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 K_E_Melton@yahoo.com 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" <kathi@discussions.microsoft.com> wrote in message
> news:5D9359DA-F7F4-4753-AEDE-3FEF64A3E420@microsoft.com...
> > 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" <kathi@discussions.microsoft.com> wrote in message
> > > news:43F6738E-FA6D-46D9-8832-7BB0899ECF85@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>