Free 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.1 has been released.
This is a simple Excel spreadsheet tool that 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 capacity 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.
What’s new in Version 5.1 of the Erlang Calculator?
The main changes in Version 5.1 is the addition of dynamic charts for the Day Planner and Expected Service Level charts – so you can add on extra rows.
In Version 5.0 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.
Other enhancements included in Version 5.1
 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
Included in version 5.0
 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
Click here to download the Erlang calculator.
How does the Erlang Calculator Work?
The Erlang Calculator uses four userdefined Erlang C based formulas. These are written in Visual Basic and are stored in the workbook using macros.
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. 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.
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.
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.
In the event that you spot any errors, please leave us some information about them in the box below.

Now that’s neat!

Jo,
Erlang seems to work best at 1/4 or 1/2 hr intervals for forecasting accuracy.
As you get more and more granualr with the data you actually drill down too far and end up in the unfortunate situation of actually trying to look too closely at the ErlangPoisson distribution.
For a pure scheduling layout I’m not sure you want to get to scheduling breaks on the 5 or 10 minute level anyway, that would depend on your staff breaking at *exactly* the correct time. With 1/4 hr or even better 1/2 hr schedules you have a little flexability, getting too involved means you are constantly manmanaging breaks and fire fighting as the adherence to rota slips.
Personally I feel it’s over engineering the situation.That said Jonty’s suggestion will work, you do however need to weight the calls toward the front of the 1/2 or 1/4hr to allow for human nature in play (People tend to call just after the hour or half hour, and to a lesser extent after the 1/4’s.
Regards
DaveA

i need erlang for contact center for forcasting tools.

Amel
Simply download the Erlang Calculator and you should be able to start making your own forecasting spreadsheets.

I used the excel and it is very useful,but i faced a problem.When i tried to caculate how many agents I need to acheive service level 99.9% and the incoming call rate is only one call and the average call duration is 150 seconds,and the target answer time is 20 seconds, so please feed me back as it is logid if you have two agents and there is only one incoming call,the service level should be 100%.
So please I will appreciate your help in clarifying that to me. 
Mahmoud.
One of the causes may be rounding error, the other is that the P.M.F of the POISSON / ERLANG sort are VERY unlikely to give a certainty. The rounding error would be my first choice. The function will say OK I have one call of X duration in Y frame so I need Z staff to achieve the required service level. However, there is ALWAYS some uncertanty.
As a rule of thumb anyone involved in Statistical / probability is reluctant to EVER say anything is either certain or will never happen, I mean, New Kids on the Block coming back. What’s the probaility of that?
Below is a breakdown I wrote for elsewhere.
Erlang being a standard adaptation of the Poisson process is a discrete probability distribution and works on events being independant. It models the P 0.95 for a single call it will indicate two staff are required.
Generally it’s very good for medium to high volumes over a given time period. Anything more than 5 calls OR 15min talk in a 1/2 hr should be 90%+ accurate.
The caveat for the whole thing is..
It’s only as good as the data you’re using.
The old addage Garbage In = Garbage out still applies.
Erlang models given P < 1 that a call will come in within a given time period. 1/2hr being best 1/4 works too. Anything else is either too granualar or gross and the P.M.F (probability mass function ) breaks down. It all depends on HOW good you want the svc levels, it will always (in raw unmodified state) give a probability of a second call coming in, therefore even if P> 0.95 for a single call it will indicate two staff are required.
Hope it helps

What do you mean by the term “Target Answer Time”, can some one elaborate on this ?

Sameer.
Simply “Target Answer Time” is the time you wish
calls to be answered within.Normally 16 / 20 / 25 / 30 seconds.
This leads to the common short hand for service levels
of 80/20 or 85/16 so 80% within 20 seconds or 85% within
16 seconds etc..Hope it helps
DaveA

Thanks DaveA for your quick response,
I think that I got your point of view, for example if an Agent is required to answer a call within 3 rings, i.e. 4 seconds per ring, equallling with in 12 seconds. hence, my TAT (target answer time) will be 12 seceonds.
please confirm if i am right.
Thanks & Best Regards.
Sameer. 
Exactly.
The industry standards tend to be between 10 and
20 seconds.Regards
DaveA

Thanks a lot DaveA
Thanks & Best Regards.
Sameer. 
Is your calculator capable to calculate delay data i.e. number of callers waiting longer than x seconds?

Cornelius,
One of the problems you would have is that
Erlang, by it’s nature, is geared toward answering
calls.It actually works assuming the maximum time you are
prepared for a caller to wait.In theory you should be able to say that if you staff
correctly and pick a sensible target answer time callers
shouldn’t have to wait, however, we do know that this
isn’t the ideal world.Having recently been asked something similar and to calculate staffing for various abandon rates and it’s
not as easy as it seems.Not exacly what you’re after I know..
Regards
DaveA

Can you elaborate on what calculation the user defined function “agent no” uses?

Lou,
The UDF is the actual staff required calculation based
on call length and service level required.It actually loops incrementing the agent
number until the target is hit. The actual function
is driven by the Erlang UDF.If you have a look at the Vba in the background
it’s the last function in the Module.Hope it helps
DaveA

Hi Dave,
I have macro security disabled entirely and still can’t get the whole page to work. The daily half hour breakdown does not calculate.
I had built a very similar tool at my previous place of work, though it required the Erlang for Excel addin. Is that required here?
If so, are you able to email it to me?
Thanks for you help.
Scott 
Scott here again – not sure what did it, but after clicking away and back a few times, it worked.
Looks great. Thanks again.

Scott,
Can you let us know which version of Excel you’re using?
It sounds like there’s an issue with the automatic calculations.
I’ve not seen this with the calculator, however, I have
seen it with other UDF’s in, paradoxically, Excel97 and 2007. ALthough not 2002 et al…Thanks
DaveA

In the first box “Calculate the number of agents required to reach an agreed service level:”, do you put in the average half hourly volume per day or the max half hourly volume? Good calculator though.

Also, does that include shrinkage?

Do you have the staff utilasation excel formula

OK,
Been away for a while….
Claire: It will give you agents required on a raw basis,
ie: this is what you need staffed at any given interval. Shrinkage is a bit too dependant on local variables to be factored into such as thisPeter: Utilistaion% is :
(Talk + After Call Work + Shrinkage factor)/Login *100
I would suggest a productivity figure of 6570 % to aim for.Regards all…
DaveA

How would I use this tool to get an annual FTE required?

Agents are supposed to login for 9 hrs but due to any particular reason (eg CAB delay), Agent is logging only for 8 hrs. how can i calculate it on excel file, please suggest.

If I have my staffing and workload is it possible to use erlang to calculate what 80% of customers will be answered in? (ie not using a service level target)

I would like to paste the calculator into an existing spreadsheet that has my call volume data. When I paste it though the “Numner of Agents Required” box shows #/Name (and the formula itself is gone.
I am able to downlaod and sucessfully use the calculator in its downloaded Excel file, jsut have the problem after the paste (I checked and Macros are enabled on the paste to file)Thank you
Gregg Solomon 
Gregg
Jo has had a look through the code and has come up with the following pointers.
The reason they have disappeared is because all the formulae are based on user defined functions (rather than built in Excel functions). These do not show in the visible macros but can only be seen by going to Tools, Macros, Visual Basic Editor and then looking in Module 1. They won’t be transferred in a standard copy and paste of the spreadsheet.
It should be possible to manually do a copy and paste of the text in module 1 to module 1 of the visual basic editor in his new spreadsheet and then the function “agentno” will work. Otherwise, depending on the volume of data in his other spreadsheet, it may be simpler to copy the data in to new sheets in the Erlang spreadsheet.
Jo

How do you enable Macros in excel version of 2007? I don’t seem to fidn it.

I have made all the changes and settign adjustments and then tried to enter the required inforamtion, nothing changes and i don’t get what i want. what else am i supposed to do?

Samuel,
A little more info would be useful…
HAve you managed to enable Macros?
Is it giving an error message?
Have you opened the whole file or just copied
it into another worksheet?let us know..

can you please explain me what formula did you use to get the required agents field? and the predicted service level? this would help me understand better how you was this done.. Thank you in advance,

I prefer using computer simulators as Erlang assumes certain important activities. Anyone knows where to download a computer simulator software in excel?

hi,
just wondering if there is a similar tool for nonvoice contact centers. specifically a calculator to determine “threshold” manpower limit for a fixed volume of documents to be processed. tx!

Something like Nuqleus will probably be what you’re
after, although that’s really a back office MI app.Regards
DaveA

tx for the tip DaveA.
however,are there simpler excelbased spreadsheets. will use it more for basic stuff, specifically determining “threshold” levels for manpower before SLA production volume suffers. tx!

Hi, can some one please tell me that the “Average Call Time” refers to “Average TALK time” or “Average HANDLE time” ?

Nauman,
It refers to the total time required to handle the call
including any after call work required.Regards
DaveA

Shrinkage isn’t factored into the model, right?

Nathan,
See my comments above on the 6th May
regarding shrinkage as a local variable.Regards

Thanks, this an excellent tool. For those wishing to convert to 15 minute intervals, you can do this by changing the denominator in column G from 1800 to 900, then inputting data in 15minute increments.

Hi ,
I have downloaded your calculator which is working properly. Thanks a lot .
I have a one question.
Why did you divide =I14 / 1800
why we are using 1800.
Could you explain . please 
Kivanc,
Simply put, it’s the number of seconds in half an hour.
Regards
DaveA

Can anyone give an explanation of what the ‘Traffic Intensity (Erlang)’ means. If it’s increased, the performance level falls. Thanks!

David,
Essentially and ‘Erlang’ is a descriptive unit of measurement in the same way as a Newton or Watt. It describes the loading on a telephony system.
Effectivly it is Time available on a system so…
Time interval / (Calls*average duration)
3 calls of 20 minutes in an hour would be one Erlang
(3*20)/603 calls of 15 minutes in an hour would be 0.75 of an Erlang
(3*15)/6015 calls of 23 minutes in 2 hours would be 2.85 Erlangs
Anything greater than 1 means there is more traffic than one line can take.
To answer your main question, if demang goes up service goes down fr the same number of staff. You do need to remember that with Traffic intensity, it is an average over a period of time and not indicitive of any queuing behaviour in teh system.
Hope it helps.
DaveA

Hi,
Please provide me with the exact formula for calculating Shrinkage % and Present % .
Thnaks !
Ravi Verma 
Hi Dave,
Is Erlang suitable to estimate agents for email queues, where the SLA may be 2 hours? So in the calculator if the SLA is set to 95% in 6480 seconds (7200 sec – 720 to complete the effort to respond to the email) would it produce a viable result?
Would you recommend another approach?
Thanks 
When using this model am I correct in assuming this is core number of bums on seats not allowing for shrinkage thanks

Marie,
Spot on 🙂
Ravi,
You can only factor shrinkage in IF you know what it
is for your particular centre, there is no standard
formula I’m afraid.Mit,
It will work IF you fator the queue / Svc time the way
you suggested, although a more ‘analogue’ system may
work as well in this situation.Regards to all..

Does the incoming call rate include or exclude abandoned calls?
Thanks for your excellent tool!

Hi,
This is a great calculator.
However, can some1 please inform me the formula to calculate FTE with AHT, Service Level, and Shrinkage.For example:
Our company operates MonFri, 8am8pm;
Average calls per day is 2000;
Service Level is 85%
Shrinkage set to 35%
AHT is 310.
How can I calculate the FTE now??
Please help.THANKS HEAPS,
Weera

Hi,
how do I calculate ‘Calls per hour’,required for ‘SLA’ calculation,if I have monthly call volumes.I am trying to figure out what will be my Service level for the month.

I just downloaded this calculator it seems very interesting and most of my difficulties related to center staffing seems to come to an end today….. Lets see how effectively it will work….

Hi,
Can you define me WFM service level formula please.
Regards,
Abdul 
Abdul,
Basically the simplest formula is:
Of x calls answered what percentage were
answered within the target time of y seconds.Regards
DaveA

Hello
Is Erlang suitable to estimate agents for email queues, where the SLA may be 2 hours? So in the calculator if the SLA is set to 100% in 7200 seconds, would it produce a viable result?
Would you recommend another approach?
Regards – Sayan

Do you know what happens to Erlang if you have a hybrid of two different call types with different qualities of service? For instance, we have all of the regular help desk calls that have to be serviced at 90% answered in <45 seconds, but a subset of same call center workforce also handles another split/skill with service level requirements at 95% at < 30 seconds. I don’t think that that would be an additive thing. How would you figure the right staffing? In other words, can erlang handle 2 service levels for 1 call center and calculate total required staffing? Thanks.

Hi!
I have very low number of incoming calls per hour, around 34, and between 200500 seconds of handling time per call.
– Can I use the Erlang calculator for these values, or should I rather calculate on number of agents needed ideally without taking service levels into account?
BR,
Camilo from Sweden 
How do I find the VB code for this tool?

Hi Dave,
This tool is pretty good. Assuming we have multiple language skill sets with varied call volumes, do we have to assess the headcount required for each language skill set as different entity. I have to support 9 languages
Thanks,
Deepak 
Hi Deepak
Your answer will depend on the degree to which your staff are multilingual. If you have 9 languages and everyone is multilingual, then you can use erlang on one worktype/skilltype.
If you have a more complex situation of some people being single and some multiskilled, then simulation is the best route, but more complicated.
Failing that you can estimate how may skill groups you have, say 3 if in your case the average number of languages spoken was three. And then break your call demand into three, and run each through an erlang model. Not perfect, but can be quicker and simpler than full on simulation.
Hope that makes sense. Let me know if it does not.
Best regards, Steve 
Thank you Steve for the suggestion. I am awaiting information on the skill groups so that I can estimate the headcount. Do i have to add any buffer head count or the Erlong model would take care of it. Also the support is not 24×7 support. The support window is just for 9 hours window.

Good calculator.
If you wish to use the model by entering higher level numbers – such as calls received per hour or calls received per day, you can change cells D14 and E14 by switching 1800 with the number of seconds in your timeframe (per hour would be 3600).
Right?

I was looking for similar tool which I can use to plan my resources for back office process. Can anybody help me with this tool? Where I can find this tool? I’ve prepared the planner which includes the incoming volume and RPH (Rate Per Hour) but I was looking for some standard format.

Hello.
Does anybody know to estimate the call volume demand when you implement a new system – ERP (SAP , Oracle, JDE) for example 3 calls a month per user ?
Thanks. 
Hi
How can i please change the VBA function for agentno so that it returns decimals?Thanks

Is there any way to get this to work based on call answer rate rather than service level?
Cheers

Hi,
Read above you cannot use calcualtor to forecast split service levels but can you forecast and include a max call wait time as an output? Basically I need to calculate FTE on GoS 80/20 on 400 calls but I also need to factor in a new SLA of 100/60 on the same calls which is where we current fail to focus attention by only targeting calls still in our SLA. Any ideas?
Thanks,
ChrisO 
This is heavensent! I’ve been searching for a free Erlang C calculator and I only found out about this just now. I’m not yet done using it but will give my feedback/input as soon as I get my results.
I’ll post this to my blog as well to help my coWFAs. 
Hello, I’m ok using the calculator, I just noticed one formula. The one above the Incoming call rate at the left hand box. it says D14/1800. I’m just wondering where you got the 1800? Is that variable or fixed? Thanks for your help!

1800 is the number of seconds in half an hour, this is the interval the calculator works to.
Regards

This calculator is fantastic for identifing interval requirements across the day. I’ve read many threads in quite a few forums and found several formulas for working out required FTE and I’m almost there but I’d really like the excel formula for working out required FTE for day/week/mth call volumes.
Using the following data day/wk/mth e.g.
Calls day/wk/mth – 1198/6049/23058
AHT – 440 secs
Occ – 82%
SLA – 85/60
Shrinkage – 22%
Hrs worked/day – 7.5Looking forward to any assistance.
Regards,
Nigel 
hy
i must calculate input time of the random time .
your help me
i am iranian i dont english
thank you 
Hi Dave,
I am a WFM Analyst here in Dubai and I’ve been a fan of Joanne’s VB codes for the past year.
I’ve been wondering though what is the relationship between Intensity,ASA,AHT,and SLA on the below formula..=AgentNo(Intensity,ASA,AHT,SLA)
I love spreadsheets but i never had an experience in writing and understanding VB codes… once i see “Dim agents As Long” etc i loose my interest and close the file.
Is there anyway you can explain
=AgentNo(Intensity,ASA,AHT,SLA)in a layman’s term like using M.D.A.S.. I can understand Algebra and Trigonometry more than VB codes.. =)Thanks,
Drebb

Hi
The erlang calculator looks great and is working however, I am looking for a calculator to help forecast weather related calls. These calls are not predictable – would this calculator be of any benefit to me. I am having great diffilculty in producing a forecasting model that is within 8/10% accurate.Many thanks
LM

LM,
It really depends on the kind of lead time you have, and, your trigger events are they sudden or do you have any lead time at all?.
A slightly more sophisticted model may be of more use, as, you’ll need to factor the sudden uplifts.
That said, if you are weather dependent you’re constrained by triggers, so, can probably build a lookup of…
Event A = xxx calls with a distibution of yyy minutes / hours.Event B = ppp calls in qqq minutes / hours.
You can then factor that into the model and look at the staffing requirements for the sopt intervals. That’s something this calculator can do.
Regards
DaveA

The below Erlang function doesnt match with the Actual ASA output with the given parameter
For example if i want to validate the actual ASA delivered with the given Agents, calls and AHT i get different output
ASA(Agents,CallsPerHour,AHT) 
YR,
Can you clarify a bit more, not sure what
you’re driving at.When you say you go to validate, how do you validate?
Regards

Hi Dave
Many thanks for the advice on 30th March – sorry I have not replied earlier. I will certainly look at this and let you know how we get on.
Regards
LM 
Hi Dave,
If I wanted to use the total volumne of calls instead, would I just increase D14/1800 to the number of seconds for the week?

Dave,
I know this has been asked a couple of times but havn’t been able to see the answer… can this be used on for a daily, weekly or monthly call volume requirement?
Thanks,
DSL,
SATX 
Sorry for the delay,
I’ll try and answer both questions at once…Theoretically you can upscale the interval data, but, this destroys the granularity and really attacks one of the strengths of the Erlang model.
Uplifting to a one hour interval will work fine, however, when looking at at a week looking at 216000 seconds based on a 12/5 as your forecast plan will only ever give you a ballpark figure asthe daily /interval trends are washed out.
This error gets larger as you increase the interval, with a monthly you’ll lose the weekly trend throughout the month.
What that IS good for that tough is looking at longer term planning and advance scheduling of major training etc… What you can do is look at the variance, match it back and then see how big a gap is developing. You can then apply that to say the monthly plan and look at available days etc…
Hope that helps, drop a line back if you’d like anything more.
Regards
DaveA

Thanks DaveA that certainly helps and is logical. I have a quick question about the agent planner – this is based staff (without shrinkage) as bums in seats… Once you add our 40% shrinkage in to get our rostered staff, what do you propose as been the best way to calculate total staffing needs over a 15/5 (40 hr week with 5*8 and 4*10 schedules)?

what type of occupance is calculated in or is it simply the number of poeple you need at any given interval to be available to take calls? I want to run it with 82% occupance and want to make sure I’m not under estimating my staff.
Thanks

