How to Calculate Margin of Error in Excel


Often in statistics, we’re interested in estimating a population parameter using a sample.

For example, we might want to know the mean height of students at a particular school. If the school has 1,000 total students, it might take too long to measure every student so instead we could take a simple random sample of 50 students and calculate the mean height of students in this sample. 

And while the mean height of students in the sample might be a good estimate of the true population mean, there is no guarantee that the sample mean is exactly equal to the population mean. In other words, there exists some uncertainty. 

One way to account for uncertainty is create a confidence interval, which is a range of values that we believe contains the true population parameter.

For example, if the mean height of students in the sample is 67 inches, our confidence interval for the true mean height of all students in the population might be [65 inches, 69 inches], which means we’re confident that the true mean height of students in the population is between 65 and 69 inches.

A confidence interval is composed of two parts:

Point estimate – often this is a sample mean or sample proportion.

Margin of error – a number that represents the uncertainty of the point estimate.

The formula to create a confidence interval is:

Confidence Interval = point estimate +/-  margin of error

Margin of Error Formula

If you’re creating a confidence interval for a population mean, then the formula for the margin of error is:

Margin of error: Z * σ / √n

Where:

Z: Z-score

σ: Population standard deviation

n: Sample size

Note: If the population standard deviation is unknown, then you can replace Z with tn-1, which is the t critical-value that comes from the t distribution table with n-1 degrees of freedom.

And if you’re creating a confidence interval for a population proportion, then the formula for the margin of error is:

Margin of error: Z * √(p*(1-p)) / n)

Where:

Z: Z-score

p: Sample proportion

n: Sample size

Note that the Z-score you’ll use for this calculation is dependent on you chosen confidence level. The following table shows the Z-scores associated with common confidence levels:

Confidence Level Z-score
80% 1.282
85% 1.44
90% 1.645
95% 1.96
99% 2.576

Next, we’ll walk through two examples of how to calculate the margin of error in Excel.

Example 1: Margin of Error for a Population Mean

Suppose we want to find the mean height of a certain plant. It is known that the population standard deviation, σ, is 2 inches. We collect a random sample of 100 plants and find that the sample mean is 14 inches. Find a 95% confidence interval for the true mean height of this certain plant.

Since we’re finding a confidence interval for the mean height, the formula we will use for the margin of error is: Z * σ / √n

The following image shows how to calculate the margin of error for this confidence interval:

 

The margin of error turns out to be 0.392.

Thus, the confidence interval for the true mean height of plants would be 14 +/ 0.392 = [13.608, 14.392].

Example 2: Margin of Error for a Population Proportion

Suppose we want to know what percentage of individuals in a certain city support a candidate named Bob. In a simple random sample of 200 individuals, 120 said they supported Bob (i.e. 60% support him). Find a 99% confidence interval for the true percentage of people in the entire city who support Bob. 

Since we’re finding a confidence interval for the mean height, the formula we will use for the margin of error is: Z * √(p*(1-p)) / n)

The following image shows how to calculate the margin of error for this confidence interval:

 

The margin of error turns out to be 0.089.

Thus, the confidence interval for the true percentage of individuals in this city that support Bob is 0.6 +/- 0.089 = [ 0.511, 0.689].

Notes on Finding the Appropriate Z-Score or t-Score

If you’re finding the confidence interval for a population mean and you’re unsure of whether or not to use a Z-Score or a t-Score for the margin of error calculation, refer to this helpful diagram to help you decide:

Also, if you don’t have a helpful table that shows you which Z-Score or t-Score to use based on your confidence interval, you can always use the following commands in Excel to find the correct Z-Score or t-Score to use:

To find Z-Score: =NORM.INV(probability, 0, 1)

For example, to find the Z-Score associated with a 95% confidence level, you’d type =NORM.INV(.975, 0, 1), which turns out to be 1.96.

To find t-Score: =T.INV(probability, degrees of freedom)

For example, to find the t-Score associated with a 90% confidence level and 12 degrees of freedom, you’d type =T.INV(.95, 12), which turns out to be 1.78.

Leave a Reply

Your email address will not be published.