Erlang-C with Excel or Lotus123

The key to performing Erlang-C calculations in a spreadsheet is to use the Poisson function, which is provided both in Excel and in Lotus123. The details of the Poisson function and how it is used are explained below, together with detailed instructions for setting up a spreadsheet. Sample spreadsheet for both Excel and Lotus123 are provided.

(1) The Poisson Function

The Poisson function is provided in both Excel and Lotus123, and is defined as

   

(2) The Erlang-C Function Using The Poisson Function

The definition of the Erlang-C function is

Multiplying the numerator and denominator by e^-u we get

The above formula for Erlang-C can be rewritten using the Poisson function as

(3) ASA or Waiting Time Calculation

(4) Service-Level Calculations

The service level can also be calculated fairly straightforwardly, making use of the EXP function. The formula for service level as a probability is given here. The spreadsheet coding is given later in this article, and the probability needs to be converted to a percentage.

The following paragraphs explain the layout of a simple spreadsheet for doing Erlang-C calculations. Samples are available for both Excel and Lotus123.

(6) Input Values

ll the input values are placed in columns on the left of the spreadsheet.

• Column B. The number of calls in the interval.
• Column C. The length of the interval in seconds. This allows you to work with hours, half-hours, or any other length of interval that you prefer.
• Column D. The average call duration in seconds, represented by the symbol "T_S"..
• Column E. The number of agents, represented by the symbol "m" in the formulae.
• Column F. The target answer time for the service level.

(7) Intermediate Values

Several intermediate values need to be calculated. These are placed in the centre columns of the spreadsheet.

• Column G. Traffic intensity, represented in the formula by the symbol "u". The spreadsheet formula is "+(Bn/Cn)*Dn", where "n" is the spreadsheet row number.
• Column H. The agent utilisation or occupancy, represented by "ρ". The spreadsheet formula is "+Gn/En". Note that utilisation is on the scale 0 to 1, not 0 to 100%.
• Column I. The Erlang-C function. the spreadsheet formula is "+Poisson(En,Gn,false)/(Poisson(En,Gn,false)+(1-Hn)*Poisson(En-1,Gn,true))"
• Column J. The service level, as a probability on the scale 0 to 1. The spreadsheet formula is "+1-In*Exp(-(En-Gn)*Fn/Dn)"

(8) Results

The results are placed in the right-hand columns. Most of the additional calculations needed are just to convert probabilities into percentages.

• Column K. Agent occupancy. This has already been calculated in column H, but needs to be converted to a percentage, so the spreadsheet formula is simply "+100*Hn".
• Column L. The percentage of calls that are answered immediately. The Erlang-C function, calculated in column I, is in fact the probability that a call will have to wait, so the percentage of calls that will be answered immediately is "+100*(1-In)".
• Column M. The service level, or the percentage of calls answered within the target answer time. This has been calculated in column J as a probability, so to get a percentage we simply need the formula "+100*Jn".
• Column N. The ASA, average speed of answer, or average time that a call waits. The spreadsheet formula is "+In*Dn/(En*(1-Hn))". The result is in seconds.