Hi Dave,
I just wanted to say this is a great spreadsheet.
You are also very patient in answering all the questions above – very detailed! (even for repetitive ones)
Greatly appreciate your hard work!
Cheers
Jo 
Loving the calculator and I would love to able to use the formula in a spreadsheet we have that tracks call volumes and plots this against a planned rota to highlight any potential gaps in our resource levels, but I cannot get excel to recognise the ‘agentno’ in the formula. Can you let me know which addin I need to be able to get this to work.
thanks in advance for your help

The Calculator is accurate, however i need to know the exact calculation of formula “agentno” & “Erlangc” used in the excel sheet.
Regards,
Nilesh T
+91 98 234 234 15 
I am trying to find a fomula that will help to calculate workload based AHT or OPHA (Outlets Processed per Hour (Average). It’s for an Outbound environment. We know how many calls we have for the day,how many agents are working each hour,AHT and Average Dials. Just wondering if anyone has experience with Outbound Daily Forecasting

Hi – has there been an update so we can calculate the agent needs per 15 minutes? I am currently trying to fix the call volme planning / forecasting for my contact centre and I am slightly struggling. I need accurate figures per 15 minutes. My idea is the following:
Pull Volume figures (Calls offered) and Handling Time (AHT) over the last 3 years. This data will help my forecasting (simple calculation of trend, should be fairly accurate as far as I could tell). I will hopefully get figures for Calls Offered / 15 minuntes including AHT separately. Based on this info combined with Service Level I should then hopefully get correct staffing requirements which will help me plan the days and weeks ahead.
Any tips or input is very welcome. Thank you for having put this Erlang C Calculator online, it is a great help!
Regards, Andy

Hey – I’m trying to build a staffing model and I’m struggling – does anyone have any good examples of how to go about doing this. I understand that the calculater will ultimately tell me how many people I need depending on the variable inputs that I use; however, I want to take a step further and use the calculator to show me what my expected rates should be etc. etc. – just want to build something that has some functionality. I’m also looking to show this in 15min intervels. Any help would be greatly appreciated. Thanks!

Hi Need a simple calculation to know the number of agents required to answer 100 calls with an AHT of 12mins and shift of 9 hrs.

OK,
The calculator will do that if you know the distribution of calls.
Regards
DaveA

Dave,
You are a bit of a hero for both the calculator and your time to answer everyone’s questions.Could you answer one more?
I am struggling with the Erlang cell.
I read your reply above, but can’t get it right in my head.Itis set at ’20’ in the calculator…. Can you explain in layman’s terms what I should put in that cell please!??
Can’t thank you enough.
If you take donations, I’ll happily forward one. 
Hi
When I created a new module in my worksheet, and agentno function calculated there 18 agents for 19 calls in 15 min, AHT=121, target answer time=10, SL=77. But in native file same calculation shows 7 agents. How can it happen?
Best Regards, 
OK….
Couple of answers(ish)
#1 Cumshaud.
I can’t really support anything outside the main download, I’d guess you’ve a reference wrong somewhere though. If it’s working fine in the native code can’t you leave it there?#2 Allan.
Which cell are you referring to? The cell set to 20 as default is the target answer time in seconds, or, am I missing something?Regards
DaveA

Thanks for this calculator! Is there a way to vary an agent’s “productivity?” (i.e., the amount of his/her an agent would really be able to take calls. This calculation would include biobreaks, time to get a drink of water, etc. Also, to add “Wrapup” time, would I ismply increment the call length to include, for exampole, 15 seconds between calls?

Ok,
Wrap should really be included in the AHT for the call not just talk, so, that’s
not an additional factor it’s part of the requirement.From a calculator like this you need to factor your
‘shrinkage’, IOT (Indirect operational time),
productivity or what ever you’re calling it as an
additional factor above and beyond what the
calculator says.This just gives you the ‘raw’ number required at any
given interval.Regards
DaveA

Thank you!

Hi dave,
You still have not answered how you obtained the number of agents…
Can you explain it in simple math.
Thanks

Sorry,
Not sure what you mean.
I can’t find a previous query from you.
If you mean in general how do we find the number of agents, it’s based on the Erlang C distribution calculations which in turn are a derivation of the Poisson distribution.
The Wikipedia page is a good place to start.
http://en.wikipedia.org/wiki/Erlang_(unit)
As always with Wikipedia, don’t trust it as the only source! Look at the references and go from there.
The basic theory in obtaining the number of agents is:
1) Look at the historical call arrival and handle time.
2) Use the probability model to calculate Agents required based on the base data.
Unfortunately it’s not a simple calculation, the upside is there are plenty of tools, like this one, available to help.
Regards
DaveA

