Average of days within a range
 

I have a spreadsheet with two series of dates, date_paid and date_rec.
The date_paid column has occurrences that are separated by 7 days. What
I am trying to do is get an average duration of payment for each
payment period. When I use the formula I wrote I get a -32 response,
i.e.
=AVERAGE((date_paid=A3)-(SUMPRODUCT((date_recA2)*(date_rec<=A3))))

Sheet 1

Date Received Date Paid
8/31/2005 9/14/2005
9/2/2005 9/14/2005
9/6/2005 9/14/2005
9/6/2005 9/21/2005
9/8/2005 9/21/2005
9/9/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/14/2005 9/21/2005
9/14/2005 9/21/2005
9/14/2005 9/21/2005
9/14/2005 9/21/2005
9/14/2005 9/21/2005

Sheet 2
A2 9/14/2005
A3 9/21/2005 -32
A4 9/28/2005
A5 10/5/2005
A6 10/12/2005
A7 10/19/2005
A8 10/26/2005

Average of days within a range
 

The array formula - entered with Ctrl-Shift-Enter

=AVERAGE(IF(date_paid=A3,date_paid-date_rec))

shoudl work, assuming that the named ranges are the same size...

HTH,
Bernie
MS Excel MVP


wrote in message oups.com...
I have a spreadsheet with two series of dates, date_paid and date_rec.
The date_paid column has occurrences that are separated by 7 days. What
I am trying to do is get an average duration of payment for each
payment period. When I use the formula I wrote I get a -32 response,
i.e.
=AVERAGE((date_paid=A3)-(SUMPRODUCT((date_recA2)*(date_rec<=A3))))

Sheet 1

Date Received Date Paid
8/31/2005 9/14/2005
9/2/2005 9/14/2005
9/6/2005 9/14/2005
9/6/2005 9/21/2005
9/8/2005 9/21/2005
9/9/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/13/2005 9/21/2005
9/14/2005 9/21/2005
9/14/2005 9/21/2005
9/14/2005 9/21/2005
9/14/2005 9/21/2005
9/14/2005 9/21/2005

Sheet 2
A2 9/14/2005
A3 9/21/2005 -32
A4 9/28/2005
A5 10/5/2005
A6 10/12/2005
A7 10/19/2005
A8 10/26/2005