Hi, i was trying create a formula for average Turnaround time (TAT) for Ticket Type of "Bug". Once we establish that i should be able to replicate that for the other request types. Attached is a sample file.
Thank you!!
Hi, i was trying create a formula for average Turnaround time (TAT) for Ticket Type of "Bug". Once we establish that i should be able to replicate that for the other request types. Attached is a sample file.
Thank you!!
You dont have sufficient data for a turnaround time as you only have the target delivery date and the actual delivery date... you could calculate the average deviation from the requested date but not a true turnaround time as for that you'd need the request date (unless its just a case of unclear labelling).
I kind of agree with Sandtree, your table labels the completion as TAT, but I would normally think of a "duration" (completion date-start date) for turn around time.
As far as the Excel programming goes, though, it probably doesn't matter what you mean by TAT as long as you know what you mean. Excel will calculate whatever average you tell it to and won't care. If this is a very small sample of what looks like it could be a much larger database, I might suggest using a pivot table for this. If this stays fairly small, then you can use the AVERAGEIFS() function (I think).
Pivot table -- Select source data -> Insert pivot table -> place "Type Type" and "Status" in the "row labels" field and "average of actual TAT" in the values field to get the "average TAT" (in this case, meaning average completion date) for the actual TAT values for each Type (filter/slicer out the other types if you don't want to see them). To get average duration (actual TAT-requested TAT) either add "average of requested TAT" to the pivot table then calculate average duration as actual TAT-requested TAT, or add that helper column to the source data and use the helper column as the average of field in the pivot table.
To use the AVERAGEIFS() function, enter the desired type and status and then enter the formula =AVERAGEIFS(E2:E15,B2:B15,"bug",C2:C15,"open") will return the "average of actual TAT" (again, meaning average completion date). If you are looking for duration, adjust to average the difference of actual TAT-Requested TAT.
Something like that should work for you, once you figure out exactly what you mean by "average turn around time".
Originally Posted by shg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks