Excel: Calculate Standard Error of Proportion

In statistics, a sample proportion (p̂) represents the proportion of observations in a sample with a certain characteristic.

This is calculated as:

p̂ = x / n

where:

  • x: The count of individuals in the sample with a certain characteristic.
  • n: The total number of individuals in the sample.

To quantify the uncertainty around our estimate for the sample proportion, we can calculate the standard error of proportion, which is calculated as:

Standard Error of Proportion = √p̂(1-p̂) / n

where:

  • p̂ : The sample proportion.
  • n: The total number of individuals in the sample.

The following example shows how to calculate a standard error of proportion in Excel.

Example: Calculate Standard Error of Proportion in Excel

Suppose we would like to estimate the proportion of students in a certain school who support a new homework rule.

To do so, we randomly survey 20 students and mark 1 if they support the new rule or 0 if they don’t:

We can type the following formulas into cells E1 and E2 to calculate the sample proportion and the standard error of the proportion, respectively:

  • E1: =COUNTIF(B2:B21, 1)/COUNTA(B2:B21)
  • E2: =SQRT(E1*(1-E1)/COUNTA(B2:B21))

The following screenshot shows how to use these formulas in practice:

Excel standard error of proportion

From the output we can see the following:

  • Sample Proportion: 0.45
  • Standard Error of Proportion: 0.111243

This tells us that 0.45 (or 45%) of students support the new homework rule and the standard error of this estimate is 0.111243.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Calculate the Standard Error of the Mean in Excel
How to Calculate the Standard Error of Regression in Excel
How to Add Custom Error Bars in Excel

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *