Table of Contents
To calculate the standard error of proportion in Excel, you need to enter the mean, standard deviation, and sample size into the appropriate cells of an Excel worksheet. In the same worksheet, use the formula =STDEVP(range) to calculate the standard error of proportion, where range is the range of cells containing the mean, standard deviation, and sample size. This formula will output the standard error of proportion as a decimal value.
In statistics, a sample proportion (p̂) represents the proportion of 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:
- 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.