how to create a probability density function in excel

EXCEL 2007: TDIST, TINV, NORMSDIST and NORMSINV

A. Colin Cameron, Dept. of Economics, Univ. of Calif. - Davis

This January 2009 help sheet gives information on how to obtain:

Probabilities and inverse probabilities in Excel
T-Distribution probabilities and inverse probabilities
Normal distribution probabilities and inverse probabilities
Othrr distributions
Random draws from distributions such as the normal.

These are useful in introductory statistics classes.
Their main use in analysis of economics data is to obtain p-values (use TINV) or critical values (use TDIST).

PROBABILITIES AND INVERSE PROBABILITIES

We consider the standard normal distribution as an example.

Let X be random variable, x be a value of the random variable, and p be a probability. Then:

A probability such as Pr(X <= x) is given by the cumulative distribution function.
So the Excel command includes "DIST"
e.g. TDIST for the T distribution
e.g. NORMSDIST for the standard normal distribution
e.g. NORMDIST for the normal distribution

A value of x such that Pr(X <= x) = p for some specified value of p is called the inverse of the cumulative distribution function.
So the Excel command includes "INV"
e.g. TINV for the T distribution
e.g. NORMSINV for the standard normal distribution
e.g. NORMINV for the normal distributio

These functions are given in Formulas Tab | Function Library Group | More Functions | Statistical.

T-DISTRIBUTION PROBABILITIES AND INVERSE-PROBABILITIES

These are the most commonly-used probabilities in statistical analysis of economics data.
These use the TDIST and TINV functions.

TDIST gives the probability of being in the right tail i.e. Pr(X > x), or of being in both tails i.e. Pr(|X| > x).
TINV considers the inverse of the probability of being in both tails.

1. Find Pr(X <= 1.9) when x is t-distributed with 9 degrees of freedom.

This is 1 - Pr(X > 1.9) where Excel function TDIST gives Pr(X > 1.9).

Choose Formulas Tab | Function Library Group | More Functions | Statistical | TDIST.
Fill in the Function Arguments Tab:

T Distribution probability

This gives result that Pr(X > 1.9) = 0.0449.
So Pr(X <= 1.9) = 1 - 0.0449 = 0.9551.

Much simpler is to directly type in the cell = 1 - TDIST(1.9, 9, 2) and hit <enter>.

T distribution inverse probability

2. Find the value x* such that Pr(X <= x*) = 0.9 when x is t-distributed with 9 degrees of freedom.

This is the same value as that for which Pr(|X| >= x*) = 0.2.
(Since there is probability 0.1 in the right tail and probability 0.1 in the left tail).

Choose Formulas Tab | Function Library Group | More Functions | Statistical | TINV.
Fill in the Function Arguments Tab:

T distribution inverse probability

This gives result that Pr(|X| > 1.383) = 0.2.
So Pr(X <= 1.383) = 0.9.

Much simpler is to directly type in the cell =TINV(0.2, 9) and hit <enter> to get Pr(|X| > 1.383) = 0.2.

T distribution probabilities

STANDARD NORMAL PROBABILITIES AND INVERSE-PROBABILITIES

These are less used than the t-distribution in statistical analysis of economics data.
These use the NORMDIST and NORMINV functions.

IMPORTANT: The format and results of these commands differ from those for the normal.
NORMDIST directly gives the cumulative distribution function i.e. Pr(X <= x), whereas TDIST instead gives the right tail, i.e. Pr(X > x) !!
NORMINV considers the inverse of the probability of being in both tails, similar to TINV.

1. Find Pr(X <= 1.9) when x is standard normal (i.e. normal with mean=0 and variance=1).

Choose Formulas Tab | Function Library Group | More Functions | Statistical | NORMDIST.
Fill in the Function Arguments Tab with Z value of 1.9.
This gives result that Pr(X <= 1.9) = 0.9713.

Much simpler is to directly type in the cell =NORMSDIST(1.9) and hit <enter> to get Pr(X <= 1.9) = 0.9713.

2. Find the value x* such that Pr(X <= x*) = 0.9 when x is standard normal.

Choose Formulas Tab | Function Library Group | More Functions | Statistical | NORMDIST.
Fill in the Function Arguments Tab with probability value of 0.9.
This gives result that x* = 1.2816, i.e. Pr(X <= 1.2816) = 0.9.

Much simpler is to directly type in the cell = NORMSINV(0.9) and hit <enter> to get x* = 1.2816.

NORMAL PROBABILITIES AND INVERSE-PROBABILITIES

The standard normal sets the mean to 0 and standard deviation to 1.
Here we consider the normal distribution with other values for the mean µ and standard devation σ.
THE functions used are NORMDIST and NORMINV.

1. Find Pr(X <= 9) when x is normal with mean µ =8 and variance 4.8.
Here standard deviation = σ = sqrt(4.8) = 2.1909.

Choose Formulas Tab | Function Library Group | More Functions | Statistical | NORMDIST.
Fill in the Function Arguments Tab

Normal probability

This gives result that Pr(X > 9) = 0.67596 for X normally distributed wuith mean 8 and variance 4.8.

Much simpler is to directly type in the cell = NORMDIST(9, 8, 2.1909, 1) and hit <enter>.

2. Find the value x* such that Pr(X <= x*) = 0.9 when x is normal with mean µ =8 and variance 4.8, so standard deviation = σ = sqrt(4.8) = 2.1909.

Choose Formulas Tab | Function Library Group | More Functions | Statistical | NORMINV.
Fill in the Function Arguments Tab:

Normal inverse probability

probability	0.9
mean	8
standard_dev	2.1909

This gives result x* = 10.8077. i.e. Pr(X < 10.8077) = 0.9 when x is normal with mean µ =8 and variance 4.8.

Much simpler is to directly type in the cell = NORMINV(0.9, 8, 2.1909) and hit <enter>.

OTHER DISTRIBUTIONSExcel provides probabilities for the following distributions (in Formulas Tab | Function Library Group | More Functions | Statistical), presented in approximate order of most commonly used in the analysis of economics data:

Normal: NORMDIST, NORMINV
Standard normal: NORMSDIST, NORMSINV
t-distribution: TDIST, TINV
F-distribution: FDIST, FINV
Chi-square: CHIDIST, CHIINV
Lognormal: LOGNORMDIST, LOGINV
Binomial: BINOMDIST, CRITBINOM
Hypergeometric: HYPGEOMDIST
Beta: BETADIST, BETAINV
Gamma: GAMMADIST, GAMMAINV
Exponential: EXPONDIST
Weibull: WEIBULL
Poisson: POISSON
Negative binomial: NEGBINOMDIST

RANDOM NUMBER GENERATION

It can be useful to generates a random sample of observations from a specified distribution, such as the standard normal.

Use Data Tab | Analysis Group | Data Analysis.
This permits generation from

Normal
Uniform
Bernoulli (0 or 1)
Binomial
Poisson
Discrete (you provide the values and probabilities for a discrete distribution with finite number of possible values)
Patterned

Example: Normal

Generate 1000 values of x where x is normal with mean mu = 8 and variance 4.8, so standard deviation = sigma = sqrt(4.8) = 2.1909.

Choose Data | Analysis | Data Analysis | Random Number Generation.
Then in the Random Number Generation dialog box fill in:

Normal random number generation