Al Ikram Amit
In this article, we will see how to calculate the population proportion in Excel. To calculate population proportion, we firstly require sample proportion and margin of error because measuring the mean height of a population is nearly impossible to do. Rather take a sample of say 10 people, measure their heights, and from the sample mean of heights we are going to calculate the mean height of a population.
Download Practice Workbook
You can download the practice workbook from the following download button.
What Is Population Proportion?
Population proportion can simply be defined as a ratio of the number of successes and the corresponding sample size. Since calculating population proportion directly is a costly measure, in real case sample proportion is used to do the job easily with the help of a margin of error.
Steps to Calculate Population Proportion in Excel
Suppose we have a problem to solve shown below:
3 people have red hair out of 100 New Yorkers. We have to construct a 90% confidence interval for the proportion of red hair in the state of New York.
⦿ Step 1: Calculate Sample Proportion
Sample Proportion is the part of a given sample of individuals in larger data that follows a specific characteristic.
The parameter p′ represents the sample proportion and is used to calculate the genuine population proportion. The following equation is used to find sample proportion:
Here,
- p′ = the sample proportion
- x = the number of the sample population that meets the criteria
- n = the whole number of the sample population
Sample Proportion Calculation:
x = 3; n= 100
Now, p′ = x/n
⇒ p′ = 3/100
⇒ p′ = 0.03
⦿ Step 2: Calculate the Margin of Error
Margin of Error is a value that will estimate the percentage of sample points that can differ from real values.
The formula to calculate the Margin of Error is shown below:
Here,
- E= Margin of Error
- Zc= Critical Value
- p′= the sample proportion
- n= the whole number of the sample population
Critical Values represent the rejection region of a hypothesis test for which cases the hypothesis fails.
The following image shows critical values for several confidence intervals.
- Now, to choose the proper Zc value from the list, apply the following formula in cell C6.
=INDEX('Sample Data'!B11:C15,MATCH(C5,'Sample Data'!B11:B15,0),2)- Now, we will calculate the Margin of Error in Excel by using the following formula:
=C5*SQRT(C4*(1-C4)/C6)
⦿ Step 3: Calculate Population Proportion
Now, taking the Margin of Error into account, the following equation will represent the population proportion with a confidence interval:
- As our Margin of Error is 0.0281, we get the left and right boundary of population proportion shown below:
p′- E = 0.03 + 0.0281 =0.0581
p′+ E= 0.03 – 0.0281=0.0019
- So, from the above inequality, we can conclude that the population proportion of redheads among the people of New York state will be between 5.81% to 0.19%.
- Now, we can have a clear idea from the normal distribution shown in the below image. The below picture depicts that the population proportion will range between 0.19% to 5.81% for a confidence level of 90%. So, the range of population proportion for our sample problem will be like in the following range:
0.0019 < P < 0.0581
Or,
0.19% < P < 5.81%
Read More: Population Projection Formula in Excel (3 Applications)
Conclusion
Follow these steps and stages on how to calculate population proportion in excel. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.