would u pls tell me if landed calls 500000 and AHT(avg handling time)500 wht should be the manpower forcasting with 40% shrinkage? productive hour:8 hr

Nazia
That’s what the calculator is for:)
DaveA

Hey!
Can I calculate staff breaks with this program anyhow? If not, can anyone tell me how can I calculate staff breaks in excel?
Thanks

Hi, can this be used to calculate agents required to do email servicing? Can it also show productivity %?
Thanks!

Just a quick question if I may,
when inputting available agents into the erlang form, doe’s it allow for breaks or would I need to adjust accordingly, silly question I know.
Your help in this matter is much appreciated.
Regards
Jez

Hello,
Thank you for this awesome calculator. My CSR’s avergage about 19min / call, I have SLA set at 120 seconds, on a busy monday, we receive about 800 – 900 calls, is 120 seconds too low or should I increase to 180 seconds? My dept ends up abandoning about 20% of incoming calls.

Quick question. The calculator provides required heads per interval. How do I convert that into required hours per interval?
Thanks
Kathy 
Kathy,
A head will equal 1800 seconds, so…
Multiply heads by 1800 then divide by 3600…
Regards

@Fidel, Have you work with the inefficiency of your team?
My dpt receive at least 1200 calls in a single monday, with a SLA 90/30 and 20 min AHT and my SLA is up to 95% Abd rate is between 0.5% and 1%
Sometimes it’s not only to increase the number it’s to handle an efficient team.
Now also is important to know how many agents do you have and how many shrinkage you have abs level, that’s a huge analize to do!
@Dave! Thanks you so Much for create this tool, it’s really usefull , I was working with the oldie erlang97 but your tool it’s more accurate!
Thanks
Jk

Great tool. I see the question asked many times, but no answer…does this factor any intervals between calls, or is asumption that calls follow in immediate succession?? thanks.

Hi there,
Does this tool take into account shrinkage? It seems like it is determining agent requirements but I cant see where shrinkage is factored in??Cheers, Tim

OK:
Couple of answers..
Big Dave: No, Erlang assumes calls arrive and queue one after the other, so, effectivly a 0 interval.
Tim: It will give you agents required on a raw basis, ie: this is what you need staffed at any given interval. Shrinkage is a bit too dependant on local variables to be factored into such as this
General Reply regarding Open Office.
I HAVEN’T FORGOTTEN!
I’m still looking at this, however, a change in job, and country, has slowed it down.
It looks like a change in the way 3.2 handles UDF’s but the coding is a subtly different flavour from the MS VBA, I’m working on it..
Regards to all.
DaveA

Hi Dave A,
I am trying to calculate agent occupancy:
I have 25 agents spread over 5 shift schedules.
SL:80% answered within 30secs
AHT:360secs
Wrap:30 secs
Abandon:3%
Calls:800what other data that i need to come up so that i can properly calculate occupancy? hope you can help.

Hi Dave,
I have run the calculator for a week calculating FTE requirements for every intraday period and the results are far too low. My FTE requirements for the week amounted to 19 FTE (80%/30 seconds) as opposed to 27 FTE using an occupancy based formula with an occupancy target of 80%. I guess an 80/30 SL would not equate with 80% Occ???? I then did a sanity check to see what actual FTE was handling the workload and it was approx 27. Any ideas on how to amend the formula.Cheers, Tim

I have two sets of calls. 1. AHT is 230 seconds and 1. AHT is 425 seconds. I have two sets of associates answering different jobs. Example 1 2000 calls with AHT of 230 and 2. 14000 calls wit AHT of 425. 10 agents taking the lower AHT calls and 300 agents taking the 425 aht calls and remaining 230 aht calls that are not getting handled by the other agents. When I calculate by half hour the combination of calls, AHT, and Agents Erlang can not give me an accurate service leve. It does however give me the accurate staff shortage or overage but I may show I am short 15 associates and get an 85 service level when you know it is going to be more like a 55. do you know how I can calculate both?

Hello, This is a great tool but I’ve made some cosmetic changes that allow me to paste an excel export from my scheduling software onto a 2nd sheet and populates the orginal sheet, and sometimes when I paste it freezes and sometimes it doesn’t. Any idea why this could be happening?

Which will be the conversion factor from 1 Erl/Mbts with a vocoder EVRC, 9.6 Kbts (CDMA system) and a vocoder G729 16kbts (Wimax systems)
Rey 
OK,
Reply set….
1) Tim and Kivz..
This literally gives your fte demand, assuming the standard Erlang noblocking functionality.. Any occupancy figures need working out separately. Within the calculation it assumes an occupancy of 1 (ie:100%) for a preshrinkage calculation.
Within the code is a variable called ‘utilisation’
you can experiment with this, but, I’m not an advocate of mucking around with that bit, and, am probably not going to be able to help with any specific issues incurred by changing that.Alternatively have a look here
https://www.callcentrehelper.com/searchresults.php?q=ocupancy
Or:
http://www.servicelevelgroup.com/files/SLGOccupancyBasics.pdf
Is well worth a read.
2)Leslie.
This is a single queue model, trying to work with two will give a good approximation of FTE requirements, but always assumes an even distribution of both types of call. This is probably leading to the SL disagree..
3)Bill.
Excel support time 🙂
Try pasting values then formats as opposed to
paste all….4) Rey.
This is a voice traffic calculator, sorry but you’ll need to speak to your vendor regarding VOIP
bandwidth issues.Hope that helps
Regards
DaveA

Hi DaveA,
I have been using your calculator for some time now and have managed to manipulate it into a bigger picture spreadsheet that can directly map a live rota (factoring in individual agents availability on a 15 minute basis) into your calculator to be able to make real time tweeks to staffing levels on a daily or weekly basis on the rota that will instantly show the impact on service and staffing needs on a daily and weekly basis.
It has worked well to date for managing a small to medium sized team (I have created 2 such documents, one for up to 15 staff and 1 for up to 30 staff). The problem I have is that I think there is more capability in what I have designed for larger services, but I do not have the VBA experience or know how to make it work without getting into excessively large spreadsheets!!
Just wondered if you wanted to see what I had managed to do and see if it was of interest for yourself to make better (?) as others may also benefit from a combined rota/erlang tool as I know we certainly have.
Please let me know if you fancy the challenge as I’d love to have someone else’s input on what I have managed to do.
Matt

Hi Dave,
I have the calculator open in Excel 2007 and macros enabled. However, no matter what I enter for variables – call rate, duration and service levels the number of agents remains calculated as 1 ! What is causing this?
Also the model currently shows hours of operation from 7:00 to 15:00. How do I change it to reflect our hours 7:30 to 17:00?

Hi Dave,
Disregard second question. I can add and delete hours of operation 🙂 Top part of calculator is still not working.

Hi DaveA
Seem to have resolved 2nd problem – Traffic Intensity (Erlang) formula was missing from uppler left hand calculation box

which grade of secvice is standard for core and wh ich one is for acees?

Hussein,
GOS is defined by the user.
Can you clarify the question please?
Thanks

Hi Dave;
I want to calculate the Erlang capacity based on available circuit in core pare, , I have to consider blocking probability rate, so I need to calculate the Erlang capacity with grade of service from Erlang B table.
Can you please let me know which GoS is standard?
0.01% , 0.1% , 1%
0.02% , 0,2% , 2%Also for radio access part as well.
thanks
Hussein 
how can we connect that sheet with auto dialier sofware

I need to forecast service level by interval and was wondering if erlang could do this ( I know my scheduled staff by interval and wanted to say at 915 we are predicting a service level of 5%)

Hav a quick look at Col:D rows 3888.
There if you put in AHT AND and target Svc level you can then add number of agents in and it will give you teh predicted svc level.
Should be what you’re after.
Regards.
DaveA

Dave, have you any solution in order to run this calculator in Open Office?
Thanks

Nice job, this is really helpful!

Walter,
sorry. still working.
which version are you using?
regards

A one hour call from one user equals one erlang?? but how much is the total traffic in MSCS considering the uplink and downlink?? 1 erlang or 2 erlang?

OK,
can you define MSCS please?
the only references I can find are network
related, not voice traffic.thanks
DaveA

Looking for some advice…
I don’t see where shrinkage is taken into account within the required FTE calc. What is the best way to account for that after obtaining the base line FTE from this calculator?

Hi Dave,
Your model rocks! I just read through this page and wow, a lot of good information. I have used your spreadsheet as a base for many models. I start with your spreadsheet, as I could not get the code over(from reading I now understand why). I used it to create a staffing plan, integrated shrinkage formulas, etc.. My thought is that it would be great to have a place on your website where people could offer their creations that are based on your model. I have not seen anything else out there like what you have, and by letting others contribute to a assorted “Collection” of solutions based on it, I think would just be awesome. 
Hi Tim
That sounds like a great idea.
Would you be able to mail it in to us and we will add it on.
Thanks

the agent planner table… i input 6 agent… the incoming call rate column…what should i put? is the incoming call rate consistent?

And finally – Dave – what are this week’s lottery numbers? You manage to answer every other query, so I am sure you can manage this one LOL

Alli
You put in your actual incoming call rate.
Pennyd
Thanks!

Hi. This is a great little calculator. Could you tell me if it would work on hourly data if I changed the 1800 to 3600? Thanks Mike

yes,
Just remember to change the
times as well 🙂DaveA

When Fractional agent is used to calculate interval level req let’s say @ 80% 20sec target then if the same rq is used to calc SLA..it does not give 80%SL as result? In other words Reverse calculation doesnt hold true.Does any one know why?

Also if fractional call cacacity is used to calcuate the call capacity of the agents dervied through fractional agetns it does not revert the same no of calls that were used in fractional agents..Do we know any thing about it?

Erlang C and it’s like are Trapdoor / One way algorithms.
http://en.wikipedia.org/wiki/Oneway_function
You cannot always reverse the probability equation.
Regards
DaveA

what is the formula to know how many agents we require in each interval
example:
in 1 hour we receive 100 calls
Our call handelling time is set to 10 mins******* how many agents we need at that period to answer all the calls 
Err…..
Not sure what you mean.
The erlang calculator will work
out your staffing for you.The formula is basically:
= POISSON(D2,G2,FALSE)/(POISSON(D2,G2,FALSE)+(1H2)*POISSON(D21,G2,TRUE))Where row d contains the agent figure, G the trafic intensity in Erlangs and H the Productivity / Utilisation required.
This is just a demo of the Poisson distribution function.
Regards

Ravi,
Without knowledge of how your calculator functions
we are going to be unable to ascertain why there is
a variance.I do not know why they vary but am inclined to go with the Excel
Erlang calculator.Regards

Hi @Applebyd,
Currently Im using the Aspect WFM tool in my PC, Im comparing the excel output to the tool Staff Group’s Actual Required without Staff Adjustments and Unproductive, Always there is a difference in the output, some values are exactly matching, but some are having variance, Please Advice on this, any idea about Aspect WFM tool calculation.
Please help.

Any one, have tried the above calculation ?, Please Advice
Thanks,

Ravi,
The problem is that the ‘raw Excel calculation
will be different from the propriety one that Aspect uses.It is very unlikely that
a) They will ever match exactly and
b) Anyone here will know the inner algorithm coding of the Aspect WFM, that’s
information that companies closely guard as it’s the key driver for their product.I think you’re going to have to settle for the fact there are
differences between Aspect and Excel.Sorry…
DaveA

Can any one help me how to get number of agents , by using erlang Abandon formula.
Please Help,
Thanks,

OK,
Now that’s a different question.
Erlang doesn’t actually work with Abandoned calls it
assumes a call will queue indefinitely.What you are after is Merlang:
Most WFM use the approach covered in the simulation
paragraph therefore, whilst their
output will be close to Erlang / any formula based system
their output will never match exactly.Regards
DaveA

Hi!
I have downloaded the Erland C calculator and I wish to use it in Excel 2013.
When I open your file, it works.
The instance I click “Enable Macros” it does not work.I get #NAME error in any field calculating with the custom formulas.
I tried saving the file in a newer file type (.xlsm) to no avail.
I have lowered macro security but I just can’t get arround that #NAME error.I hope you are able to help me.
Is there anything I should be aware of, when using Excel 2013 instead of one of the older versions?Thanks in advance!
Have a nice day!
Michael

Hi,
I really like this model and it is very useful.
The only thing I need to know is how can I use this for a blended inbound/outbound environment?
Your help would be greatly appreciated.
Cheers
Liam

Why is it that the Erlang C calcuator only calculates for a half hour period instead of an hour?

I historic call data for every 15 minutes. I’m predicting the calls based on a 90 percentile assumption.
Can I change 1800 to 900 from cell g39 to g86 if I want to calculate agents required by 15 minute interval?

If 90 contacts are expected in the next 1 hr. How many agents would be required if the AHT is 480 secs and occupancy is 80%.

What %age of FTEs are more required if we move from 7030 SL to 8020 SL? Please help!

The calculator will do that for you 🙂
Change cells D18 and D21
Regards
DaveA

Does the calculator consider by default an expected level of occupancy, or it has to be considered separately?

Jerry,
The calculator gives you the bottom
line numbers you need.You have to factor shrinkage etc on top.
Regards
DaveA

Is it unreasonable to expect valid forecasts for 85% in 300 seconds service level?

Hi,
is it possible to convert this to hourly?specially the event planner. Can I just use hourly data for it?
thank you

Hi Can you tel me the difference between “Average call duration” and “Target answer time”? Thank you. Sincerely.

Average Call Duration is the length of the telephone call – i.e. the time from when an agent started talking to the caller to the time when the call was ended. The Target Answer Time is how long that you target that the caller has to queue for – i.e. We wish to answer 90% of calls in 15 seconds.

Thank you very much. Have a nice day.

Hi
I’ve read through all the comments and answers as well and still have a couple of questions relating to the calculator. It looks great though but before I can begin to use it for forecasting and resource planning can you answer the following please?
1. Are the projected calls spread across the 30 minute slot equally or are they weighted to have more at the beginning of the half hour interval?
2. With one of my service levels, we are only required to hit 50% SL. I have keyed in 50% as the required service level and the form calculates but is the predicted service level a percentage of the 50% or actual Service level that will be achieved?
Thanks
Matt

Matt,
To answer your questions.
1) It looks at a balanced distribution during the arrival period, but…
You can set the interval threshold to 900 sec instead of 1800 and work with the 15min intervals for greater granularity.
2) Broadly speaking it’s what you can expect to achieve.
Hope that helps.
Regards
DaveA

When factoring Occupancy percentages, do you take Presented calls or handled in any given interval?

I am a big fan of your erlang calculator as the organization I work for does not have a workforce management suite with which to model call stats and staffing. I have used your tool for years to produce the Staffing Requirements (or other values)for cut and paste into Excel or Access tools. In the absence of a WFM software I have recently built a staffing model in Excel 2010 that requires I have multiple instances of the Erlang Calculator embedded in the multiple worksheets of the same workbook.
The Calculator works perfect when I use it as a standalone excel workbook. If I copy the agentcalc worksheet from the standalone calculator and paste it into a new Excel 2010 worksheet, Excel no longer recognizes the erlang formulas for servicelevel, agentno, erlangc. The VBA shows, but it seems as though it has lost the connection to the .xla file.
Do you have any recommendations on how or if I can do this.

Hi Paul.
Nice to see you find it useful!
In order to duplicate it within a workbook you’ll need to follow a couple of steps.
1) When you copy it over also copy the module from the VBA Window.
2) servicelevel, agentno, erlangc are all named ranges you’ll need to change. I suggest just appending _1 (VBA will need the underscore)
3) Change the variables in the new module (Anything that has a DIM statement at the top)
again I suggest appending a _1 (same caveat applies)Give it a go, see what happens, and, if you get problems post them back here and I’ll try to help.
Just as a suggestion have you thought about just calling a second workbook from the first and running
the macro from the first book? That would eliminate the whole problem as you can then automate the whole cut and paste of the data…Regards
DaveA

Thanks for your quick response. I’ll report back after I try this. Kind Regards.
Paul 
In a call center environment, AHT is rarely consistent every 30 minutes. Do you plan to create a version of the Erlang Calculator where you can input the AHT forecast everyin 30 minute intervals along side the CV intervals?

I have AHT 240 , ASA 20, Calls 30 min interval as 40 , Target sl 80%, Now if i have to calculate req agent and forcasted sl , without using erlang what would be the step by step process

I’m not sure why you cannot use Erlang, as this would be the easiest way to do it.
The only other approaches are
– Using Workforce Management software. There are lots of companies who do this in the directory.
– Creating some form of simulation computer program. This would take a lot of time and effort.I’m not aware of any way to do it on paper.

Its just a challange given to me and i really hope i could get soe help , pOisson probabilty migh help but i m unbale to make data to use Poisson , if some one can help with the data points

The calculator works just fine – i plugged in my actuals and it is showing i should be doing fine while day on day or even on an interval basis I am failing. My assumption is that i have too much in office shrinkage which is not factored in this tool. I notice my Outbound activity and other auxes are taking away my staffing. how do i incorporate these to my planning for better coverage?

Do you have this for a weekly call profile calculation, this currently is a daily calculator.

did you include the shrinkage in this calculator ?

I got this one in your Web :
3. Does it allow for shrinkage?The short answer is no, it gives a required number of staff to be available on the phone for any given interval. Shrinkage is a bit too much of a local variable to be able to factor. Different companies use different criteria, personally I’ve seen:
•Sickness
•Late
•1:1 sessions
•Breaks
•Coaching
•TrainingHowever, everyone has their own criteria.
It can simply be corrected for by using: (FTE/Shrinkage %)*100
WHERE we can apply this formula “by using: (FTE/Shrinkage %)*100” ??

Hi,
My self Sudip.
Now I have download Erlang sheet V3 it is brilliant thank you so much for providing this tools.
Need help on this.
I want to know how much occupancy included in this tools?
Can I able to change the target of occupancy?Thanks.

Hi Dave, Thanks you for this tool!!! Worked with several WFM tools in the past, at my new job we don’t have one, and I really like this for the basic requirements. HAve one question, how can an interval with 3 calls have a service level of 95,45%?? I think it can only be 0%, 33,3%, 66,6% or 100% (0,1,2 or 3 calls within SLA). Can you please explain what I am missing?
Thanks!!! 
Can someone tell me what is “Required service level”. I am very new to this and i am in need of this kind of template

Hello, I was questioned whether this calculator has agent utilization built into the formulas and if we can adjust? I do not normally add utilization in my analysis, but am being asked to do so. I am unsure whether to include it in the ErlangC calculation or in Shrinkage. Thanks!

how to calculate number of staffs achieved between 80% to 95% using below data
80%
75%
95%
98%
65%