Cellwise Start free

Excel guide · 5 min read

How to calculate service level in Excel

Quick answerService level is the share of calls answered within a threshold, such as 80% in 20 seconds. From a call log with an outcome and a wait time in seconds: =COUNTIFS(outcome,"Answered",wait,"<="&threshold)/calls_offered. Many centers take short abandons (callers who hang up within a few seconds) out of calls offered first. The definition changes the result a lot, so write yours next to the number.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJK
1CallTimeOutcomeWait (s)HourThreshold (s)20HourService level
250019:02 AMAnswered89Short abandon under (s)590.5
350029:04 AMAnswered159Target0.8101
450039:07 AMAbandoned39
550049:11 AMAnswered429
650059:15 AMAnswered129
750069:19 AMAbandoned359
850079:26 AMAnswered199
950089:33 AMAnswered209
1050099:41 AMAnswered619
1150109:48 AMAbandoned279
1250119:55 AMAnswered339
13501210:04 AMAnswered510
14501310:12 AMAbandoned410
15501410:25 AMAnswered910
16501510:39 AMAnswered1410
17501610:51 AMAnswered1810

Download this sheet as an Excel practice file (.xlsx)

Service level from a call log

The sheet is one queue's calls from 9:00 to 11:00: 16 calls offered, 12 answered and 4 abandoned, with the wait in seconds in column D. The threshold (20 seconds) is in H1 and the short-abandon cutoff (5 seconds) in H2.

The top of the formula counts calls that were answered and waited 20 seconds or less: 9. The bottom is calls offered minus short abandons, callers who hung up in under 5 seconds, often a misdial, before any agent could have answered. Two calls (3 and 4 seconds) are short abandons, so the base is 14 and the service level is 9/14, 64.3%.

"<="&H1 joins the operator to the threshold cell. Writing "<=H1" inside the quotes compares with the text H1 and counts nothing.

=COUNTIFS(C2:C17,"Answered",D2:D17,"<="&H1)/(COUNTA(C2:C17)-COUNTIFS(C2:C17,"Abandoned",D2:D17,"<"&H2))

Result: 0.64285714286 shows as 64.3% with Percent format

64.3%: 9 of the 14 calls offered (excluding 2 short abandons) were answered within 20 seconds.

The four common definitions, side by side

There is no single standard formula. Centers, outsourcers and contracts use different ones, and on the same 16 calls they give very different numbers. HSTACK in Excel 365 shows them in one row:

All abandons in the bottom (9/16): 56.3%, the strictest, every caller who gave up counts against you. Answered calls only (9/12): 75%, which ignores abandons entirely and looks best when the queue is so slow that people hang up. Short abandons out (9/14): 64.3%, the version above. Abandons within the threshold count as met ((9+2)/16, since the 3 and 4-second abandons waited less than 20): 68.8%.

The spread is 56% to 75% from the same calls. Pick the definition your contract or management uses, write it next to the result, and never compare numbers built on different definitions. Answered-only is the one to be wary of: it can rise while customers are having a worse time. Report abandonment rate on the same base next to it.

=HSTACK(COUNTIFS(C2:C17,"Answered",D2:D17,"<="&H1)/COUNTA(C2:C17),COUNTIFS(C2:C17,"Answered",D2:D17,"<="&H1)/COUNTIF(C2:C17,"Answered"),COUNTIFS(C2:C17,"Answered",D2:D17,"<="&H1)/(COUNTA(C2:C17)-COUNTIFS(C2:C17,"Abandoned",D2:D17,"<"&H2)),COUNTIFS(D2:D17,"<="&H1)/COUNTA(C2:C17))

Result: spills 1 row × 4 columns

0.56250.750.642857142860.6875

0.5625, 0.75, 0.643 and 0.6875: 56.3%, 75%, 64.3% and 68.8% for the same 16 calls.

Within 20 seconds: <= or <?

Call 5008 was answered at exactly 20 seconds. With "<="&H1 it counts as within the threshold; with "<"&H1 it does not, and the service level drops from 64.3% to 57.1%. On a real day with hundreds of calls, the calls answered at exactly the threshold second can move the result by a point or more.

Phone systems differ on whether "within 20 seconds" includes the 20th second, so check how yours reports it and use the same operator so your Excel figure matches the system report. If your export gives wait time as h:mm:ss instead of seconds, multiply by 86400 to get seconds, or compare with H1/86400.

=COUNTIFS(C2:C17,"Answered",D2:D17,"<"&H1)/(COUNTA(C2:C17)-COUNTIFS(C2:C17,"Abandoned",D2:D17,"<"&H2))

