Interval between two dates with criteria

I need to find the average interval from the Assignment date to Completed date. Criteria of conditions of status code: Completed, Service Coodinator of William, Order type of New.


William Completed 17-Feb-10 New 3/1/2010
William Completed 17-Feb-10 New 3/5/2010
William Completed 17-Feb-10 New 4/2/2010
William Completed 16-Feb-10 Old 2/19/2010
William Completed 16-Feb-10 New 2/19/2010
Susan Completed 16-Feb-10 New 2/19/2010
William Completed 16-Feb-10 New 2/19/2010
William Completed 16-Feb-10 New 2/19/2010
William Completed 16-Feb-10 New 2/19/2010

Okay here is a rookie stab at it

Confirm formula with Ctrl+Shift+Enter

If entered correctly the formula will be encased with {}

=AVERAGE(IF((A1:A7="William")*(B1:B7="Completed")*(D1:D7="New"),F1:F7))

Adjust ranges to line up with your data

I added a helper column, column F and added the formula =E1-C1 to establish the interval for each row