XLSTAT: Calculating Sample Size and Power for Linear Regression 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 but less studied. The test's power (1-beta) is the chance of correctly rejecting a false hypothesis. XLSTAT calculates power and beta when parameters are known and determines the necessary sample size for a desired power. Power calculations are typically performed before experiments to estimate the required sample size for accurate results.

Dataset for Example

With the attached example, we will analyze children's weights based on height and age using linear regression to test if the R² is significantly above zero.

With two predictors, we aim for a power of 0.9. Since sample parameters are unknown, we use Cohen's effect size, testing three levels: 0.02 (small), 0.15 (moderate), and 0.35 (large).

Larger effect sizes require smaller sample sizes to achieve the desired power.

 

Setting Up

• Open XLSTAT.

• Click on the Life Sciences/ Power analysis menu and select the Linear regression feature.

• Once the button is clicked, the dialog box appears.

In the General tab:

• Choose the objective Find the sample size.

• Select the test R² different from 0.

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

• The number of predictors or explanatory variables is 2.

• Rather than detailed input parameters, set the effect size to 0.2 to represent a weak effect.

In the Chart tab:

• The option simulation plot is activated and the “size of sample 1” will be displayed on the vertical axis and the “power” on the horizontal 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 parameters used as input. In our case, only the number of predictors is displayed.

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

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

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

For effect sizes of 0.15 and 0.35, we obtain the following results:

 

Conclusion

As R² increases, required sample size decreases; for a large effect, only 39 observations are needed for 90% power. If weight variability is well explained by age and height (R² near 1), 39 observations suffice. With more sample information, input parameters should replace effect size in calculations.