Open In App

Sample Size Calculator With Excel

Improve
Improve
Like Article
Like
Save
Share
Report

The idea of sample size in statistics refers to deciding how many observations or replicates—repeated experimental conditions—should be included in a statistical sample to quantify a phenomenon’s variability. Making conclusions about a population based on a sample is a crucial component of every empirical investigation. Sample sizes are essentially used to represent portions of the population that are selected for any specific survey or investigation. The number of observations chosen from the population when the statistical estimate for the entire population is made is referred to as the sample size in statistics. 

For instance, your sample size is 50 if you test 50 species. Typically, the time, expense, and convenience of collecting the data are taken into account while determining the sample size. Determining the appropriate sample size is crucial because too large or too small samples can waste time, resources, and money, respectively, while insufficient samples can produce inaccurate results. The minimum acceptable size of your survey must therefore be determined. These many conversations will ensure that the results accurately represent the chosen group.

The formula for the sample size calculator:

\frac{\frac{Z^2\cdot p(1-p)}{e^2}}{1+\left ( \frac{Z^2\cdot p(1-p)}{e^2N} \right )}

Where,
Z is Z-Score
p is population proportional
e is marginal error
N is population size.

To calculate the sample size, we need to find:

 terms for finding sample size

 

Population size: The entire amount of people for whom a sample size calculation is necessary.
Sample size: The number of people needed to reach the necessary degree of accuracy.
The margin of error: In opinion poll findings, the margin of error or confidence interval is typically provided. Statistics indicate that for every 20 times you repeat this poll, you will receive an answer that is 95 percent certain to be accurate.

The Sample Size Calculator: How Does It Work?
The sample size calculator should be used as follows:
Step 1: Fill in the appropriate input fields with the confidence level, interval, and prevalence.
Step 2: To obtain the result, click the “Calculate Sample Size” option now.
Step 3: The output field will display the sample size for the provided input.

FOR EXAMPLE,

Question: 
The confidence level is 95%
The population proportion is 0.5
The marginal error is 4%
The population size is 2500
Calculate sample size.

Answer:

Step 1: Entering all the given values in the excel sheet.

Values in excel sheet

Values in excel sheet

Step 2: Now we need to find the alpha divided by 2, for this we have to use the given formula that is:

(1-confidence level)/2

Step 3: To calculate alpha divided by 2:
Write =(1-B2)/2 and then press enter now you will get the value of 2.5%

Calculating alpha divided by 2

Calculating alpha divided by 2

Step 4: We know that 2.5% =0.025 so we will write this in another column.

calculated alpha divided by 2 with its approx. value

calculated alpha divided by 2 with its approx. value

Step 5: Now we will calculate the Z-Score:

For this, we need the NORM.S.INV function and a formula to calculate.
Write =norm
Then select option NORM.S.INV

Write NORMS function.

 

Then it will write that function.

writing values

writing values

Step 6: After that, we have to use the formula that is (1- alpha divided by 2)
Now write,

=NORM.S.INV(1-C7)

Calculating Z-score

Calculating Z-score

Step 7: Then press enter and you will get the value of 1.959963, which is approximately equal to 1.96.

So, we will write the approximate value in another column.

calculated Z-score

calculated Z-score

Step 8: Now we can calculate the sample size by using the formula but this formula is large so we separately calculate the numerator and denominator.

In a separate column, we will calculate the value of the numerator. For calculating the numerator, the formula is

\frac{Z^2\cdot p(1-p)}{e^2}

As we already know that

  • Z is Z-score
  • p is population proportional
  • e is marginal error

For this write

=(C8^2)*(B3*(1-B3))/(B4^2)

calculating numerator

calculating numerator

Step 9: After writing the formula just press enter then we will get a value of 600.25.

calculated numerator value

calculated numerator value


Step 10: Now In another column, we will calculate the denominator by using the formula:

1+\left ( \frac{Z^2\cdot p(1-p)}{e^2N} \right )

As we already know that

  • Z is Z-score
  • p is population proportional
  • e is marginal error
  • N is the population size

For this write

 =1+(C8^2)*(B3*(1-B3))/(B4^2*B5)

calculating denominator

calculating denominator


Step 11: Now after writing the values just press enter and you will get a value of 1.2401.

calculated denominator

calculated denominator


Step 12: Now we calculate the sample size, for this, we have to divide the numerator with the denominator as mentioned in the formula of sample size.

calculating sample size

calculating sample size


Step 13: Now write: 

=(B12/B13)

calculated sample size

calculated sample size

Step 14: Now after writing the values just press enter and you will get a value of 484.0335457.

calculated sample size with its approx. value.

calculated sample size with its approx. value.

The sample size is equal to 484(approx.).

NORM.S.INV

For a given probability value, this Excel function can be used to find or construct the inverse normal cumulative distribution. It gives back the usual normal cumulative distribution’s inverse. The distribution’s mean and standard deviation are both equal to one. It determines the z value (standard normal deviation), where it relates to an area under the curve, given the chance that a variable is within a specific range of the mean.
The region should typically range from 0 to 1. A one-tailed probability P corresponds to the z value (standard normal deviation). where P must fall within the range of 0 and 1 (0<P<1).

Excel’s NORM.S.INV Formula
The NORM.S.INV function in Microsoft Excel has the following syntax or formula: 

=NORM.S.INV(

Its syntax or formula contains the following justification:
Probability: (Required or essential parameter) A probability that fits the normal distribution is what it is.

How to use NORM.S.INV

Question: Find probability using the NORM.S.INV function for less than 0.5 value.

Answer: For this lets, we have to find the probability of value 0.328. So, first, we will write the value of 0.328 in probability value. Now we will write the function NORM.S.INV by writing =Norm. You will find many options in which you have to choose NORM.S.INV.

Finding NORM.S.INV function

Finding NORM.S.INV function

Then goes to cell B2 and press enter.

Using NORM.S.INV for calculating the value'

Using NORM.S.INV for calculating the value

After this, you will get the value of -0.44544 as a result.

calculated value

calculated value



Last Updated : 11 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads