The ErlangC functions are built into every WFM system, but for those of us who are still doing many of our WFM calculations in Excel, calculating ErlangC functions such as FTE required is pretty much impossible.

I’ve provided here the most used WFM functions, in VBA, for anyone to use free of charge. If you do decide to edit it at least give me a shout out somewhere in your code 🙂

This text file contains the code for Factorial, Sigma, ErlangC, ServiceLevel, TrafficIntensity, and AgentsNeeded. Copy the text from the file, and past it in a new Module in your Excel VB Editor on your WFM spreadsheet (Alt+F11 -> Insert Module). You will then be able to use the functions in your Excel document. I will also post the code below at the end of the article for those who are just checking it out.

The main function used from this suite is the AgentsNeeded() function. Use it by interval and pass in your projected call volume, AHT, service level goal (as a decimal percentage), and service level time (how quickly you want your calls to be answered). If you have any questions on the other functions, feel free to contact me. If you would rather pass your reporting interval than change is statically, replace the first two lines of that function with the following:

Function AgentsNeeded(ByVal intCallVolume As Long, ByVal dblAHT As Double, ByVal dblServiceLevelGoal As Double, ByVal dblServiceLevelTime As Double, ByVal intReportingInterval) As Long

Const intReportingPeriod As Integer = intReportingInterval

 

If you found this useful, you may also be interested in the FifthSaturday() function.

 

‘Developed by Kyle Boehlen
‘At Ken Garff SCC
‘Last Updated 6/27/17 by Kyle Boehlen
‘Contains all of the erlang functions needed such as Sigma, Factorial, SigmaFactorialLoop, ErlangC, Service Level, Traffic Intensity, and agents needed.

Function Factorial(ByVal n As Double) As Double

‘Variable for answer
Dim dblAnswer As Double

‘Factorial function loop
dblAnswer = n
For i = n – 1 To 1 Step -1
dblAnswer = dblAnswer * i
Next i

‘Return answer
Factorial = dblAnswer

End Function

Function Sigma(ByVal n As Integer) As Integer

‘Declare variable to hold answer
Dim intAnswer As Integer

‘Sigma function loop
For i = 0 To n
intAnswer = intAnswer + i
Next i

‘Return answer
Sigma = intAnswer

End Function

Function SigmaFactorialLoop(ByVal dblTrafficIntensity As Double, ByVal intAgentsNeeded As Integer) As Double
‘Function is easier than using both sigma and factorial when calculating erlangc
‘Declare variables and variable for answer
Dim n, k, dblAnswer As Double

‘Declar counter
Dim i As Integer

‘Starting variable values
n = 1
dblAnswer = 0

‘Calcuate
For i = intAgentsNeeded To 0 Step -1
k = n * i / dblTrafficIntensity
dblAnswer = dblAnswer + k
n = k
Next i

‘Return answer
SigmaFactorialLoop = dblAnswer

End Function

Function ErlangC(ByVal intCallVolume As Integer, ByVal intReportingPeriod As Integer, ByVal dblAHT As Double, ByVal intAgentsNeeded As Integer) As Double

‘Declare variables needed and variable for answer
Dim dblAgentOccupancy, dblTrafficIntensity, dblAnswer As Double

‘Define variables needed to calculate
dblTrafficIntensity = TrafficIntensity(intCallVolume, intReportingPeriod, dblAHT)
dblAgentOccupancy = dblTrafficIntensity / intAgentsNeeded

‘Return ErlangC
dblAnswer = 1 / (1 + ((1 – dblAgentOccupancy) * SigmaFactorialLoop(dblTrafficIntensity, intAgentsNeeded)))

‘Makes sure ErlangC is between 0 and 1
If dblAnswer <= 0 Then
dblAnswer = 0
ElseIf dblAnswer >= 1 Then
dblAnswer = 1
End If

‘Return answer
ErlangC = dblAnswer

End Function

Function ServiceLevel(ByVal intCallVolume As Integer, ByVal intReportingPeriod As Integer, ByVal dblAHT As Double, ByVal dblServiceLevelTime As Double, ByVal intAgentsNeeded As Integer) As Double

‘Declare variables needed to calculate service level and variable for answer
Dim dblServiceLevel, dblTrafficIntensity, dblErlangC, e As Double

‘Calculate prerequisites
dblTrafficIntensity = TrafficIntensity(intCallVolume, intReportingPeriod, dblAHT)
dblErlangC = ErlangC(intCallVolume, intReportingPeriod, dblAHT, intAgentsNeeded)
e = Exp((intAgentsNeeded – dblTrafficIntensity) * dblServiceLevelTime / dblAHT * -1)

‘Calculate service level
dblServiceLevel = 1 – (dblErlangC * e)

‘Return answer
ServiceLevel = dblServiceLevel

End Function

Function TrafficIntensity(ByVal intCallVolume As Integer, ByVal intReportingPeriod As Integer, ByVal dblAHT As Double) As Double

‘Declare variable for traffic intensity
Dim dblTrafficIntensity As Double

‘Calculate traffic intensity
dblTrafficIntensity = (intCallVolume / (intReportingPeriod * 60)) * dblAHT

‘Return traffic intensity
TrafficIntensity = dblTrafficIntensity

End Function

Function AgentsNeeded(ByVal intCallVolume As Long, ByVal dblAHT As Double, ByVal dblServiceLevelGoal As Double, ByVal dblServiceLevelTime As Double) As Long

Const intReportingPeriod As Integer = 15 ‘Change this value (in minutes) if your reporting period changes from 15 minutes

‘Declare variables needed to calculate agents needed
Dim dblTrafficIntensity, dblServiceLevel As Double
Dim intAgentsNeeded As Integer

‘Calculate traffic intensity
dblTrafficIntensity = TrafficIntensity(intCallVolume, intReportingPeriod, dblAHT)

‘Start agents out as the integer of intensity and see if we already have enough agents
intAgentsNeeded = Int(dblTrafficIntensity)
dblServiceLevel = ServiceLevel(intCallVolume, intReportingPeriod, dblAHT, dblServiceLevelTime, intAgentsNeeded)

‘Looping until the service level is higher than the goal
Do Until dblServiceLevelGoal <= dblServiceLevel
intAgentsNeeded = intAgentsNeeded + 1
dblServiceLevel = ServiceLevel(intCallVolume, intReportingPeriod, dblAHT, dblServiceLevelTime, intAgentsNeeded)
Loop

‘Make sure it returns at least one agent
If intAgentsNeeded <= 1 Then
intAgentsNeeded = 1
End If

‘Return agents needed
AgentsNeeded = intAgentsNeeded

End Function

4 thoughts on “ErlangC VBA Functions”

  • Pingback:Erlang-C
  • This is an amazing creation. Very well done!

    Worth mentioning the requirements for each variable:
    intCallVolume = call volume for the interval you’re looking at
    dblAHT = Average Handle Time IN SECONDS
    dblServiceLevelGoal = Percent of calls you want answered within the interval period
    dblServiceLevelTime = Target answer time in seconds

    Also, I can’t seem to get the option of declaring the interval length in the sheet to work. I keep getting an error stating that a constant expression is required. I’m leaving it in the code for now, but i would be interested to know what should be done to fix this.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>