Excel supplies several statistical functions for working with probability distributions. It’s very unlikely that you’ll ever work with any of these functions. A couple of them, though — the ZTEST and the POISSON functions, in particular — are actually pretty useful.
POISSON: Poisson distribution probabilities
The POISSON function calculates probabilities for Poisson distributions. The function uses the syntax
=POISSON(x,mean,cumulative)
where x is the number of events, is the arithmetic mean, and cumulative is a switch. If set to TRUE, this switch tells Excel to calculate the Poisson probability of a variable being less than or equal to x; if set to FALSE, it tells Excel to calculate the Poisson probability of a variable being exactly equal to x.
To illustrate how the Poisson function works, suppose you want to look at some probabilities associated with cars arriving as a drive-through car wash. (This type of analysis of events occurring over a specified time interval is a common application of Poisson distributions.) If on average, 20 cars drive up an hour, you can calculate the probability that exactly 15 cars will drive up using the formula
=POISSON(15,20,FALSE)
This function indicated that there’s roughly a 5-percent chance that exactly 15 cars will drive up in an hour.
To calculate the probability that 15 cars or fewer will drive up in an hour, use the following formula:
=POISSON(15,20,TRUE)
This function returns the value 0.051648854, indicating that there’s roughly a 16-percent chance that 15 or fewer cars will drive up in an hour.
ZTEST: Probability of a z-test
The ZTEST function calculates the probability that a value comes from the same population as a sample. The function uses the syntax
=ZTEST(<i>array</i>,<i>x</i>,[<i>sigma</i>])
where array is the worksheet range holding your sample, x is the value you want to test, and (optionally) sigma is the standard deviation of the population. If you omit sigma, Excel uses the sample standard deviation.
For example, to find the probability that the value 75 comes from the population as the sample stored in the worksheet range A1:A10, use the following formula:
=ZTEST(A1:A10,75)
