Compute single daily average in and single daily average out

I maintain a trouble ticket report... each new ticket gets it's own row containing, among other things, the date opened, the ticket type, and ultimately a date closed (blank means its still open).

I'm trying to create a formula that will show me the average number of new tickets that arrive on any given day and the average number of tickets that are closed on any given day.

I want to display a stat that looks like this...


TYPE | Avg New per Day | Avg Closed per day

--------------------------------------------------------------------

SW | 5 | 4

--------------------------------------------------------------------

HW | 8 | 3

--------------------------------------------------------------------


So, essentially, for the Avg New column on the SW row, I need to subtotal the number or rows in my worksheet by Open Date, where type=SW, and then take the average of the subtotals. Then do the same for the HW row/type.

And, for the Avg Closed column on the SW row, I need to subtotal only the rows that have an entry in the date closed column and type=SW, then average those subtotals. And, again, do the same for the HW row/type.

Can this be done with a formula?

Thank you!

Hi menem,

Each row represents a new trouble ticket and the essential info is the Open Date (when the issue was reported), the ticket Type (e.g. SW, HW, User Error, etc.),
and the Closed Date (which is blank if the ticket is not resolved).

Every row is included in the first calculation, which is the overall average number of new tickets received each day...

1) To get that we'd need to first filter on a Type, let's say SW for now.

2) Then get subtotals of how many rows have the same Open Date.

3) Then take an average of the Subtotals.

4) The result is the average number of SW Type tickets opened on a typical day.

The steps for the other calculation, the average number of tickets closed on a given day, are similar...

1) To get that we'd need to first filter on a Type, let's say SW again, but also rows where Closed Date is not blank.

2) Then get subtotals of how many rows have the same Closed Date.

3) Then take an average of the Subtotals.

4) The result is the average number of SW Type tickets closed on a typical day.

Let me know if that helps.

Thank you so much for looking at this!

Frank

I can't seem to get the file uploaded, menem; nothing happens when I click the attachment icon.

follow the yellow/orange bar at the top of the screen, the attachment icon doesn't work

Also what will you be counting as days? do you work weekends? you will need to give the expected result

Got it, davsth; thank you.
Counting calendars days including weekends... Open Friday, closed Monday = 3 days.

The Excel file has two worksheets; the stats page, where the calculation will go, and the individual ticket info, from whence the stats are derived.

The column in red on the stats page, Avg New Per Day, is the one I need a formula to compute. I also need one for the column next to it, Avg. Closed per day, but
if you give me the first column formula, I'm pretty sure I can modify it to work on the second.

To get the values that you see in red, I manually counted the occurrences per day, for each service, averaged them, and then populated the Stats (col J)... that's what
I need a formula to do.

I show the manually counted data in red on the Ticket sheet... just temporary entries to help explain.

Let me know if this helps. Thanks again!

davsth - just by way of clarification, the number of days elapsed is irrelevant for the calculation I need.

I'm trying to find the average number of tickets opened on a typical day, by each type.

e.g. for type AS, if there were 5 tickets opened 11/1, 10 on 11/2, and 15 on 11/3. The Avg/Day calculation would result in 10 for that type.
Hope that helps!

Thank you to you and menem for the assistance!

I put that formula into J3 and the result was 22, which is the total number or tickets opened for Type=AV.

That's a good start but what I need is the average per day, which in this case is 3.7.... In the sample spreadsheet, 22 AV tickets were opened on 6 days... 22/6=3.7 the average per day.

So this is where it started to get confusing for me... the formula needs to compute the total number of days in which AV tickets were opened AND the total number of tickets that were opened (which you computed); and then it needs to divide the ticket count by the number of days to get the average.

Ah, I was wondering why M1 was involved!

I'll give that a shot now but, after I wrote that last reply I had a bit of insight and came up with a formula that worked...
=COUNTIF(Tickets!D2:D37,"AV")/SUM(IF(FREQUENCY(Tickets!A2:A37,Tickets!A2:A37)>0,1))

Thanks for playing around with this menem; your willingness to help a total stranger is so very much appreciated! There may be hope for this world after all.

I think I over complicated how I was thinking about it because I was trying to use AVERAGE... once I described it to you and realized it was just more a simple division of the number or total rows by the number of row with a specific Type, I was able to come up with the formula.
Now I just need to tweak it to populate the other column; the average number of tickets closed per day.
Thanks again for the assist!

The alternative is to search for a count distinct function to get the denominator
=SUMPRODUCT(1/COUNTIF(Tickets!$A$2:$A$37,Tickets!$A$2:$A$37))

and then count the occurances as the numerator
=COUNTIFS(Tickets!D:D,A3

so the average is
=COUNTIFS(Tickets!D:D,A3)/SUMPRODUCT(1/COUNTIF(Tickets!$A$2:$A$37,Tickets!$A$2:$A$37))

Thank you, Davsth!