Excel guide · 4 min read
How to calculate call abandonment rate in Excel
=COUNTIF(C2:C13,"Abandoned")/COUNTA(C2:C13). If you exclude short abandons (callers who hung up within a few seconds), remove them from both the top and the bottom: =COUNTIFS(C2:C13,"Abandoned",D2:D13,">="&G1)/(COUNTA(C2:C13)-COUNTIFS(C2:C13,"Abandoned",D2:D13,"<"&G1)).Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Date | Hour | Outcome | Wait (sec) | Short abandon under (sec) | 5 | |
| 2 | Nov 9, 2026 | 9 | Answered | 12 | Answer target (sec) | 20 | |
| 3 | Nov 9, 2026 | 9 | Abandoned | 3 | |||
| 4 | Nov 9, 2026 | 9 | Answered | 45 | |||
| 5 | Nov 9, 2026 | 10 | Abandoned | 64 | |||
| 6 | Nov 9, 2026 | 10 | Answered | 8 | |||
| 7 | Nov 9, 2026 | 10 | Abandoned | 38 | |||
| 8 | Nov 10, 2026 | 9 | Answered | 15 | |||
| 9 | Nov 10, 2026 | 9 | Abandoned | 2 | |||
| 10 | Nov 10, 2026 | 10 | Answered | 30 | |||
| 11 | Nov 10, 2026 | 10 | Answered | 6 | |||
| 12 | Nov 10, 2026 | 10 | Abandoned | 95 | |||
| 13 | Nov 10, 2026 | 11 | Answered | 18 |
Download this sheet as an Excel practice file (.xlsx)
The basic abandonment rate
Start from a call log with one row per call that reached the queue: date, hour, outcome (Answered or Abandoned) and how long the caller waited in seconds. Every row is a call offered, so the bottom of the fraction is just the row count.
In the sample, 5 of 12 offered calls were abandoned. This is a small slice of a help desk's worst morning, so the rates are high; the method is the same for 12 calls or 12,000.
Result: 0.41666666667 shows as 41.7% with Percent format
0.417: 41.7% of offered calls were abandoned, counting every hang-up.
Excluding short abandons, from top and bottom
Callers who hang up within a few seconds usually dialled the wrong number or changed their mind; no agent could have reached them. Many centers leave these out. Put the threshold in an input cell (G1 = 5 seconds) so everyone can see it and it's easy to change.
The trap is removing short abandons from the abandoned count but not from calls offered. That gives =COUNTIFS(C2:C13,"Abandoned",D2:D13,">="&G1)/COUNTA(C2:C13) = 25%, which flatters you: those calls are still sitting in the bottom of the fraction. Subtract them from both sides and you get 3 of 10, 30%.
Result: 0.3 shows as 30.0% with Percent format
0.3: 30% abandonment once the 2 short abandons are removed from both the abandoned count and calls offered.
Abandonment by day and by hour
Add the date to every COUNTIFS. For 9 November: =COUNTIFS(A2:A13,DATE(2026,11,9),C2:C13,"Abandoned",D2:D13,">="&$G$1)/(COUNTIFS(A2:A13,DATE(2026,11,9))-COUNTIFS(A2:A13,DATE(2026,11,9),C2:C13,"Abandoned",D2:D13,"<"&$G$1)) gives 40%; 10 November gives 20%. In a real summary, put the dates down a column and point at that cell instead of typing DATE.
By hour is where staffing problems show. Swap the date criteria for the Hour column (the hour each call arrived, from =HOUR(time) if your log has timestamps). The 9:00 hour loses no real callers; the 10:00 hour loses half of them. That's a scheduling answer: more people on the phones from 10.
Result: 0.5 shows as 50% with Percent format
0.5: in the 10:00 hour, half of the callers who waited more than 5 seconds gave up.
How long callers wait before giving up
AVERAGEIFS gives the average wait of abandoned calls, again leaving out short abandons: =AVERAGEIFS(D2:D13,C2:C13,"Abandoned",D2:D13,">="&G1). Compare it with the average wait of answered calls, =AVERAGEIFS(D2:D13,C2:C13,"Answered"), 19.1 seconds.
The three real abandons gave up after 38, 64 and 95 seconds. Answering within 30 seconds would have reached all three, and answered calls already wait 19 seconds on average, so the target is close. Use MEDIAN with FILTER in Excel 365 if a few very long waits distort the average.
Result: 65.666666667
65.67: callers who abandoned had waited about 66 seconds on average.
Use the same call base for service level
Service level (the share of calls answered within a target, G2 = 20 seconds) is usually reported next to abandonment. Both must divide by the same calls offered, or the two numbers can't be read together.
Dividing answered-within-20 by answered calls only gives =COUNTIFS(C2:C13,"Answered",D2:D13,"<="&G2)/COUNTIF(C2:C13,"Answered") = 71.4%, because every abandoned caller has vanished from the bottom. On the same base as the abandonment rate (offered minus short abandons) it's 5 of 10, 50%. Some centers use a different service level definition; whichever you use, write it down. How to calculate service level compares the four common definitions on one call log.
Result: 0.5 shows as 50% with Percent format
0.5: half of the real callers were answered within 20 seconds.
Common mistakes
Removing short abandons from the top only
Subtract them from calls offered too; otherwise the rate drops from 30% to a flattering 25%.
Hard-coding the threshold in every formula
Keep it in an input cell (G1) and reference it with ">="&$G$1.
Service level on answered calls only
Divide by the same calls offered as the abandonment rate, so abandoned callers count against you.
Wait times stored as text or as h:mm:ss
Convert to seconds first: a time value times 86400, or VALUE for numbers stored as text.
FAQ
What is the formula for call abandonment rate?
Abandoned calls / calls offered. In Excel: =COUNTIF(C2:C13,"Abandoned")/COUNTA(C2:C13), with one row per call and the outcome in column C. To report it alongside service level and handle time, the free call center KPI dashboard template does both from the same call log.
Should short abandoned calls be excluded?
Many centers exclude calls abandoned within a few seconds, because no agent could have answered them. If you do, remove them from both abandoned calls and calls offered, and keep the threshold in a visible input cell.
How do I calculate abandonment rate by hour in Excel?
Add a column with the hour each call arrived (=HOUR(A2) on a timestamp), then add the hour as a criteria pair to each COUNTIFS: =COUNTIFS(B:B,10,C:C,"Abandoned")/COUNTIFS(B:B,10).
My wait times are in mm:ss. How do I convert them to seconds?
If Excel sees them as times, multiply by 86400: =D2*86400. Check first that 1:30 means 1 minute 30 seconds in your data; typed by hand, Excel reads 1:30 as 1 hour 30 minutes.
Functions used
COUNTIFCounts cells that matchCOUNTIFSCounts with several conditionsCOUNTACounts non-empty cellsAVERAGEIFSAverages with several conditionsDATEBuilds a dateHOURHour of a time
Related guides
- How to calculate service level in Excel
- How to count by hour in Excel
- How to calculate average handle time (AHT) in Excel
- How to calculate SLA compliance in Excel
- How to calculate agent occupancy in Excel
- How to calculate backorder rate in Excel
- How to calculate first contact resolution (FCR) in Excel
Updated