Looking at the code for the ErlangC Addin for Excel and trying to do what it does without using VB.
'-----------------------------------------------------------------------
Public Function Agents(SLA As Single, ServiceTime As Integer, CallsPerHour As Single, AHT As Integer) As Long
'Copyright © T&C Limited 1996, 1999, 2001
'Calculate the number of agents required to service a given number of calls to meet the service level
' SLA is the % of calls to be answered within the ServiceTime period e.g. 0.95 (95%)
' ServiceTime is target answer time in seconds e.g. 15
' CallsPerHour is the number of calls received in one hour period
' AHT is the call duration including after call work in seconds e.g 180
Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single
Dim Erlangs As Single, Utilisation As Single, C As Single, SLQueued As Single
Dim NoAgents As Long, MaxIterate As Long, Count As Long
Dim Server As Single
On Error GoTo AgentsError
If SLA > 1 Then SLA = 1
BirthRate = CallsPerHour
DeathRate = 3600 / AHT
'calculate the traffic intensity
TrafficRate = BirthRate / DeathRate
'calculate the number of Erlangs/hours
Erlangs = Fix((BirthRate * (AHT)) / 3600 + 0.5)
'start at number of agents for 100% utilisation
If Erlangs < 1 Then NoAgents = 1 Else NoAgents = Int(Erlangs)
Utilisation = TrafficRate / NoAgents
'now get real and get number below 100%
While Utilisation >= 1
NoAgents = NoAgents + 1
Utilisation = TrafficRate / NoAgents
Wend
MaxIterate = NoAgents * 100
'try each number of agents until the correct SLA is reached
For Count = 1 To MaxIterate
Utilisation = TrafficRate / NoAgents
If Utilisation < 1 Then
Server = NoAgents
C = ErlangC(Server, TrafficRate)
'find the level of SLA with this number of agents
SLQueued = 1 - C * Exp((TrafficRate - Server) * ServiceTime / AHT)
If SLQueued < 0 Then SLQueued = 0
If SLQueued >= SLA Then Count = MaxIterate
'put a limit on the accuracy required (it will never actually get to 100%)
If SLQueued > (1 - MaxAccuracy) Then Count = MaxIterate
End If
If Count <> MaxIterate Then NoAgents = NoAgents + 1
Next Count
AgentsExit:
Agents = NoAgents
Exit Function
AgentsError:
NoAgents = 0
Resume AgentsExit
End Function
-----------------------------------------------------------------------
Obviously, I'm not going to stuff all that into a formula in a single cell that reads four other cells, but it would be rad to be able to have the components in the VB generate in as few cells as possible and then spit out the same requirement that the add-on does.
Solving for this would allow others to view FTE Requirements without having to install the Add-on themselves.
Help!
'-----------------------------------------------------------------------
Public Function Agents(SLA As Single, ServiceTime As Integer, CallsPerHour As Single, AHT As Integer) As Long
'Copyright © T&C Limited 1996, 1999, 2001
'Calculate the number of agents required to service a given number of calls to meet the service level
' SLA is the % of calls to be answered within the ServiceTime period e.g. 0.95 (95%)
' ServiceTime is target answer time in seconds e.g. 15
' CallsPerHour is the number of calls received in one hour period
' AHT is the call duration including after call work in seconds e.g 180
Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single
Dim Erlangs As Single, Utilisation As Single, C As Single, SLQueued As Single
Dim NoAgents As Long, MaxIterate As Long, Count As Long
Dim Server As Single
On Error GoTo AgentsError
If SLA > 1 Then SLA = 1
BirthRate = CallsPerHour
DeathRate = 3600 / AHT
'calculate the traffic intensity
TrafficRate = BirthRate / DeathRate
'calculate the number of Erlangs/hours
Erlangs = Fix((BirthRate * (AHT)) / 3600 + 0.5)
'start at number of agents for 100% utilisation
If Erlangs < 1 Then NoAgents = 1 Else NoAgents = Int(Erlangs)
Utilisation = TrafficRate / NoAgents
'now get real and get number below 100%
While Utilisation >= 1
NoAgents = NoAgents + 1
Utilisation = TrafficRate / NoAgents
Wend
MaxIterate = NoAgents * 100
'try each number of agents until the correct SLA is reached
For Count = 1 To MaxIterate
Utilisation = TrafficRate / NoAgents
If Utilisation < 1 Then
Server = NoAgents
C = ErlangC(Server, TrafficRate)
'find the level of SLA with this number of agents
SLQueued = 1 - C * Exp((TrafficRate - Server) * ServiceTime / AHT)
If SLQueued < 0 Then SLQueued = 0
If SLQueued >= SLA Then Count = MaxIterate
'put a limit on the accuracy required (it will never actually get to 100%)
If SLQueued > (1 - MaxAccuracy) Then Count = MaxIterate
End If
If Count <> MaxIterate Then NoAgents = NoAgents + 1
Next Count
AgentsExit:
Agents = NoAgents
Exit Function
AgentsError:
NoAgents = 0
Resume AgentsExit
End Function
-----------------------------------------------------------------------
Obviously, I'm not going to stuff all that into a formula in a single cell that reads four other cells, but it would be rad to be able to have the components in the VB generate in as few cells as possible and then spit out the same requirement that the add-on does.
Solving for this would allow others to view FTE Requirements without having to install the Add-on themselves.
Help!
