Erlang B Visual Basic implementation for use in Excel
Erlang B is a commonly used algorithm used to estimate required capacity for a given load. Originating in telephony for estimating required trunks/channels for an expected load, this same algorithm can be applied to any problem where you have a known arrival rate and required Grade of Service - e.g. servers required for given web traffic, lanes required for given car traffic, staff required for given customers in a retail shop, etc. I’m by no means an expert on the algorithm (it involves poisson distributions and clever things mathematicians get exited about). I do however use it so need it available in a format that doesn’t require a maths degree or regularly going to websites, hence Excel. Note also that this does not account for queuing (Erlang C for that) so may not be suitable where you expect and accept a queue, e.g. customers in your store can wait a few minutes and would not typically mind.
For the layman, the algorithm boils down to 3 components:
Grade of Service = the probability that the service is “busy”. For telephony, this is the % of time that customers get a busy message due to all trunks being consumed. In a web environment it would be how often your users hit a “busy” message from your website due to all capacity being consumed. You set this to a desired level based on your business needs, .5% is quite conservative and works for me. A higher value means you will install less circuits/servers etc which is cheaper, at the cost of increased probability that your service is unavailable to your customers.
Erlangs = the definition is on Wikipedia, but this is basically a dimensionless measure of arriving volume, aka load. Don’t confuse with the programming language. Put simply it is the number of calls/web requests/customers arriving per second/minute/hour/day multiplied by the average time taken to “complete” each request. If each customer in your store takes 6 minutes to handle, and you expect 100 customers per hour, you would have (100 * 60) * 5 = 10 Erlangs. Generally you would measure this over a long period of time and pick your busiest hour to scale capacity.
Circuits (or servers, or counter staff, lanes on the road, etc) = these are basically the consumers of the “load”. This is usually what you are trying to figure out so you can scale appropriately.
Below is a Visual Basic implementation of Erlang B based on the pseudo-code algorithms in the paper by Sanzheng Qiao. To use it, create a new file called “erlangb.bas” and import it as a module into the Excel spreadsheet you wish to use it with. You will then have access to 2 User Defined Functions (UDF):
1 | ErlangB_GoS(erlangs, capacity) |
The main one of use is ErlangB_Circuits
. Once you have your Erlangs (perhaps from historic data or forecast) and an agreed target GoS, you can then figure out how many circuits/servers/people you need to service the given load.
There is more in the article that I did not implement (e.g. ErlangC) as I didn’t need it for the job at hand, but I leave that as a reader exercise to complete and leave in the comments :)
1 | Attribute VB_Name = "ErlangB" |