Erlang B - Excel Spreadsheet

1744 views
Skip to first unread message

Bruce Griffis

unread,
Feb 20, 1996, 3:00:00 AM2/20/96
to

Anyone know how to do an Erlang B in an Excel spreadsheet?

I am trying to do a rough guestimate on trunks required to
carry a given load of LD and 800 (actual minutes) and estimated
on-net calling to arrive at required dedicated trunks. I have
minutes per month of billed traffic for a year, working days per
month, average hours in working days - and a rough cut at Erlangs.

How do I take the Erlangs in the Excel spreadsheet and
arrive at required trunks. I'd rather not jump between a traffic
calculator and a spreadsheet if I don't have to.

The purpose is to see if dedicated trunks and required
hardware can be cost-justified (I already have hardware costs for
upgrades, and co-trunk costs that would be offset by going to
dedicated trunks).

Regards, Bruce Griffis
cccef.b...@capital.ge.com

fav...@ibm.net

unread,
Feb 21, 1996, 3:00:00 AM2/21/96
to
In <NEWTNews.8248570...@cccef.bgriffis.capital.ge.com>, Bruce Griffis <cccef.b...@capital.ge.com> writes:
>
> ....The purpose is to see if dedicated trunks and required
>hardware can be cost-justified (I already have hardware costs for
>upgrades, and co-trunk costs that would be offset by going to
>dedicated trunks).
>
>Regards, Bruce Griffis
> cccef.b...@capital.ge.com
>
>

Bruce,

I would recommend you try a shareware program called "Traffic Calc". It is a Windows
based program that not only will do all the calculation including the cost of the dedicated
trunks versus the local line costs, but also handles ACD groups for holding times.

I believe you can find the program on Compuserve and various Internet sites. The program
costs between $30 and $50.00. Good luck


--------------------------------------------------------------------------
"Living comfortably with OS/2 Warp and over 1,500 OS/2 applications."

Jonathan Faville *** My opinions belong to me and only me ***
74454...@compuserve.com
or
fav...@ibm.net
--------------------------------------------------------------------------


Peter Jones

unread,
Feb 21, 1996, 3:00:00 AM2/21/96
to
Bruce Griffis (cccef.b...@capital.ge.com) wrote:

> Anyone know how to do an Erlang B in an Excel spreadsheet?

It might be easier to just type an Erlang B table in for the approximate
range of trunks/offered traffic you expect!

If not then the way I have done it before is:

Probability of all trunks busy for C trunks with T offered traffic (Erl)
is given by this simple induction formula.

P(C,T) = T * P(C-1,T)
-----------------
C + T * P(C-1,T)
P(0,T) = 1

The next step is to determine the number of circuits needed to give a
blocking probability (above) at your chosen design grade of service (0.01 say).
As far as I know you have to solve this iteratively - probably Newton Raphson
or something flash. The easiest thing is to do something like

circuits = ROUND( traffic)
gos = 1
while ( gos > design_gos ) {
gos = P(circuits,traffic)
circuits = circuits + 1
}
circuits_needed = circuits - 1

This loop is reasonably well behaved.

Anyone know a less iterative way?

--
----------------------------------------------------------------------------
PETER JONES E-Mail: peter...@metrica.co.uk
Metrica Ltd. Tel: +44 181 334 2920
11-13 Worple Way Fax: +44 181 332 7403
Richmond, UK
----------------------------------------------------------------------------

Fred Koke

unread,
Feb 24, 1996, 3:00:00 AM2/24/96
to
Bruce Griffis <cccef.b...@capital.ge.com> wrote:

>
> Anyone know how to do an Erlang B in an Excel spreadsheet?
>

I have an Erlang table in Excel format covering 1 to 200 trunks at .01
grade of service, its anyones for $5.00 to cover my costs. Email if
your interested.

Fred

C. T. Nadovich

unread,
Feb 24, 1996, 3:00:00 AM2/24/96
to
se...@iprolink.co.nz (Fred Koke) writes:

>Bruce Griffis <cccef.b...@capital.ge.com> wrote:

How generous of you.

--
73 de KD3BJ SK .. http://www.kd3bj.ampr.org
+1 215 257 0635 (voice) +1 215 257 2744 (data/fax)

Scott Townley

unread,
Feb 26, 1996, 3:00:00 AM2/26/96
to
I wrote one a little while back.
Plug in number of trunks and offered traffic and it figures grade of service.
If anyone wants it contact me via e-mail and I'll send it to you same.

Or is the reverse calculation more desireable? It's an easy change.

Scott Townley
nx...@primenet.com

Bruce Griffis

unread,
Feb 26, 1996, 3:00:00 AM2/26/96
to

Scott,
I'll take a copy of the GOS spreadsheet model.
Regards, Bruce Griffis
cccef.b...@capital.ge.com


