Excel how to get an average for column for rows that meet multiple criteria
- by Jess
I would like to know the average days between open and close dates for an item with a close date in a particular month.
So from the below example in Jan 2013 items 2,5 and 6 were closed (Closed can be RESOLVED or CANCELLED status), each were open for 26, 9 and 6 days respectivly. So of the jobs that have a closed date in Jan 2013 (between 01/01/2013 and 13/02/13) they have an average open time (between open and close date) of 13.67 days to 2dp.
I have tried a few ways to get this to work and i think the issue I am having is with the AVERAGE function.
First time using a forum so apologies if my question is unclear.
Was unable to post image to have this comma seperated below
Item_ID,Open_Date,Status,Close_Date
1,1/06/2012,RESOLVED,16/07/2012
2,20/12/2012,RESOLVED,16/01/2013
3,2/01/2013,IN PROGRESS,
4,3/01/2013,CANCELLED,7/05/2013
5,3/01/2013,RESOLVED,12/01/2013
6,4/01/2013,RESOLVED,10/01/2013
7,1/02/2013,RESOLVED,15/02/2013
8,2/02/2013,OPEN,
9,7/02/2013,CANCELLED,26/02/2013
