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

## MartinPoica says:

Hellow my name is MartinPoica. Wery good-hearted article! Thx ðŸ™‚

## RKKC says:

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.

## Kyle Boehlen says:

Hmm I’m not sure, it’s been a while since I’ve played with it