Statistical FunctionsIntermediate

Report Support Reply Times with MEDIAN and a COUNTIF Hit Rate

A help desk's weekly report needs two numbers: how long a customer typically waits for a first reply, and how often the team hits its target. One slow ticket drags the average far from typical, so MEDIAN gives the honest middle, and COUNTIF with a criterion built from the target cell counts the tickets answered in time.

Task:

You run the weekly report for a small customer-support team. Each ticket from Monday is in A2:C11 with the minutes it waited for its first reply, and the team's target is in F1: every customer should hear back within 60 minutes. In F2, find the typical wait, the middle value of column C. In F3, work out the share of tickets answered within the target, as a fraction of all ten. A reply at exactly 60 minutes counts as on time, and F3 should read the target from F1 so it still works when the target changes.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

11 rows × 6 columns2 cells you fill in
ABCDEF
1TicketChannelMinutes to first replySLA target (minutes)60
2T-4811Email14Median reply time
3T-4812Chat38Share within target
4T-4813Chat9
5T-4814Email60
6T-4815Phone27
7T-4816Email61
8T-4817Chat19
9T-4818Email310
10T-4819Phone44
11T-4820Chat23
What this exercise teachesMay contain the answer

An average spreads every minute evenly across every ticket, so the one ticket that waited 310 minutes adds 25 minutes to everyone else's figure. AVERAGE(C2:C11) says 60.5, which reads as a team that misses its target on a typical day. It does not: eight customers in ten heard back within the hour. MEDIAN ignores how far out the extremes sit and returns the value in the middle of the sorted list. With ten tickets there are two middle values, 27 and 38, and MEDIAN averages those to 32.5, the wait a typical customer actually had. For the hit rate, COUNTIF needs its condition as text, which is why the operator goes in quotes and the cell joins it with &: "<="&F1 becomes "<=60" when the formula runs, and becomes "<=45" the day the target tightens, without anyone editing the formula. Writing "<=F1" inside the quotes compares every reply time to the letters F1 and counts nothing. The other trap is the operator itself. Ticket T-4814 was answered in exactly 60 minutes, which is within the target, so the test is <= and the answer is 8 of 10, or 0.8. A plain < quietly drops it and reports 70%. Dividing by COUNT(C2:C11) rather than typing 10 keeps the rate right when next week has a different number of tickets.