Compute sample size and power for linear regression in Excel

This tutorial shows how to compute the sample size and power in a linear regression using the XLSTAT software.

What is the power of a statistical test?

XLSTAT can estimate the power or calculate the appropriate number of observations associated with variations of R ² in the framework of a linear regression model.

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 up front 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 wish to study the weights of children according to their height and age using linear regression. Precisely, we want to know if the R² of this model is significantly different from 0.

There will be two independent variables or predictors and we would like to know how many children should be interviewed to obtain a power of 0.9. Since we do not yet know the parameters of our samples, we will use the concept of effect size. Cohen (1988) introduced this concept which provides an order of magnitude for the effect size. So we will test three effect sizes: 0.02 for a small effect, 0.15 for a moderate effect and 0.35 for a strong effect. It is expected that the larger the effect size is, the smaller the sample size required will be.

Setting up the calculation of statistical power in a linear regression model

After opening XLSTAT, click the Power icon and choose Linear regression.

Once the button is clicked, the dialog box appears. You must then choose the objective Find the sample size.

Then 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, we select the effect size option and enter the value 0.02 for 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.

Once you have clicked on the OK button, the calculations begin, and then the results are displayed.

Results of the calculation of statistical power in a linear regression model

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.

We see it takes 66 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 50 observations per sample and as a power of 0.95 we get to 80 observations.

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

The sample size will therefore fall as the R² moves away from 0 and we see that for a large difference, 39 observations will be sufficient.

So, if we assume that the variability of the weight variable is well explained by age and height (R ² close to 1), then 39 observations will be sufficient to obtain a power of 0.9.

XLSTAT is a powerful tool both to investigate the sample size required for an analysis and to calculate the power of a test. Obviously, if one has more information about the samples or populations, then input parameters should be used instead of the effect size.