Confidence interval is a useful metric used in statistics that will tell you how well your sample represents the population.
If you’re wondering how to calculate confidence the interval in a spreadsheet—you’ve come to the right place.
In this tutorial, I will show you how to calculate the confidence interval in Google Sheets.
Calculating Confidence Interval
The formula for calculating confidence interval is:
Confidence Interval = x +/- t*(s/√n)
- x: sample mean
- t: t-value that corresponds to the confidence level
- s: sample standard deviation
- n: sample size
I will be calculating a 95% confidence interval with this example data:
Here are the steps to do this:
1. First we need to calculate the average. To do this, you need to use the AVERAGE function and put the cell range that contains your data into the function. I my example this is =AVERAGE(A2:A12).
2. Next we need to find the standard deviation. To do this, use the STDEV function and place the cell range that contains your data into the function. In my example, this is =STDEV(A2:A12)
3. Next we need the sample size. Do this you can use the COUNT function and place the cell range that contains your data into the function. In my example, this is =COUNT(A2:A12).
4. Once we have our average, standard deviation, and sample size, we can begin to calculate our lower and upper 95% confidence interval. Remember that the formula for this is: x +/- t*(s/√n). Do do this in our spreadsheet we can use the T.INV function and the SQRT function. The exact formula for the lower 95% interval is =D1–T.INV(0.975,D3–1)*(D2/SQRT(D3))
5. For the upper 95% confidence interval the formula in my spreadsheet is: =D1+T.INV(0.975,D3–1)*(D2/SQRT(D3))
Closing Thoughts
In this example, I have used t-distribution to calculate the confidence interval. t-distribution works great with smaller sample sizes (usually n>30).
If you have a larger sample size, you will need to use normal distribution instead.
With normal distribution, you can use the NORM.S.INV function instead of the T.INV function.
The exact formula in my example using normal distribution would be:
95% lower: =D1–NORM.S.INV(0.975)*(D2/SQRT(D3))
95% upper: =D1+NORM.S.INV(0.975)*(D2/SQRT(D3))
More Google Sheets Tutorials:
How to Calculate p-Value
How to Calculate Z-Score
