EXCEL 97: Univariate Proportions

EXCEL 97: Proportions

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

This September 1999 help sheet gives information on

confidence interval for proportions
hypothesis test for proportions

A proportion is the fraction of the population choosing a particular action, such as employer or vote for a particular Presidential candidate.
This is not usually encountered in analysis of economics data, but is provided for completeness.

There is no separate command for analyzing proportions in Excel.
There are two ways to proceed in the usual case that samples are large (number of trials greater than 30)

Directly type into Excel the formulae for proportions from a textbook.
Create a column of data of length n with a 1 for each success and a 0 for each failure and use Tools | Descriptive Analysis in the usual way..

These two methods give slightly different results though both are valid.
The first method is preferred and presented in textbooks and we give it here.

THEORY

In general there are n independent trials each with probability of success pi.
Our estimate of pi is p = the number of success divided by n.
A central limit theorem gives p is normally distributed with mean pi and variance pi*(1-pi)/n.

A 95% confidence interval is
pi lies within p +/- z(.025) * sqrt(p*(1-p)/n)

To test the hypothesis that pi equals a particular value, say pi0, we form the test statistic
(p - pi0) / sqrt(pi0*(1-pi0)/n)
which is standard normal under the hypothesis that pi = pi0.

DATA EXAMPLE

As an example, in a sample of size 90, 50 people preferred Pepsi to Coke.
We want to test the claim that the majority of the population prefer Pepsi.

Here pi is the probability of choosing Pepsi.
The estimate of pi is p = 50/90 = 0.55555.

CONFIDENCE INTERVALS FOR PROPORTIONS

A 95% confidence interval is
pi lies within p +/- z(.025) * sqrt(p*(1-p)/n)

We calculate this in Excel using the numbers for this example.
The CI is 0.55555 +/- NORMSINV(.975) * sqrt(0.55555*(1-0.55555)/90)
equals 0.55555 +/- 1.960 * 0.052378
equals 0.55555 +/- 0.103

HYPOTHESIS TESTS FOR PROPORTIONS

The z-test statistic is
z = (p - pi0) / sqrt(pi0*(1-pi0)/n)

We calculate this in Excel using the numbers for this example.
z = (0.55555 - 0.5) / sqrt(0.5*(1-0.5)/90)
or 0.055555 / 0.052704
or 1.054085

Here we conduct a one-sided test of H0: pi <= 0.5 against Ha: pi0 > 0.5.
Then p-value = 1 - NORMSINV(1.054085) = 1 - 0.854 = 0.146.
We do not reject H0 at the 5% significance level since p-value is not < .05.

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