How to Calculate Margin of Error in Google Sheets


We can use the following formula to calculate a confidence interval for a population mean:

Confidence Interval: x ± tn-1(s/√n)

where:

  • x: Sample mean
  • tn-1:The t critical value 
  • s: Sample mean
  • n: Sample size

The portion of the formula that we add and subtract from the sample mean is known as the margin of error.

Margin of Error = tn-1(s/√n)

To calculate the margin of error in Google Sheets based on a given sample and a desired confidence level, we can use the MARGINOFERROR function.

This function uses the following syntax:

MARGINOFERROR(range, confidence)

where:

  • range: The range of values
  • confidence: The desired confidence level (e.g. 0.9, 0.95, 0.99)

The following example shows how to use this function to calculate a margin of error in Google Sheets in practice.

Example: How to Calculate Margin of Error in Google Sheets

Suppose a botanist would like to calculate a 95% confidence interval for the mean height of a particular species of plant.

She decides to collect a simple random sample of 15 plants and records each of their heights in inches:

We can type the following formula into cell D1 to calculate the margin of error for a 95% confidence interval for the population mean:

=MARGINOFERROR(A2:A16, 0.95)

The following screenshot shows how to use this formula in practice:

Google Sheets margin of error

The margin of error turns out to be 1.478.

If we’d like to verify this is correct, we can type the following formula into cell D1 to calculate the margin of error using built-in functions in Google Sheets:

=T.INV.2T(0.05, COUNT(A2:A16)-1)*STDEV.S(A2:A16)/SQRT(COUNT(A2:A16))

The following screenshot shows how to use this formula in practice:

Notice that this formula returns the same result.

If we’d like to calculate a 95% confidence interval for the population mean height of this species of plant, we could proceed to add and subtract this margin of error from the sample mean to obtain the upper and lower bounds of the confidence interval.

Note: You can find the complete documentation for the MARGINOFERROR function in Google Sheets here.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Calculate Standard Error of the Mean in Google Sheets
How to Calculate Confidence Intervals in Google Sheets
How to Create a Bell Curve in Google Sheets

Leave a Reply

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