An Erlang C calculator takes a handful of inputs and tells you how many agents need to be logged in and taking calls in each half hour to hit your service level. You can build one in Excel in about fifteen minutes, with no add-ins and no software budget. If you can write a formula and fill it down a column, you have everything you need. I thought the hard part of workforce planning was the maths. It isn’t. The maths fits in five cells. The hard part is trusting the inputs.
In my earlier post on Erlang C scheduling for call centres I explained why the formula matters and where it stops being useful. This is the hands-on version: the sheet, the formulas and a worked example you can check your own numbers against.
What does an Erlang C calculator need to know?
Five numbers. Everything else is calculated from them.
- Calls offered in the interval. We’ll use 120 calls in one half hour.
- Average handle time (AHT). 300 seconds, which is talk time plus hold plus after-call wrap-up. Leave the wrap-up out and every answer below comes out too low.
- Interval length. 1,800 seconds for a half hour.
- Target answer time. 20 seconds.
- Service level goal. 80%, so the target reads 80/20: 80% of calls answered within 20 seconds.
From those, the calculator works out the traffic in Erlangs, which is simply the amount of work arriving measured in hours of agent time per hour. Here that’s 120 calls multiplied by 300 seconds, divided by 1,800 seconds: 20 Erlangs. Put another way, the phones create 20 agents’ worth of work every half hour, and you will always need more than 20 people to handle it, because nobody can run at 100% busy and still answer quickly.
How do you build the Erlang C calculator in Excel?
Set the sheet out like this. Inputs go in column B, and the calculation block starts at row 10.
- B1 calls in the interval (120)
- B2 AHT in seconds (300)
- B3 interval in seconds (1800)
- B4 target answer time in seconds (20)
- B5 service level goal (0.80)
- B6 traffic in Erlangs:
=B1*B2/B3
In row 9 add the headings: Agents, Chance a call waits, Service level, Average speed of answer, Occupancy. Then fill in row 10:
- D10, agents:
=ROUNDUP($B$6,0)+1. This starts one above the traffic, because you can’t staff at or below it. In D11 type=D10+1and fill down about fifteen rows. - E10, chance a call has to wait:
=POISSON(D10,$B$6,FALSE)/(POISSON(D10,$B$6,FALSE)+(1-$B$6/D10)*POISSON(D10-1,$B$6,TRUE)). This is the Erlang C probability, written with Excel’s POISSON function so you never have to type a factorial. - F10, service level:
=1-E10*EXP(-(D10-$B$6)*$B$4/$B$2) - G10, average speed of answer in seconds:
=E10*$B$2/(D10-$B$6) - H10, occupancy:
=$B$6/D10
Copy row 10 down. Add a conditional format on column F that turns a cell green when it reaches B5, and the answer is the first green row. No lookup formula needed.
What does the worked example say?
Here are the middle rows of the block for 120 calls, 300 seconds, 80/20:
| Agents | Chance a call waits | Service level | Average speed of answer | Occupancy |
|---|---|---|---|---|
| 23 | 41.6% | 66.0% | 41.6 sec | 87.0% |
| 24 | 29.8% | 77.2% | 22.4 sec | 83.3% |
| 25 | 20.9% | 85.0% | 12.5 sec | 80.0% |
| 26 | 14.3% | 90.4% | 7.2 sec | 76.9% |
Twenty-five agents is the first row that clears 80%, at 85.0%. Twenty-four misses at 77.2%, and that one missing person is the difference between a queue that behaves and one that doesn’t. Notice what the curve does as you go down: dropping from 25 to 23 agents takes the service level from 85% to 66% and more than triples the average wait. The model punishes understaffing far harder than it rewards overstaffing, which is the first thing worth showing the person who signs off headcount.
Run a quiet interval through the same sheet. With 60 calls in the half hour and the same handle time, you need 14 agents, with 71.4% occupancy. The gap between 25 and 14 is the shape of your day, and the reason a flat roster never works.
How sensitive is the answer to your inputs?
This is the part most spreadsheets never show, so build it into yours. Change one input at a time and watch the staffing number move. Take the 120-call example at 25 agents and nudge it:
- Handle time up 10%, from 300 to 330 seconds: you need 27 agents, two more. Up 20%, to 360 seconds, you need 29.
- Call volume up 10%, from 120 to 132 calls: 27 agents again. Up 25%, to 150 calls, you need 30.
- Handle time down 10%, to 270 seconds: 23 agents, two fewer than the base case.
A 10% miss on either input costs you two people in that half hour. Across a ten-hour day that is not a rounding error, it is a different rota. Show me a team whose handle time drifts upward by thirty seconds over a few months and I’ll show you a team that is quietly short every afternoon and cannot work out why.
So do not type 300 into B2 once and forget it. Pull handle time from your phone system for the same weekday and the same half hour, and refresh it monthly. A single average across the whole day hides the fact that Monday mornings and Friday afternoons are different calls with different lengths. The calculator is only as honest as the number you give it.
What do you do with the answer?
The 25 is people logged in and taking calls. It isn’t the number of people on the roster. Before the sheet is any use for scheduling, you have to account for breaks, training, meetings, leave and sickness. Call that shrinkage. At 30% shrinkage you need 25 divided by 0.70, which is 36 people rostered for that half hour. At 25% shrinkage it’s 34. That gap is where most call centre schedules fall over, and it is the number I see people leave out first.
Repeat the block for every half hour of the day, line the answers up against your forecast and you have the staffing requirement for the whole shift. Then you can lay the rota over the top and look at where you’re over and where you’re short.
Where does an Erlang C calculator mislead you?
The model assumes a caller who waits forever. Real customers hang up, and the formula doesn’t know that. It also assumes every agent can take every call and that all calls are alike. As Call Centre Helper points out, feeding it calls that abandoned quickly can make it overestimate the agents you need, so strip out calls that hang up in the first few seconds before you count volume. It was also built for inbound calls, so don’t use it as is for email.
None of that makes the calculator wrong. It makes it a first estimate. The honest way to use it is to compare its predicted service level with what your phone system actually reported last week, interval by interval, and adjust until the two agree. If your sheet says 85% and the ACD report says 70% every Monday morning, your forecast or your handle time is wrong, and you’ve just found it for free. That is the same habit I described in tracking your KPIs properly: a number you never compare against reality is a guess with a formula wrapped around it.
How do you check the sheet before you trust it?
Three quick tests will catch most broken formulas:
- The service level must rise every time you add an agent. If it ever falls, a cell reference has slipped.
- Occupancy has to equal the traffic divided by the agents. With 25 agents that’s 80.0% exactly.
- For this worked example, 24 agents must show 77.2% and 23 must show 66.0%. If yours doesn’t, check that the POISSON call uses FALSE in the first place and TRUE in the second.
For a second opinion on the underlying formula, TechTarget’s Erlang C definition is a clear reference. The Incoming Calls Management Institute publishes plenty of material on forecasting and scheduling if you want to go deeper than a spreadsheet. Save your sheet as a template and the next step is making it respond to a changing forecast, which is where the real work starts.
What should you do this week?
Open a blank workbook and build the five inputs and the row 10 formulas. Run your busiest half hour from last Monday through it and compare the result with what your phone system reported. If the two agree within a few points, you have a planning tool. If they don’t, you have found out which input to chase, and that is worth more than any add-in.
Then run the three sensitivity cases above against your own numbers. Write the agent count for each on a sticky note and put it next to the rota. The next time someone says handle time is up a bit, you will know exactly what a bit costs.
Hutch Morzaria is a Director-level CX and Support Leadership professional with 19 years of experience building global support organizations across SaaS, Fintech, and enterprise technology. He has hired dozens of support and CX leaders across his career and holds ITIL Expert certification across V3 and V4.
