How to Calculate Population Proportion in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Data of Population Proportion in Excel


⦿ 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:

p′=x/n

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

Calculating sample proportion


⦿ 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:

The formula to calculate Margin of Error

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.

The 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)

Calculate Margin of Error in Excel by using a formula


⦿ 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:

(p′-E)< P <(p′+E)
  • 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

Getting the left and right boundary of population proportion

  • 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.


Related Articles

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, I am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo