EXCEL 97: TDIST, TINV, NORMSDIST and NORMSINV

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

This September 1999 help sheet gives information on how to obtain:

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

PROBABILITIES AND INVERSE PROBABILITIES

We consider the standard normal distribution as an example.

Let Z be a standard normal random variable, z be a value of the random variable, and p be a probability. Then:

Excel does this for the following distributions, presented in approximate order of most commonly used in the analysis of economics data:


NORMAL PROBABILITIES AND INVERSE-PROBABILITIES

These use NORMDIST and NORMINV.

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

Choose Function Wizard (f_x) / Statistical / NORMDIST / Next. Then
 
x 9
mean 20
standard_dev 2.1909
cumulative 1

gives answer 0.67596.

Alternatively, in the cell type =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 mu =8 and variance 4.8, so standard deviation = sigma = sqrt(4.8) = 2.1909.

Choose Function Wizard (f_x) / Statistical / NORMINV / Next. Then
 
probability 0.9
mean 8
standard_dev 2.1909

gives answer 10.8077.

Alternatively, in the cell type =NORMINV(0.9, 8, 2.1909) and hit <enter>.
 

STANDARD NORMAL PROBABILITIES AND INVERSE-PROBABILITIES

These use NORMDIST and NORMINV.

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

Choose Function Wizard (f_x) / Statistical / NORMSDIST / Next. Then
 
x 1.9

gives answer 0.9713.

Alternatively, in the cell type =NORMSDIST(1.9) and hit <enter>.

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

Choose Function Wizard (f_x) / Statistical / NORMSINV / Next. Then
 
probability 0.9

gives answer 1.2816.

Alternatively, in the cell type =NORMSINV(0.9) and hit <enter>.
 

T-DISTRIBUTION PROBABILITIES AND INVERSE-PROBABILITIES

These use TDIST and TINV.
The format and results of these commands differ from those for the normal.

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 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 can obtain Pr(x > 1.9).
Choose Function Wizard (f_x) / Statistical / TDIST / Next. Then
 
x 1.9
degrees_freedom 9
tails 1

gives answer that Pr(x > 1.9) = 0.0449.
So Pr(x <= 1.9) = 1 - 0.0449 = 0.9551.

Alternatively, in the cell type =TDIST(1.9, 9, 2) and hit <enter> gives Pr(x > 1.9).
So Pr(x <= 1.9) = 1 - TDIST(1.9, 9, 2).

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.
Choose Function Wizard (f_x) / Statistical / TINV / Next. Then
 
probability 0.2
degrees_freedom 9

gives Pr(|x| > 1.383) = 0.2.
So Pr(x <= 1.383) = 0.9.

Alternatively, in the cell type =TINV(0.2, 9) and hit <enter> to get Pr(|x| > 1.383) = 0.2.
 

RANDOM NUMBER GENERATION

This generates a random sample of observations from a distribution you specify.

Use Tools / Data Analysis / Random Number Generation.
This permits generation from

Example: Normal

Generate 50 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 Tools / Data Analysis / Random Number Generation / Normal. Then
 
Number of Variables 1
Number of Random Numbers 50
Distribution Normal
Mean 8
Standard Dev 2.1909
Random seed 140
Output range b1

gives values of mostly 4 to 12 in cells b1:b50.
 

For further information on how to use Excel
   go to http://www.econ.ucdavis.edu/faculty/cameron