Excel 2007 : Help (again) with AVERAGEIFS formula

Hello, I am trying to write two formulae that looks at start and finish dates of investigations and calculates both the average length of time an investigation took to complete and also what percentage of investigations were completed within target.

For the first formula:
The start date is in column Q
The finish date is in column T

The conditions are that the value in column T must be within the date range 02/01/2012 - 27/01/2012, the value in column F must be "Yes" and both columns Q and T must not be empty.

The part that is confusing me is adding in the date range for column T


For the other formula:
Start date is in column Q
Finish date is in column T
Whether or not target was hit (either yes or no) is in column U

I need to calculate what percentage of values in column Q were "Yes" but only if the value in column T is between 02/01/2012-27/01/2012.

Once again, it's this date range element that is foxing me.


Any help is very much appreciated.

Thank you

Try

=AVERAGEIFS(V2:V20,T2:T20,">=2012-01-02",T2:T20,"<=2012-01-27",U2:U20,"Yes")

Thank you, Bob....is this for the first part of my query? Where've you've put reference to column V, should this be column Q? Thank you for helping me!

Sorry, I made a bit of a mess of that.

Try this ARRAY version

=AVERAGE(IF((T2:T20>=--"2012-01-02")*(T2:T20<=--"2012-01-27")*(F2:F20="Yes"),T2:T20-Q2:Q20))

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

Thank you, Bob. So I've tried this and it has given me an answer. I presume this is the average number of days. Do you know how I would adapt this formula to answer the other part of my question? So, what percentage of values in column U are "Yes" if the date range is as before?

Thank you

I've just realised that the formula doesn't include the condition that it must only look at rows where both Q and T have a date in them....

Further

=AVERAGE(IF((Q2:Q20<>"")*(T2:T20<>"")*(T2:T20>=--"2012-01-02")*(T2:T20<=--"2012-01-27")*(F2:F20="Yes"),T2:T20-Q2:Q20))

Thank you so much for all your help, Bob. It works perfectly!

Do you also know how I would do the other part of my query regarding percentage of investigations completed on time?

So, I need to write a formula that says 'if there are values in Q and T, and the values in T fall within date range 02/01/2012 and 27/01/2012, what percentage of the values in column U are "Yes".

I appreciate your help with this....it's driving me nuts!

I tried to ignore you but you just wouldn't let me would you

I think this array formula gives you what you want


=SUM(($T$2:$T$20<>"")*($T$2:$T$20>=--"2012-01-02")*($T$2:$T$20<=--"2012-01-27")*($F$2:$F$20="Yes"))
/SUM(($T$2:$T$20<>"")*($T$2:$T$20>=--"2012-01-02")*($T$2:$T$20<=--"2012-01-27"))

Ha ha.....yes, I'm stalking you but it was worth it as this formula works! Thank you (finally)....