Erlang C Calculator Excel – Including Shrinkage

Erlang C Calculator – Free Excel Spreadsheet that calculates the number of staff required in a contact centre. Very accurate and includes shrinkage.

Click here to download the Erlang calculator.

Version 5.5 has been released – which contains some bug fixes.

This is a simple Excel spreadsheet tool that uses the Erlang C Formula and allows you to work out how many agents you will need.  You just need to enter your call volume and the service level that you would like to achieve.  The calculator then looks at the agent resource you would need to achieve the service level.

And because it is all done in an Excel spreadsheet, it makes it pretty easy for you to model different call volumes across the course of the day.  With an Excel Erlang Calculator you can build a simple or complex staffing model for the contact centre.

What’s new in Version 5.5 of the Erlang Calculator?

The main changes in Version 5.5 is a bug fix affecting calculations below 1 erlang of traffic intensity.

In Version 5.4  we added the ability to handle higher call volumes, addition of dynamic charts for the Day Planner and Expected Service Level charts – so you can add on extra rows.

In Version 5.1 we added Shrinkage into Call Centre Staffing Calculations. Shrinkage is a factor used in staffing calculations to take account of holiday, sickness and internal factors. For a full description of Shrinkage, read our article on How to Calculate Contact Centre Shrinkage.

Version 5.5

• Bug fix affecting calculations below 1 erlang of traffic intensity

Version 5.4

• Bug fix affecting calculations above 150 agents
• Amended shrinkage data entry validation from 0.000001 to 0 so can enter 0%

Version 5.3   Removed ErlangC function and replaced with Probcallwaits to remove number overload and match online calculator

Version 5.2

• Bug Fix – Fixed issue with error if period over 546 minutes – (Integer to Long in Excel VBA)
• Added warning that calculator only works up to 600 agents with advice to use online calculator for large agent sizes
  https://www.callcentrehelper.com/tools/erlang-calculator/
• Added links to other Call Centre Helper tools

Included in version 5.1

• Renamed and improved Erlang C Formulas
• Agents Required – This is the raw figure without Shrinkage
• Agents FTE Required – This is more accurate as it includes Shrinkage
• Service Level Calculation – Works out the service level for a given number of calls and agents
• Probability that a Call Waits – Works out the probability that a call waits for a given number of calls and agents
• How to use Tab with details of how the formulas work
• Help on function arguments by pressing the fx (Insert Function) button
• Agent Planner – Tab see at a glance the agents needed across the day – with graph
• Expected Service Level Planner Tab – show expected Service Level across the day – with graph
• Improved error checking to not allow values outside of expected limits
• Day Planner and Expected Service Level charts set dynamically – so you can add on extra rows
• Change of terminology from Call Duration to Average Handling Time
• Added in missing Help Details when you press the fx button
• Correction of typos

Click here to download the Erlang calculator.

How does the Erlang Calculator Work?

The Erlang Calculator uses four user-defined Erlang C based formulas. These are written in Visual Basic and are stored in the workbook using macros.  There is no need to install an add-in as the macro is stored in the worksheet.

1. Work out the raw number of agents required for a call volume

This uses the Excel Formula
=AgentsReq(Calls, Reporting_period, Average_Call_duration, Service_level_percent, Service_level_time)

For example =AgentsReq(B9,C9,D9,E9,F9)

Please note that this does not include any Shrinkage (holidays, training, meetings, etc.)

2. Resource Calculator. Work out the total number of agents (FTE) required for a call volume (including shrinkage)

This uses the Excel Formula
=AgentsFTE(Calls, Reporting_period, Average_Call_duration, Service_level_percent, Service_level_time, Shrinkage)

For example =AgentsFTE(B30,C30,D30,E30,F30,G30)

This formula is more accurate as it includes Shrinkage (holidays, training, meetings, etc.) and so gives a more realistic staffing requirement. For a full explanation of Shrinkage, read our article on How to calculate shrinkage

3. Work out the Predicted Service Level for a provided number of agents

This uses the Excel Formula
=ServiceLevel(Calls, Reporting_period, Average_Call_duration, Service_level_time, Agents)

For example =ServiceLevel(B45,C45,D45,E45,F45)

This formula works out the probability that a call is answered within the target time. In practical use this is the predicted service level.

Please note that this does not include any Shrinkage (holidays, training, meetings, etc.)

4. Work out the probability that a call queues

This uses the Excel Formula
=ProbCallWaits(Calls, Reporting_period, Average_Call_duration, Agents)

For example =ProbCallWaits(B60,C60,D60,E60)

This works out the probability that a call has to wait (queue)

Please note that this does not include any Shrinkage (holidays, training, meetings, etc.)

To get the Erlang Calculator to work you will need to have macros enabled on your computer.  

In Excel select Tools > Options > Security > Macro Security and then select Medium.

You can then “Enable macro” when you open the spreadsheet.

The calculator is based on Microsoft Excel with a bit of nifty software programming from Joanne Sparkes at Expedio Spreadsheets using a mathematical technique called Erlang Theory*. Jo has coded it from scratch and it seems intuitive to use.  We have upgraded it over the years as a result of user feedback.

Need to calculate the number of agents for webchat and emails?

We have also developed a Multi-Channel call centre calculator which can mix calls emails and webchat. It can also be used as a standalone calculator. It has a really nice visualisation that shows the contacts and the calls over time.

Click here for the Multi-Channel call email and web chat calculator.

What is Erlang?

Erlang Theory  is named after the Danish mathematician Agner Krarup Erlang (1 January 1878 – 3 February 1929) , who invented the fields of traffic engineering and queueing theory.

He published his Erlang C formula in 1917 and it has been widely used ever since.  The Erlang C formula (which is the one that this Erlang Calculator is based on) is the one used for working out the numbers of agents needed for a given call volume. There is another Erlang formula, Erlang B, which works out the number of telephone lines that you need for a given number of agents.

You are free to use the Free Erlang C Calculator in all of your projects, provided that you do not resell or distribute the calculator on the web.

The Erlang C model is used in all major call centre forecasting and workforce optimisation (WFO) and workforce management (WFM) solutions available today.

Terms and Conditions

Use of the Erlang Calculator is subject to our standard terms and conditions.

We also have an online Erlang C Calculator

If you prefer, we also have an online version available. Click for the online Erlang C call centre staffing calculator and here for the spreadsheet version.