Calculate the optimal sample size in Excel

This tutorial will show you how to calculate the optimal sample size for your survey in Excel using XLSTAT.

Data to calculate sample size

In order to obtain good results during a statistical study or market study, it is necessary to have a sample that is as representative as possible of the total population. XLSTAT allows you to calculate the right number of people to interview to obtain a sample that is neither too large nor too small.

Several variables must be considered for this:

• The size of the target population

• The margin of error

• The confidence level

In this tutorial, we want to conduct a telephone survey. For cost reasons, we do not want to interview the 5,000 people in our population. We will therefore look for the optimal sample size to obtain results with a confidence level of 95% and a margin of error of 2%.

Setting up the calculation of the optimal sample size in XLSTAT

• Open XLSTAT.

• Select the XLSTAT/ Advanced functions / Marketing tools / Sample size. The dialog box pops up.

• In the XLSTAT interface, enter the following analysis parameters: 5000 for the sample size, 2% for the margin of error, and 95% for the confidence level. Click on OK.

Interpret the results for sample size calculation

In order to obtain results with a confidence level of 95% and a margin of error of 2%, it will be necessary to validate surveys of 1623 people, just over 32% of the total population, as summarized by the waffle chart which makes it possible to represent the percentage of the sample size compared to the size of the population.