Home Â» How to Calculate Margin of Error in Excel

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