Raising the bar

Chapter 14. Excel statistical functions

Unit 14.1. Excel functions for distributions

Here I show how to replace Tables 1 through 9 from the appendix in NCT (that's about 30 pages) with one spreadsheet in Excel or one notebook file in Mathematica. The numbering of tables corresponds to that in NCT. In all cases in the first row of the Excel spreadsheet put names of variables, to be able to use the formulas repetitively. In the second row put numerical values of the variables and the formulas shown, with references to cells that contain numerical values. The values I put are for illustration only. In reality, the cell with the formula will show the function value. In case of Mathematica, type the formula shown, with the values of the arguments, all on one line, and then press Shift+Enter to see the result. Instead of a desktop version of Mathematica, you can use the online version wolframalpha.com for free.

Excel doesn't have functions corresponding to Tables 10$latex -$14. The Excel functions below are for Excel 2010 and 2013. Some functions supply the density value or the distribution function value, depending on the choice of the logical argument TRUE or FALSE. See Excel Help for more details.

Cumulative distribution function of the standard normal distribution. For a given real $latex z$, the value of the distribution function of the standard normal is (see Figure 14.1)

$latex F(z) = \frac{1}{\sqrt{2 \pi}} \int_{-\infty}^z \exp(-t^2 / 2) dt.$

Table 14.1. Distribution function of the standard normal distribution.

In Mathematica, enter (on one line, without spaces)

z=0.5;CDF[NormalDistribution[0,1],z]

Figure 14.1: Cut-off point for standard normal distribution.

Probability function of the binomial distribution. For given number of successes $latex x$, number of trials $latex n$ and probability $latex p$ the probability is

$latex P(\textrm{Binomial} = x) = C_x^n p^x (1 - p)^{n - x}.$

Table 14.2. Probability function of the binomial distribution.

In Mathematica, enter (on one line, without spaces)

x=2;n=4;p=0.1;PDF[BinomialDistribution[n,p],x]

Cumulative binomial probabilities. For a given cut-off value $latex x$, number of trials $latex n$ and probability $latex p$ the cumulative probability is

$latex P(\textrm{Binomial} \leq x) = \sum_{t=0}^x C_t^n p^t (1 - p)^{n - t}.$

Table 14.3. Cumulative binomial probabilities.

In Mathematica, enter (on one line, without spaces)

x=2;n=4;p=0.1;CDF[BinomialDistribution[n,p],x]

Values of the exponential function $latex e^{-\lambda}$.

Table 14.4. Values of the exponential function $latex e^{-\lambda}$.

In Mathematica, enter (on one line, without spaces)

l=0.5;Exp[-l]

Individual Poisson probabilities. For given number of successes $latex x$ and arrival rate $latex \lambda$ the probability is

$latex P(\textrm{Poisson} = x) = \frac{e^{-\lambda} \lambda^x}{x!}.$

Table 14.5. Individual Poisson probabilities.

In Mathematica, enter (on one line, without spaces)

x=3;l=0.1;PDF[PoissonDistribution[l],x]

Cumulative Poisson probabilities. For given cut-off $latex x$ and arrival rate $latex \lambda$ the cumulative probability is

$latex P(\textrm{Poisson} \leq x) = \sum_{t=0}^x \frac{e^{-\lambda} \lambda^t}{t!}.$

Table 14.6. Cumulative Poisson probabilities.

In Mathematica, enter (on one line, without spaces)

x=3;l=0.1;CDF[PoissonDistribution[l],x]

Previous: Questions for repetition (Chapter 13) | Next: Excel functions for critical values

Want the whole book (all 14 chapters, exercises, and the index) as a single PDF? Available on Lulu for $1.99.