XL: ATP Definition: POISSON

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 4.0, 5.0, 5.0c
• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SUMMARY

The versions of Microsoft Excel listed at the beginning of this article provide a set of special analysis tools called the Analysis ToolPak. This article is part of a series of articles that provides information about the underlying formulas used in the Analysis ToolPak functions.

This article covers the following function:

   POISSON(x,mean,cumulative)

MORE INFORMATION

The POISSON function returns the result of the Poisson probability distribution function for a particular value of the random variable X. It follows the form "Poisson(x,mean,cumlative)", where:

   x = number of events
   mean = expected value or average of the distribution
   cumlative = logical value specifying whether to return the cumlative
   distribution or the probability mass function.

   #include  <math.h>
   
   #define PI 3.141592654
   
   float poidev(xm,idum)
   float xm;
   int *idum;

   {
           static float sq,alxm,g,oldm=(-1.0)
           float em,t,y;
           float ran1(),gammln();
   
           if (xm < 12.0) {
                   if (xm != oldm) {
                           oldm=xm;
                           g=exp(-xm);
                   }
                   em = -1;
                   t = 1.0;
                   do {
                           em += 1.0;
                           t *= ran1(idum);
                   } while (t > g);
           } else {
                   if (xm != oldm) {
                           oldm=xm;
                           sq=sqrt(2.0*xm);
                           alxm=log(xm);
                           g=xm*alxm-gammln(xm+1.0);
                   }
                   do {
                           do {
                                   y=tan(PI*ran1(idum));
                                   em=sq*y*xm;
                           } while (em < 0.0);
                           em=floor(em);
                           t=0.9*(1.0+y*y)*exp(em*alxm-gammln(em+1.0)-g);
                   } while ran1(idum) < t);
           }
           return em;
   }
   

The POISSON function returns the result of the Poisson probability distribution function for a particular value of the random variable X. The Poisson distribution is useful in predicting the number of events over a specific time period; for example the number of ships arriving at a pier between noon and midnight. Given that the mean number of arrivals was 5, to calculate the probability that exactly 3 ships would arrive, use POISSON(3,5,false). To find the probability that 3 or less ships would arrive, use POISSON(3,5,true).

REFERENCES

"Numerical Recipes in C," Press, Flannery et al, pages 218-222

"Microsoft Excel Function Reference," version 4.0, pages 328-329