Inverse CDF of Poisson dist in Excel

Question

I want to know is there a function to calculate the inverse cdf of poisson distribution? So that I can use inverse CDF of poisson to generate a set of poisson distributed random number.

Answer 1

A) Inverse CDF of Poisson distribution

The inverse CDF at q is also referred to as the q quantile of a distribution. For a discrete distribution distribution . the inverse CDF at q is the smallest integer x such that CDF[dist,x]≥q.. The Poisson distribution is a discrete distribution that models the number of events based on a constant rate of occurrence. The Poisson distribution can be used as an approximation to the binomial when the number of independent trials is large and the probability of success is small. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute.

Formula

The probability mass function (PMF) is:

mean = λ

variance = λ

Notation

Term    Description
e       base of the natural logarithm

Reference: Methods and Formulas for Inverse Cumulative Distribution Functions

B) Excel Function: Excel provides the following function for the Poisson distribution:

POISSON(x, μ, cum) where μ = the mean of the distribution and cum takes the values TRUE and FALSE

POISSON(x, μ, FALSE) = probability density function value f(x) at the value x for the Poisson distribution with mean μ.

POISSON(x, μ, TRUE)= cumulative probability distribution function F(x) at the value x for the Poisson distribution with mean μ.

Excel 2010/2013/2016 provide the additional function POISSON.DIST which is equivalent to POISSON.

Reference: Office Support POISSON.DIST Function

C) Excel doesn’t provide a worksheet function for the inverse of the Poisson distribution.

Instead you can use the following function provided by the Real Statistics Resource Pack. It’s a free download for Excel various versions. POISSON_INV(p, μ) = smallest integer x such that POISSON(x, μ, TRUE) ≥ p Note that the maximum value of x is 1,024,000,000. A value higher than this indicates an error.

Reference: Real Statistics Using Excel

D)
Reference to MREXCEL.COM web site a query related to your question quoted below seems to be related to your question.

Not sure if anyone can help with this. Basically I'm trying to find out how to apply the reverse of the Poisson function in excel. So as of now I have poisson(x value, mean, true-cumulative) and that lets me get the probability for that occurence. Basically I want to know how I can get the minimum/maximum x value based on a given probability. So if I have a list of data (700 rows) and I want to find out what the minimum starting value should be given a desired average and the fact that I want the lowest value to be at the 0.05% probability. So 0.05% = (x, 35, True) solve for x. I know I can prob do this with solver, but I am trying to figure out a way to do this formulaicly without having to use the solver (as I may have to use this many times).

The code referred to here covers the inverse of the poisson formula when using True in the excel formula. It does not cover the inverse of the poisson formula when using False in the excel formula.

       Re: Reverse Poisson?
  Originally Posted by shg  
A further mod to accommodate large means:
Code:
Function PoissonInv(Prob As Double, Mean As Double) As Variant
  ' shg 2011, 2012, 2014, 2015-0415

  ' For a Poisson process with mean Mean, returns a three-element array:
  '   o The smallest integer N such that POISSON(N, Mean, True) >= Prob
  '   o The CDF for N-1 (which is < Prob)
  '   o The CDF for N (which is >= Prob)

-------Reference :> https://www.mrexcel.com/forum/excel-questions/507508-reverse-poisson-2.html>

E) Why doesn't Excel have a POISSON.INV function?

Discussion on Referred web page have references to some formulas for calculating related information desired by OP.

You could use the following. With the Poisson mean named lambda, enter the following in an newly inserted worksheet.

A1:  =IF(ROWS(A$1:A1)<=4*lambda,POISSON(ROWS(A$1:A1)-1,lambda,1))

Fill A1 down into A2:A1000 (4 times as many rows as your most typical lambda value). Name the A1:A1000 range POISSON.CDF. Then use the formula

 =MATCH(n,POISSON.CDF)-1

to give the results a POISSON.INV(n,lambda) function would. If you want this for varying lambda, use the array formula

=MATCH(n,POISSON(ROW($A$1:INDEX($A:$A,4*lambda+1),lambda,1))-1

Reference Shared Link

Hope That Helps.

Answer 2

=MATCH(RAND(),MMULT((ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(MAX(lambda,5+lambda* 45/50)+6* SQRT(lambda)+3,1)))=COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,MAX(lambda,5+lambda* 45/50)+6* SQRT(lambda)+2))))+0,MMULT((ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(MAX(lambda,5+lambda* 45/50)+6* SQRT(lambda)+2,1)))=(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,MAX(lambda,5+lambda* 45/50)+6* SQRT(lambda)+1)))+1))+0,POISSON(ROW($A$1:INDEX($A:$A,MAX(lambda,5+lambda* 45/50)+6* SQRT(lambda)+1))-1,lambda,1)))+(ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(MAX(lambda,5+lambda* 45/50)+6* SQRT(lambda)+3,1)))=(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,1)))+FLOOR(MAX(lambda,5+lambda* 45/50)+6* SQRT(lambda)+2,1)))+0)-1

It is quite slow for lambda >1000.

This expands on the array formula

=MATCH(C4,POISSON(ROW($A$1:INDEX($A:$A,4*lambda+1)),lambda,1))-1

shared above by skkakkar, by prepending the array with 0 and appending with 1, following Is there a way to concatenate two arrays in Excel without VBA? .

The rest is mostly making the array shorter by replacing 4* lambda with 6* SQRT(lambda).

Answer 3

Just stumbled across this question. I have an imperfect solution that I will add to the mix. Since the Poisson distribution is the limit of a binomial distribution, we can use BINOM.INV. Specifically, if L is the mean of your Poisson and p is the probability of interest and K is a large number, then a good approximation for the desired inverse is given by:

BINOM.INV(L*K,1/K,p)

Experimentally, the probability that this inverse disagrees with the true inverse appears to be bounded above by:

If anyone has a nice mathy reason that this is true, I would love to hear it. Therefore, if a significance level of A is desired, e.g. A = 0.05, then we can instead use:

BINOM.INV(ROUNDUP(L*SQRT(L)/A,0),L/ROUNDUP(L*SQRT(L)/A,0),p)

You could even define custom functions in your workbook using LAMBDA. In the Formulas tab in the ribbon, open Name Manager and add three new names. Name the first Trials_Helper, and in the 'Refers to:' section enter:

=LAMBDA(L,A,ROUNDUP(L*SQRT(L)/A,0))

Name the second Inverse_Helper, and in the 'Refers to:' section enter:

=LAMBDA(L,trials,p,BINOM.INV(trials,L/trials,p))

Name the third Poisson_Inv, and in the 'Refers to:' section enter:

=LAMBDA(L,A,p,Inverse_Helper(L,Trials_Helper(L,A),p))

This function can be called like any other Excel function and will approximate the Poisson inverse at the specified significance level.

With these defined, we could further define workbook functions to simulate from a Poisson distribution. For example, we could define Poisson_Rand and Poisson_RandArray as below:

=LAMBDA(L,A,Poisson_Inv(L,A,RAND()))
=LAMBDA(L,A,rows,columns,Poisson_Inv(L,A,RANDARRAY(rows,columns)))

As a final note, we could use these to define an inverse and a random sampler for a Negative Binomial distribution (which Excel also lacks). The Negative Binomial distribution is the mixture of a Poisson by a Gamma. Therefore, given parameters r (# of successes) and q (success probability), and given the probability of interest p, the inverse of the Negative Binomial CDF is:

Poisson_Inv(GAMMA.INV(RAND(),r,(1-q)/q),A,p)

Some care needs to be taken in defining this function, though. The underlying GAMMA needs to be simulated from as many times as the size of the array passed for p. Therefore, we could define the name NegBinom_Inv:

=LAMBDA(r,q,A,p,Poisson_Inv(GAMMA.INV(RANDARRAY(ROWS(p),COLUMNS(p)),r,(1-q)/q),A,p))

We could then define NegBinom_Rand and NegBinom_RandArray as:

=LAMBDA(r,q,A,NegBinom_Inv(r,q,A,RAND()))
=LAMBDA(r,q,A,rows,columns,NegBinom_Inv(r,q,A,RANDARRAY(rows,columns)))