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?

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---

--
RyGuy


"SteveG" wrote:

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?

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

Thanks

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" wrote:

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?

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

Dear Steve, Dear all,

This leaves me confused too!

I guess I'm faced with a simular problem as yours. I am trying to estimate the number of spare parts required to keep in stock to give me a 90% Service level (protection level).

At the moment, the reverse calculation for demand with normal distribution is no problem ( x = NORMSINV ("ServiceLevel") ) however I could not find such for Poisson demand. So, simular to your earlier question;
input = Service level and Mean demand (cumulative = True)
and output the number of spare parts required?

I follow your reasoning and I fully agree with your conclusion; Unfortunately, in my version of Excel, I can not find anything close to the "crit_poisson(B70,D70)" that you SteveG are using. My version of Excel is "Excel 2010".

Can anyone help me to solve this using Excel 2010 and/or by not using the "crit_poisson()" function? Thanks a lot! KRG Erik