Single average of entries per day

Question

I maintain a trouble ticket report... each ticket has it's own row and contains 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 how many tickets, on average, come in on a daily basis and are closed on a daily basis.

I want to display something like this...

TYPE | Daily Avg New | Daily Avg Closed

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

SW | 5 | 4

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

HW | 8 | 3

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

So, essentially, for the Avg New column, I need to subtotal the number or rows by Open Date, for each Type, and then take the average of the subtotals.

And, for the Avg Closed column, I need to subtotal only the rows that have an entry in the date closed column and then average those.

Answer 1

Hey Frank, there are a couple ways to go about this. You could either accomplish this with a pivot table or try writing it out with a fairly involved formula with functions COUNTIF and SUMIF, but still doable. Can you provide your spreadsheet? Thanks.