Mastering Effective Sample Size Calculation in Excel (With Practical Examples)
In this Excel tutorial, I will demonstrate how to calculate the sample size using the Excel application.
Sample size of a population
Use the Cochran’s formula for that purpose. It takes into account the confidence level, the margin of error, and the proportion of the population that has the attribute of interest.
Let’s estimate the percentage of people who prefer the Best Excel Tutorial over other tutorials with a 95% confidence level and a 5% margin of error. Let’s assume that 50% of the population likes the Best Excel Tutorial. You can use this formula: =ROUNDUP((1.96^2*0.5*(1-0.5))/(0.05^2),0)
Sample size of a sample
Use the finite population correction factor, which adjusts the sample size based on the ratio of the sample size to the population size.
To survey 100 viewers out of a total of 500 viewers of Best Excel Tutorial, you can use this formula: =ROUNDUP((1.96^2*0.5*(1-0.5))/(0.05^2)/(1+((1.96^2*0.5*(1-0.5))/(0.05^2)-1)/500),0)
Sample size based on the standard deviation
Use the formula for the margin of error, which relates the sample size, the confidence level, and the standard deviation.
To estimate the average height of students in a class with a 95% confidence level and a 2 cm margin of error, and you know that the standard deviation of the heights is 10 cm, you can use this formula: =ROUNDUP((1.96*10/2)^2,0)
I recommend using one of the methods mentioned above, as manually calculating a sample size can be quite complicated.
