AVERAGE DAYS OPEN IF BETWEEN DATES

K

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
 
B

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)
 
K

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))}
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
K

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 [email protected] and I can send you the worksheet
if you wish to send me an email that I can communicate with.
 
Top