Cellwise Start free

Excel guide · 4 min read

How to calculate call abandonment rate in Excel

Quick answerAbandonment rate is abandoned calls divided by calls offered: =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.

RowABCDEFG
1DateHourOutcomeWait (sec)Short abandon under (sec)5
2Nov 9, 20269Answered12Answer target (sec)20
3Nov 9, 20269Abandoned3
4Nov 9, 20269Answered45
5Nov 9, 202610Abandoned64
6Nov 9, 202610Answered8
7Nov 9, 202610Abandoned38
8Nov 10, 20269Answered15
9Nov 10, 20269Abandoned2
10Nov 10, 202610Answered30
11Nov 10, 202610Answered6
12Nov 10, 202610Abandoned95
13Nov 10, 202611Answered18

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.

=COUNTIF(C2:C13,"Abandoned")/COUNTA(C2:C13)

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%.

=COUNTIFS(C2:C13,"Abandoned",D2:D13,">="&G1)/(COUNTA(C2:C13)-COUNTIFS(C2:C13,"Abandoned",D2:D13,"<"&G1))

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.

=COUNTIFS(B2:B13,10,C2:C13,"Abandoned",D2:D13,">="&$G$1)/(COUNTIFS(B2:B13,10)-COUNTIFS(B2:B13,10,C2:C13,"Abandoned",D2:D13,"<"&$G$1))

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.

=AVERAGEIFS(D2:D13,C2:C13,"Abandoned",D2:D13,">="&G1)

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.

=COUNTIFS(C2:C13,"Answered",D2:D13,"<="&G2)/(COUNTA(C2:C13)-COUNTIFS(C2:C13,"Abandoned",D2:D13,"<"&G1))

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

Related guides

Updated