Owen Duffy

unread,
Mar 7, 1996, 3:00:00 AM3/7/96
to
In article <NEWTNews.8248570...@cccef.bgriffis.capital.ge.com>,
cccef.b...@capital.ge.com says...

>
>
> Anyone know how to do an Erlang B in an Excel spreadsheet?
>
I have written a set of functions to allow calculation of
1) number of trunks given traffic intensity and GOS,
2) GOS given traffic intensity and number of trunks

The functions are in a written in C and are packaged in a DLL. Once the DLL
is registed (by a macro call) these machine language functions are available
in the normal Insert Function dialogues. The operate MUCH faster than a macro
implementation, and using 80 bit reals, are accurate up to about 150 trunks.
Indeed, you will find them more accurate than most tables which were based on
60 bit or 64 bil reals.

I will try and post a sample traffic model using these functions in the next
few days (especially if there is significant interest). The functions are
quick enough to recalculate a model for a least cost solution for dimensioning
a private tie line route with overflow the public switched network in a
fraction of a second, so that one can explore the overflow/carried traffic and
demonstrate to an unsophisticated audience (the customer) that overflow isn't
necessarily bad, in fact a small overflow during busy hour is typical of an
optimally configured least cost route.

(Considering the specialist nature of the market, the price of the function
package is IMHO quite modest at A$50 + p&p.)

Owen Duffy

here or ow...@oda.com.au


Al Varney

unread,
Mar 15, 1996, 3:00:00 AM3/15/96
to
In article <4h3ho5$s...@nnrp1.news.primenet.com>,
Scott Townley <nx...@primenet.com> wrote:
>I've gotten so many requests for this darned Erlang-B spreadsheet that I might
>as well post it.

Thanks, Scott -- you asked for suggestions, so there's one below.
And thanks also for providing a project interesting enough to force me
to start using Excel and Visual Basic....


>Synopsis: Excel 5.0 spreadsheet that will calculate in both directions:
>1. Given offered load and number of trunks, calculate resultant
>grade-of-service.
>2. Given desired grade-of-service and offered load, calculate minimum number
>of trunks to supply the desired grade-of-service. Also requires an input as
>to the maximum number of trunks to consider--it's purely an algorithmic
>requirement in the Excel macro, but actually has some practical grounding.

Just drop in the following VB code (a modification of Scott's). It will
work beyond some of VB/Excel limits {you can't do [120 ^ 200], for example}
and be MUCH faster. For example, [=Rev_Erlang(.02,50,72)] with the original
code took 40 seconds on a 80386 without math co-processor. The new code took
about 1 second. And [=Rev_Erlang(.02,500,720)] works in about a second. The
old code couldn't handle such big numbers.

The Rev_Erlang function was changed to compute ErlangB inline, so the
check with 'gos' could be done internally.

Al Varney - just my opinion
(No warranty on the code....)


--------------------------------------------------------------------------
Function ErlangB(A, N)
' A is offered load in Erlangs, N is number of trunks
' ErlangB is Lost Calls Cleared
' Iterative formula: ErlangB(A,0) = 1,
' ErlangB(A,n) = A*ErlangB(A,n-1)/[n+A*ErlangB(A,n-1)]
' [The iterative formula is easily(!) derived from and mathematically
' identical to the original Erlang B formula of
' A^N / N! * Sum-from-0-to-N(A^i/i!)
' but doesn't overflow. :) ]
'
Dim Partial As Double
Dim i As Integer
Partial = 1
For i = 1 To N
Partial = A * Partial
Partial = Partial / (i + Partial)
Next i
ErlangB = Partial
End Function

Function Rev_Erlang(gos, A, max)
Dim Partial As Double
Dim gos1 As Double
Dim ntrunks As Integer
gos1 = gos
Partial = 1
For ntrunks = 1 To max
Partial = A * Partial
Partial = Partial / (ntrunks + Partial)
If Partial <= gos1 Then
Rev_Erlang = Int(ntrunks + 0.5)
GoTo EndIt
End If
Next ntrunks
Rev_Erlang = max
EndIt:
End Function


edson.g....@gmail.com

unread,
Nov 1, 2016, 11:02:53 AM11/1/16
to

Hi Bruce,
I have some erlang b spreed sheets, if you still need this information please send your email.

Best Regards,
Edson lourenço

Adam H. Kerman

unread,
Nov 1, 2016, 12:23:49 PM11/1/16
to
edson.g....@gmail.com wrote:

>Hi Bruce,
>I have some erlang b spreed sheets, if you still need this information plea=
>se send your email.

>Best Regards,
>Edson louren=C3=A7o

This thread is from 1996. Nice troll.
Reply all
Reply to author
Forward
0 new messages