"Backsolve" Poisson function

[SteveG]

I am trying to estimate the number of spare parts required to give say
95% protection level.

At the moment I have used the Poisson function to calculate the
protection level with various number of spares. I then use MATCH to
find the first value that exceeds the required protection level.

Is there a way of reversing the POISSON calculation so that I input
protection level plus Mean demand (cumulative = True) and output the
number of spare parts required?

 

[ryguy7272]

View this site:
http://support.microsoft.com/kb/828130
Copy/paste data into a .txt file and then copy/paste from there into a
worksheet.

In Cell D3 enter:
=C3*$B$1
Fill down...


Would this work for you?

Regards,
Ryan---

 

[SteveG]

Both my original technique and this new technique use an intermediate
table.
I was trying to avoid a table of pre-calculated results.

Thanks

 

[Jerry W. Lewis]

Mathematically, =POISSON(x,m,TRUE) is equivalent to each of the following
expressions
=CHIDIST(2*m,2*(x+1))
=1-GAMMADIST(m,x+1,1,TRUE)
So you can use CHIINV and GAMMAINV to obtain Poisson confidence limits.

Numerically, this will fail if m needs to be too large, because of
weaknesses in Excel's implementation of CHIDIST, GAMMADIST, and their
inverses.

A world-class (for both accuracy and working range) library of VBA
probability functions is available in
http://members.aol.com/iandjmsmith/examples.xls
which also explicitly includes confidence limit functions for discrete
distributions.

Jerry

 

[SteveG]

This is where my Knowledge Stats < Knowledge Excel! (I inherited a
spreadsheet using Poisson.)

In the poisson calculation I have Probability = POISSON(x,m,TRUE)
where x is the number of spares and m is the 'demand'.

Ideally I would reverse this calculation inputing the required
probability and obtain x (the spares level required).

What you show is Probability =CHIDIST(X,degrees_freedom)=
CHIDIST(2*m,2*(x+1) or Probability =1-
GAMMADIST(X,alpha,beta,cumulative)=1-GAMMADIST(m,x+1,1,TRUE).

When I turn to CHIINV(probability,degrees_freedom) or
GAMMAINV(probability,alpha,beta) I still see the degrees of freedom
or Alpha parameters as inputs when they contain the x number of
spares.

This leaves me confused ...

.... But fortunately in the examples.xls sheet I see the
crit_poisson(B70,D70) calculation which fortunately seems to match my
original calculations!

So I will probably use this!

Thanks
Steve