Skip to main content

Compute sample size and power for 2 variances in Excel

This tutorial explains how to calculate and interpret sample size and power to compare two variances in Excel using XLSTAT.

What is the power of a statistical test?

XLSTAT includes several tests to compare variances and can also calculate the power or the number of observations required for a test based on Fisher's F distribution to compare variances.

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-beta1−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 will take an example of a test on two variances.

We want to measure the heart rate of a number of patients after administration of a drug in order to test whether the drug has an effect on the heart rate. Before collecting the data for a 2-variance test, a sample size calculation is used to obtain a power of 0.9. As we want to have samples of the same size, we choose an N1/N2 ratio of 1.

We do not know yet the parameters of our samples, thus we will use the concept of effect size. This is the ratio of the two variances to be compared.

We will therefore test 3 effects sizes: 0.6, 0.9 and 1.5. Since the effect size is based on the ratio of variances, it is expected that the closer the effect is to 1, the larger the sample size needed will be, since it will be more difficult to obtain two samples with the same variance.

Compute the sample size needed for a comparison of variances test in XLSTAT

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

Power computation for the comparison of two variances in the XLSTAT Menu

In the dialog box, choose the Find sample size goal. We will take as an alternative hypothesis Variance 1 / Variance 2 <> 1.

The alpha is 0.05, the desired power is 0.9.

We will take samples of equal sizes so the ratio N1/N2 is equal to 1. Rather than entering parameters, we select the Effect size option and we take 0.6.

General tab of the computation of sample size for the comparison of two variances in XLSTAT

In the Chart tab, activate the simulation plot. 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 in intervals of 0.01.

Chart tab of the computation of sample size for the comparison of two variances in XLSTAT

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

Interpret the results of sample size computations for a comparison of two variances

The first table displays all the main results followed by an interpretation.

Table returned by the calculation of sample size for the comparison of two variances

We see that 163 observations are needed for each sample to obtain a power as close as possible to 0.9.

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

Results of the calculation of sample size for the comparison of two variances

The simulation plot shows the evolution of the sample size as a function of the power. We see that for a power of 0.8, just over 120 observations per sample are sufficient and that for a power of 0.95 we arrive at 200 observations.

Graph of sample size depending on power in XLSTAT

For effect sizes of 0.9 and 1.5, we obtain the following results:

Result for an effect size of 0.9 in Excel

Result for an effect size of 1.5 in Excel

The sample size drops to 3788 for an effect size of 0.9 and to 258 for an effect size of 1.5.

This clearly confirms the hypothesis made at the beginning of the tutorial: if we want to have samples with a similar variance, we must have a very large sample size.

XLSTAT is a powerful tool both for finding the sample size required for an analysis and for calculating the power of a test. Obviously, if one has more information about their samples, they can give details of the input parameters, rather than entering the effect size.

Was this article useful?

  • Yes
  • No