A B C D
1 Ticket Opened Closed Status
2 12345 1/1/2011 Open
3 12346 1/2/2011 2/2/2011 Closed
4 12347 1/3/2011 2/3/2011 Closed
5 12348 1/4/2011 2/4/2011 Closed
6 12347 1/4/2011 2/3/2011 Closed
7 12349 1/5/2011 2/5/2011 Closed
Okay, I cant figure this one out and I am new to arrays...
I need a formula that picks the unique tickets, then averages the number of days the ticket was open, with the criteria that the ticket is Closed and is <= 2/3/2011 and puts the average is cell E1.
The system may have one ticket, but if we assign 5 units to a single ticket, a report shows all 5 in the report as individual rows. So even though there is a single ticket, taking the average of duplicates skews the information. I hope that explanation of the duplicates makes sense.
Thanks in advance.
So, when there are multiple rows with the same ticket #, you want to calculate your days open based on the first instance?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Yeah, its essentially the same ticket so it doesn't matter which it gets really as long as it ignores the other duplicate entries.
So, when you say "averages the days the ticket was open", you really mean counts the days the ticket was open, based on any one of the unique ticket IDs...right?
And, on the subject of those unique IDs...you really don't care which one? You don't want, say, the longest, or shortest?
Ticket 12345 might show up 5 times on the report but each entry will have the same open and closed dates. It won't matter which is used as long as it's used once in the average.
Okay, the use of "Open" in column C makes it a bit more complex. I put your date in A9 to make it more versitile (so the final formula refers to <=$A$9.
I also created a dummy column in F which you can later hide. In F2 dragged down;
=COUNTIF($A$2:$A2,A2) This points out your first occurance of each number.
Then in E1, entered as an array formula (CNTRL SHFT ENTER)
=SUMPRODUCT((IF(ISNUMBER($C$2:$C$7),($C$2:$C$7-$B$2:$B$7),"")),--($F$2:$F$7=1),--($C$2:$C$7<=$A$9))/SUMPRODUCT(--($F$2:$F$7=1),--($C$2:$C$7<=$A$9))
If entered properly, you'll see brackets around it {}
Does that work for you?
Last edited by ChemistB; 01-21-2011 at 04:42 PM.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
hm.. ok thanks. i was hoping to get something that was going to avoid adding extra columns and what. i hoped the array method would work out
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks