Excel 2007 : Average number of days between two events

**CatherineCarey** · 04-16-2012, 10:07 AM

I'm looking for the average number of days between entry date and interview date during August 2011.

I filtered the data between 8/1/11 and 8/31/11 in Sheet3.

This formula returned #value error message:

=SUMPRODUCT(--NETWORKDAYS(Sheet3!A4:Sheet3!A2000,Sheet3!I4:Sheet3!I2000))

Suggestions?

Catherine

**Bob Phillips** · 04-16-2012, 10:16 AM

Catherine,

Just take one from the other and divide by the count

=SUMPRODUCT(--(Sheet3!A4:A2000-Sheet3!I4:I2000))/COUNT(A4:A2000)

unless you want to ignore weekends?

**Andrew-R** · 04-16-2012, 10:22 AM

You can't feed NETWORKDAYS a range, even inside a SUMPRODUCT.

However, this should work:

=SUMPRODUCT(NETWORKDAYS(OFFSET(Sheet3!A4,ROW(1:1997)-1,0),OFFSET(Sheet3!I4,ROW(1:1997)-1,0))

This will return the total number of days difference, so you'll still have to divide it by the number of dates to get an average.

**CatherineCarey** · 04-16-2012, 12:03 PM

The offset formula worked. Thank you.

Catherine

**daddylonglegs** · 04-16-2012, 05:54 PM

Originally Posted by CatherineCarey

I filtered the data between 8/1/11 and 8/31/11 in Sheet3.

...but all of the formulas suggested here will take into account all data in the range, whether visible or not. If you want to only take account of the August 2011 data then you'd need to do that by adding that criterion in to the formula or by using a formula that will only take account of the filtered data. The former is probably easier, e.g. to average workdays between those dates, when the first date is in August 2011 try this array formula

=AVERAGE(IF(TEXT(Sheet3!A4:A2000,"mmm-yy")="Aug-11",NETWORKDAYS(Sheet3!A4:A2000+0,Sheet3!I4:I2000+0)))

confirmed with CTRL+SHIFT+ENTER