XLSTAT: Calculate Sample Size and Power for 2 Variances in Excel

 

Background

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 (beta) is the probability of failing to reject a false null hypothesis and is crucial yet less studied. The test's power (1-beta) is the chance of correctly rejecting a false null hypothesis. XLSTAT calculates power and beta when parameters are known and determines the necessary sample size for a desired power. These power calculations are typically performed before experiments to estimate the number of observations needed for reliable results.

 

Dataset for Example

Using the attached dataset, we plan to measure patients’ heart rates after drug administration to test for an effect. To achieve 90% power in a two-variance test, we assume equal sample sizes (N1/N2 = 1). Since sample parameters are unknown, we use effect sizes based on variance ratios: 0.6, 0.9, and 1.5. Closer to 1 means larger sample sizes are needed, as it's harder to detect differences when variances are similar.

Setting Up

• Open XLSTAT.

• Click on the Life Sciences/ Power analysis menu and select the Compare variances feature.

• In the dialog box, choose the Find sample size goal.

In the General tab:

• Take as an alternative hypothesis Variance 1 / Variance 2 <> 1.

• The alpha is 0.05, the desired power is 0.9.

• Take a ratio N1/N2 equal to 1 as we want to have samples of the same size.

• Rather than entering parameters, select the Effect size option, and enter 0.6.

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.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpret the Results

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

Each sample requires 163 observations to achieve a power close to 0.9. The table below displays calculations for power values from 0.8 to 0.95.

The simulation plot shows that about 120 observations per sample are needed for 80% power, increasing to 200 for 95% power.

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

Sample sizes decrease to 3788 for an effect size of 0.9 and to 258 for 1.5. This confirms that larger samples are needed to ensure similar variances, especially when effects are small. With more sample information, input parameters can replace effect size for more precise calculations.