Queuing Theory with Excel pt2

The previous blog post showed the formulas that are used in M/M/c queuing models, and finished by showing that we need an Excel formula for $\sum\limits_{a}^{b}$ if we are going to use Excel to help us.

The previous blog entry also introduced us to
$c$ = the number of servers, and
$\rho$ = the average utilisation

Excel does have a function “Poisson” which is defined as
Poisson(x,mean,cumulative)
Where
x is the number of events ("servers" in queuing terminology)
mean is the expected numerical value ("servers in use" in queuing terminology)
cumulative is a Boolean value indicating whether to return the sum of the Poisson probability (TRUE) or the exact probability (FALSE)

So
Poisson( $c$ , $c\rho$ , false) = $\frac{e^{-c\rho} c\rho^c}{c!}$
Poisson( $c$ , $c\rho$ , true) = $\sum\limits_{n=0}^{c} \frac{e^{-c\rho} c\rho^c}{n!}$

Warning!!!!

The Microsoft help for poisson shows the cumulative formula using an incredibly poor image... They show the cumulative equation as:
$= \sum\limits_{k=0}^{x} \frac{e^{-\lambda} \lambda^\wedge}{k!}$

That $\wedge$ symbol isn't clear whether it is the bottom half of an $x$ , $\lambda$ or $k$ . It is actually a $k$ however many websites have erroneously shown it as an $x$ , and therefore care should be taken to ensure that you use the cumulative poisson function of Excel correctly.

Back to our formula
Remember our target equation?

$\wp_0 = \biggl [1 +\frac{(c\rho)^c}{c! (1-\rho)} + \sum\limits_{n=1}^{c-1} \frac{(c\rho)^n}{n!} \biggr]^{-1}$

In Excel, we can use the psuedo-formula
=Poisson( $c$ , $c\rho$ ,false) * exp( $c\rho$ ) / $(1-\rho)$

Which (when written algebraically) is the following
$\frac{e^{-c\rho} c\rho^c}{c!}$ * $\frac{e^{c\rho}}{(1-\rho)}$

We can simplify this to the following
$\frac{e^{-c\rho} c\rho^c e^{c\rho}} {c! (1-\rho)}$

Since
$e^{c\rho}$ * $e^{-c\rho}$ = 1

We can reduce this further to the following
$\frac{c\rho^c}{c!(1-\rho)}$

That’s the middle part of our $\wp_0 =$ equation.. so we're halfway there.

Stage 2
The second stage is achieved as follows:

In Excel, we can use another psuedo-formula
=Poisson( $c$ , $c\rho$ ,true) * exp( $c\rho$ )

Which is the following
$\sum\limits_{n=0}^{c} \frac{e^{-c\rho} c\rho^n}{n!}$ * $e^{c\rho}$

Since $e^{-c\rho}$ is used in every part of that Sigma for every value for “n”, and doesn’t change, we can take it outside the sum and use it as a multiplier.

This changes the equation to:
$e^{-c\rho}$ * $\sum\limits_{n=0}^{c} \frac{c\rho^n}{n!}$ * $e^{c\rho}$

Also $e^{c\rho}$ * $e^{-c\rho} = 1$

So we can reduce the equation to the following
$\sum\limits_{n=0}^{c} \frac{c\rho^n}{n!}$

But look carefully at the $\wp_0 =$ equation above. We need n=1 as the starting point of the sum, not n=0. We can achieve this by rewriting the above sum, but extract the n=0 point individually.

$\frac{c\rho^0}{0!}$ + $\sum\limits_{n=1}^{c} \frac{c\rho^n}{n!}$

Since 0! = 1, and anything to the power of 0 = 1, this is the same as saying
1 + $\sum\limits_{n=1}^{c} \frac{c\rho^n}{n!}$

Ah... I thought it was too easy
That very simple Excel formula of =Poisson( $c$ , $c\rho$ ,true) * exp( $c\rho$ ) hasn't quite given us everything we needed. The SUM goes all the way to n=c however our original formula for the M/M/c queue $\wp_0$ only needed n=c-1. So we need to take that "top value" off of our nice Excel formula
=Poisson( $c$ , $c\rho$ ,true) * exp( $c\rho$ ) - $\frac{c\rho^c}{c!}$

Hang on. We've dealt with a formula like that already. We can re-use the lessons learnt in stage 1, to say that the Excel formula of
=Poisson( $c$ , $c\rho$ ,false) * exp( $c\rho$ )

When written algebraically, is the following
$\frac{e^{-c\rho} c\rho^c}{c!}$ * $e^{c\rho}$

Simplified to the following
$\frac{e^{-c\rho} c\rho^c e^{c\rho}} {c!}$

Reduced to
$\frac{c\rho^c}{c!}$

That's what we needed.

Put it all together

So in Excel
Stage 1 + Stage 2 - Stage 3
Poisson( $c$ , $c\rho$ ,false)*exp( $c\rho$ )/( $1-\rho$ ) + Poisson( $c$ , $c\rho$ ,true)*exp( $c\rho$ ) - Poisson( $c$ , $c\rho$ ,false)*exp( $c\rho$ )

Is equal to
$1 + \frac{c\rho^c}{c!(1-\rho)} + \sum\limits_{n=1}^{c-1} \frac{c\rho^n}{n!}$

Ah, but.... The $\wp_0$ equation is all to the minus 1, and we haven't got that (yet). This is simply resolved.

The Excel formula of 1/(Poisson( $c$ , $c\rho$ ,false)*exp( $c\rho$ )/( $1-\rho$ ) + Poisson( $c$ , $c\rho$ ,true)*exp( $c\rho$ ) - Poisson( $c$ , $c\rho$ ,false)*exp( $c\rho$ ))

Is equal to
$\biggl [1 + \frac{(c\rho)^c}{c!(1-\rho)} + \sum\limits_{n=1}^{c-1} \frac{(c\rho)^n}{n!} \biggr]^{-1}=\wp_0$

Now you have the "tricky" $\wp_o$ value, all other values in the M/M/c queuing theory are MUCH easier to calculate.

This may have appeared to be a long and tortuous route, but now we have proved it once, we never need to do it again... we can just use the Excel Poisson formula in our queuing models knowing exactly how it can be applied correctly.

Phew!

Profile cancel

Recent Posts