Result: 0.57142857143 shows as 57.1% with Percent format

57.1% with "<": the call answered at exactly 20 seconds no longer counts.

Service level by interval

Column E holds =HOUR(B2), so COUNTIFS can filter by hour like any other column. K2 and K3 repeat the main formula with one more condition, the hour in J2 or J3: 50% for 9:00 and 100% for 10:00. COUNTIFS cannot take HOUR(B2:B17) directly as a range, so either add the helper column or switch to SUMPRODUCT: =SUMPRODUCT((HOUR(B2:B17)=9)*(C2:C17="Answered")*(D2:D17<=H1)) counts the 5 calls answered within 20 seconds in the 9:00 hour.

For half-hour intervals, use =FLOOR(B2,TIME(0,30,0)) in the helper column instead of HOUR, and match against times such as 9:00 and 9:30. Interval results are what workforce planners use to see when the queue was short of agents.

=K2

Result: 0.5 shows as 50% with Percent format

50% for the 9:00 hour: 5 of the 10 calls offered (excluding a short abandon) were answered within 20 seconds.

The daily figure is total over total, not the average of intervals

The 9:00 hour had 10 calls in the base and missed badly; the 10:00 hour had 4 and hit 100%. =AVERAGE(K2:K3) says 75%, close to an 80/20 target, but customers had a 64.3% day: 9 calls within 20 seconds out of 14. Averaging gives the quiet hour as much weight as the busy one, which flatters the day exactly when the busy hours go wrong.

Always build the daily, weekly or monthly figure from the counts: sum the calls within threshold, sum the calls offered, divide. The call centers hub shows the same rule on a half-hour interval report, and the same applies to average handle time and occupancy.

=AVERAGE(K2:K3)

Result: 0.75 shows as 75% with Percent format

75%, which overstates the day. The correct daily service level is 64.3%.

Against target, and average speed of answer next to it

Compare with the target in H3 and say by how much it was missed, in percentage points: =LET(sl,service_level_formula,IF(sl>=H3,"Met","Missed by "&TEXT(H3-sl,"0.0%"))) returns "Missed by 15.7%" here, which reads as 15.7 points. To meet 80% on a base of 14 calls, 12 must be answered within 20 seconds, so this queue was 3 calls short: =ROUNDUP(H3*14,0)-9.

Service level hides how long the slow calls waited. Average speed of answer (ASA) is the average wait of answered calls: =AVERAGEIFS(D2:D17,C2:C17,"Answered") gives 21.3 seconds. Two queues can share a 64% service level while one has a longest wait of 61 seconds (=MAXIFS(D2:D17,C2:C17,"Answered")) and the other several minutes. Report service level, ASA and the longest wait together.

=AVERAGEIFS(D2:D17,C2:C17,"Answered")

Result: 21.333333333

21.3 seconds average speed of answer across the 12 answered calls.

Common mistakes

Averaging interval service levels for the day

Busy and quiet intervals then count the same. Divide total calls within threshold by total calls offered.

Dividing by answered calls without saying so

Answered-only leaves abandoned callers out and can look good while service gets worse. State the definition, and report abandonment on the same base.

Wait times stored as h:mm:ss compared with 20

0:00:20 is stored as 0.00023 of a day, so every call looks "within 20". Multiply by 86400 for seconds, or compare with 20/86400.

Typing the threshold into the formula

Put 20 and the short-abandon cutoff in input cells, so a change to 80/30 is one edit and every formula agrees.

FAQ

What is the formula for service level in a call center?

Calls answered within the threshold divided by calls offered. In Excel: =COUNTIFS(outcome,"Answered",wait,"<="&20)/COUNTA(outcome). Many centers subtract short abandons from calls offered first; others divide by answered calls only. Use the definition your center or contract specifies.

What does 80/20 service level mean?

80% of calls answered within 20 seconds. The first number is the target percentage, the second the threshold in seconds. Other common targets are 80/30 and 90/15.

Should abandoned calls count in service level?

It depends on the definition you use. Counting all abandons is strictest; leaving out short abandons (under about 5 seconds) is common; answered-only ignores them. Pick one and keep it, so weeks compare.

How do I calculate service level from an interval report?

If the phone system exports calls answered within threshold per interval, divide that column by calls offered for each interval, and for the day use =SUM(answered_within)/SUM(offered), not the average of the interval percentages.

What is the difference between service level and ASA?

Service level is the share of calls answered within a threshold. Average speed of answer is the average wait of answered calls in seconds. Service level shows how many callers got a fast answer, ASA how long they waited on average.

Functions used

Related guides

Updated