XL: ATP Definition: POISSON

ID: Q87862

The information in this article applies to:

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,cumulative)", where:


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

The Microsoft Excel function approximates the Poisson distribution with the following code:


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

   }

NOTE: The corresponding code for the gammln function can be found by querying on keywords "gammln" and "code".

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

Additional query words: XL98 XL97 XL7 XL5 XL4 98 97 atp tool pak pack

Keywords :
Version : WINDOWS:4.0,5.0,7.0,97; MACINTOSH:4.0,5.0,98
Platform : MACINTOSH WINDOWS
Issue type :