XLSTAT: Calculate Sample Size and Statistical Power for the Comparison of 2 Means in Excel

 

Background

XLSTAT’s Parametric tests menu offers tools like t and z tests, with options to estimate test power and required sample size.

When setting up a test, one must define:

• The null and alternative hypotheses (H₀ vs. Hₐ),

• The test type,

• The type I error rate (α, typically 5%),

• And consider the type II error (β), with power = 1 - β.

Power reflects the probability of correctly rejecting a false H₀ and should be maximized. XLSTAT computes power or, given a target power, the needed sample size—useful for planning experiments.

In this example, we compare two independent samples, aiming for 0.9 power under H₀: Mean₁ − Mean₂ = 0. Without known parameters, we use Cohen’s effect sizes:

• 0.2 (small),

• 0.5 (moderate),

• 0.8 (large).

Larger effects require smaller sample sizes to reach the same power.

Setting Up

• Open XLSTAT.

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

• Clicking the button opens the dialog box. In the General tab:

  • Choose Find the sample size as the goal.

  • Select t test for two independent samples.

  • Set the alternative hypothesis to Mean 1 ≠ Mean 2.

  • Set alpha to 0.05 and power to 0.9.

  • Assume equal sample sizes: set N1/N2 ratio to 1.

 

• In the Effect size tab, enter 0.2 for a small effect.

 

• In the Charts tab:

  • Activate the Simulation plot option.

  • Select the Power on the horizontal axis. The size of sample 1 will be represented on the vertical axis.

  • Power varies between 0.8 and 0.95 by increments of 0.01.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results 

The first table shows the calculation results and an interpretation of the results.

We see it takes 526 observations per sample to obtain an output as close as possible to 0.9.

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

The simulation plot shows the evolution of the sample size depending on the power. We see that for a power of 0.8, just slightly more than 393 observations per sample and as a power of 0.95 we get to 651 observations.

For effect sizes of 0.5 we obtain the following results:

For effect sizes of 0.8 we obtain the following results:

As the difference between means increases, the required sample size decreases—for a large effect, only 34 observations per group are needed.

XLSTAT is a valuable tool for determining required sample size or test power. If more information is available, detailed input parameters can be used instead of effect size.