Compute sample size and power for a comparison of proportions in Excel

This tutorial explains how to calculate the sample size and power for a comparison of proportions in Excel using XLSTAT.

What is the power of a statistical test?

XLSTAT offers several parametric tests and nonparametric tests to compare proportions. Thus we can use the z-test, chi-square test, the sign test or the McNemar test. XLSTAT can calculate the power or the number of observations necessary for these tests using either exact methods or approximations.

When testing a hypothesis using a statistical test, there are several decisions to take:

• The null hypothesis H0 and the alternative hypothesis Ha.

• The statistical test to use.

• The type I error also known as alpha. It occurs when one rejects the null hypothesis when it is true. It is set a priori for each test and is 5%.

The type II error or beta is less studied but is of great importance. In fact, it represents the probability that one does not reject the null hypothesis when it is false. We cannot fix it upfront, but based on other parameters of the model we can try to minimize it. The power of a test is calculated as 1−beta and represents the probability that we reject the null hypothesis when it is false.

We therefore wish to maximize the power of the test. XLSTAT calculates the power (and beta) when other parameters are known. For a given power, it also allows to calculate the sample size that is necessary to reach that power.

The statistical power calculations are usually done before the experiment is conducted. The main application of power calculations is to estimate the number of observations necessary to properly conduct an experiment.

Goal of this tutorial

We want to compare the known proportions of male and female managers working in pubic relations in the USA who gain from $ 50,000 to $ 74,999. Before collecting data for a 2-proportion test, a sample size calculation is used to obtain a power of 0.9.

Setting up the sample size calculation for a comparison of proportions

Once XLSTAT has been launched, click on the Power icon and choose the Compare proportions function.

Once the button is clicked, the dialog box pops up.

Choose the goal Find the sample size, and the Two proportions test (arcsin approx.). We will take as an alternative hypothesis Proportion 1 - Proportion 2 <> 0.

The alpha is 0.05, the desired power is 0.9.

As the male/female ratio in the United States is 0.97, this value is chosen from the N1/N2 ratio. Then we enter the proportion of female managers of 0.25 in proportion 1 and the proportion of male managers of 0.2932 in proportion 2.

In the Chart tab, the Simulation plot option is activated and the size of sample 1 will be represented on the vertical axis and the power on the horizontal axis.

The power varies between 0.8 and 0.95 by steps of 0.01.

Once you click the OK button, the calculations are done and then the results are displayed.

Interpreting the results of a sample size calculation for a comparison of proportions

The first table gathers the results of the calculations as well as an interpretation.

We see that 2259 observations are needed for the sample of women and 2191 observations for the sample of men to obtain a power as close as possible to 0.9.

The following table gathers the calculations obtained for each power value between 0.8 and 0.95.

The simulation graph shows the evolution of the sample size as a function of the power. We see that for a power of 0.8, 1688 women and 1637 men are enough, and that for a power of 0.95 we arrive at 2794 women and 2710 men.

XLSTAT is a powerful tool both for finding the appropriate sample size for an analysis and for calculating the power of a statistical test.