Excel 2007 : AVERAGEIFS formula not working

**milliemoo** · 03-07-2012, 06:24 AM

Hello, I am trying to write a formula that calculates the average number of working days between a date in column Q and another date in column T, based on the following conditions:

The value in T falls within date range 02/01/2012 - 29/01/2012
The value in column Q is not blank
The value in column F is "Yes"

I have tried to write this using AVERAGEIFS but I don't know how to factor in the date condition for column T.....please can anyone assist?

Many Thanks

**Bob Phillips** · 03-07-2012, 06:55 AM

Try this array formula

=AVERAGE(IF(($T$2:$T$200<=--"2012-01-29")*($T$2:$T$200>=--"2012-01-02")*($Q$2:$Q$200<>"")*($F$2:$F$200="Yes"),$T$2:$T$200-$Q$2:$Q$200))

Commit with Ctrl-Shift-Enter, not just Enter.

**milliemoo** · 03-07-2012, 08:48 AM

Thank you, Bob....this doesn't appear to be working exactly as hoped...it isn't recognising that saturday and sunday are not working days. Does it need the NETWORKDAYS function in it somewhere?

**daddylonglegs** · 03-07-2012, 09:04 AM

Is T later than Q or vice versa?

**milliemoo** · 03-07-2012, 09:05 AM

Q occurs first.......it marks the start of an investigation. T is the date on which the investigation was completed.

**daddylonglegs** · 03-07-2012, 09:19 AM

Try this version of Bob's suggested formula

=AVERAGE(IF(($T$2:$T$200<=--"2012-01-29")*($T$2:$T$200>=--"2012-01-02")*($Q$2:$Q$200<>"")*($F$2:$F$200="Yes"),NETWORKDAYS(--$Q$2:$Q$200,--$T$2:$T$200)))

confirmed with CTRL+SHIFT+ENTER