Erlang C formula in Excel: probability of waiting, service level and agents needed

What the Erlang C formula calculates

Erlang C, named after the Danish engineer A. K. Erlang, models a queue in which calls arrive at random, wait in one line until an agent is free and are answered in turn. It needs three inputs: the number of calls in an interval, the average handle time (AHT), which is talk time plus hold and after-call work, and the number of agents N on the phones. It returns Pw, the probability that a call has to wait at all.

Two planning numbers follow from Pw: the service level, the share of calls answered within a target time such as 20 seconds, and the average speed of answer (ASA). Workforce planners calculate them per 30- or 15-minute interval, because a daily average hides the peaks when callers actually wait.

Step 1: traffic intensity in Erlangs

Traffic intensity A = calls × AHT ÷ interval length, with the AHT and the interval in the same unit. In the example, 100 × 180 ÷ 1,800 = 10 Erlangs: ten agents would be busy for the whole half-hour if calls arrived perfectly evenly. In Excel: =B2*B4/B3.

The number of agents must be greater than A. With N = A or fewer, work arrives faster than it can be handled and the queue grows without limit, which is why the formula only works for N > A. In practice you need a margin above A, because calls arrive in random bunches.

Step 2: the Erlang C formula in Excel

With A Erlangs and N agents:

Pw = X ÷ (Σ A^k ÷ k! for k = 0 … N−1 + X), where X = A^N ÷ N! × N ÷ (N − A).

In Excel, put k = 0, 1, 2 … in a column and calculate each term once with =POWER($B$7,A10)/FACT(A10). The sum for k up to N−1 is then SUMIF($A$10:$A$23,"<"&A26,$B$10:$B$23), so one table serves every staffing option. For 11 agents the formula gives Pw = 68.21%, the standard textbook check value. Test your sheet on this case before you trust it with real data.

Worked example: 100 calls in 30 minutes, 180 seconds AHT

Yellow cells are inputs. Column B from row 10 holds the terms A^k ÷ k!, and rows 26–29 turn them into Pw, service level, ASA and occupancy for 11 to 14 agents. Row 30 picks the first N that meets the target in B6.

Worked example: 100 calls in 30 minutes, 180 seconds AHT

	A	B	C	D	E
1	Inputs (one 30-minute interval)	Value
2	Calls offered	100
3	Interval length (seconds)	1,800
4	Average handle time, AHT (seconds)	180
5	Target answer time T (seconds)	20
6	Target service level	80%
7	Traffic intensity A (Erlangs)	10.00
8
9	k	A^k ÷ k!
10	0	1.00
11	1	10.00
12	2	50.00
13	3	166.67
14	4	416.67
15	5	833.33
16	6	1,388.89
17	7	1,984.13
18	8	2,480.16
19	9	2,755.73
20	10	2,755.73
21	11	2,505.21
22	12	2,087.68
23	13	1,605.90
24
25	Agents N	Pw (Erlang C)	Service level	ASA (s)	Occupancy
26	11	68.21%	39.0%	122.8	90.9%
27	12	44.94%	64.0%	40.4	83.3%
28	13	28.53%	79.6%	17.1	76.9%
29	14	17.41%	88.8%	7.8	71.4%
30	Agents needed for the target service level				14

Textbook figures for one interval. Real staffing also needs an interval forecast, shrinkage and schedule constraints.

Step 3: service level and ASA

Service level = 1 − Pw × e^(−(N − A) × T ÷ AHT). In Excel: =1-B26*EXP(-(A26-$B$7)*$B$5/$B$4). Callers answered at once count as answered in time, and so do those whose wait ends within T.

ASA = Pw × AHT ÷ (N − A), averaged over all callers, including those who did not wait. With 11 agents the service level is only 39.0% and the ASA 122.8 seconds. Each extra agent changes this sharply: 12 agents give 64.0% and 40.4 seconds, 13 agents 79.6% and 17.1 seconds, 14 agents 88.8% and 7.8 seconds.

Step 4: the number of agents for 80/20

Staffing with Erlang C is a search: try N = A + 1, A + 2 … and take the first N whose service level reaches the target. =INDEX(A26:A29,MATCH(TRUE,C26:C29>=B6,0)) returns it, here 14 agents. Occupancy, A ÷ N, is then 71.4%, comfortably below the 85–90% at which agents get no break between calls.

These are agents on the phones. To find the staff to schedule, divide by (1 − shrinkage) for breaks, training, meetings and absence. With 30% shrinkage, 14 agents on the phones means 14 ÷ 0.7 = 20 agents scheduled for that half-hour.

Large call centers and the limits of Erlang C

FACT overflows above 170 (FACT(171) returns #NUM!), and POWER becomes huge long before that. For large teams use the Poisson shortcut in Excel: with P = POISSON.DIST(N,A,FALSE) and C = POISSON.DIST(N-1,A,TRUE), Pw = P × N/(N−A) ÷ (C + P × N/(N−A)). It gives the same result without factorials.

Erlang C assumes that nobody hangs up and that every caller waits as long as it takes. Real callers abandon, so Erlang C is slightly pessimistic and tends to recommend a few more agents than needed. For most planning that safety margin is acceptable. Models such as Erlang A include abandonment when it matters.

Common mistakes

• Mixing units. AHT in minutes with an interval in seconds gives a traffic intensity 60 times too small. Use seconds for both.
• Reading 1 − Pw as the service level. That is only the share answered at once. Callers who wait less than T also count.
• Staffing exactly A agents. With N = A the queue never clears. N must be above A, and a service level target usually needs several agents more.
• Planning from daily averages. Calls bunch at certain hours. Run Erlang C for every 30- or 15-minute interval.
• Forgetting shrinkage. Erlang C counts agents on the phones, not agents on the schedule.
• Using FACT for large teams. Above 170 agents FACT returns #NUM!. Use the POISSON.DIST form instead.