How to Calculate a Confidence Interval in Excel
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
The ‘CONFIDENCE’ function is one of Excel’s oldest statistical functions.
The ‘CONFIDENCE’ function calculates the confidence value for the confidence interval of a data set. A confidence interval is a defined range of values that might contain the true mean of a data set.
In this tutorial, you’ll get to know more about the ‘CONFIDENCE’ function, look under its hood, and figure out how to make it work.
Kasper Langmann, Co-founder of SpreadsheetoLet’s start!
What is the ‘CONFIDENCE’ function?
The ‘CONFIDENCE’ function is an Excel statistical function that returns the confidence value using the normal distribution.
In turn, the confidence value is used to calculate the confidence interval (or CI) of the true mean (or average) of a population.
It’s a way to represent the uncertainty of your data in a scientific way. It gives the reader or user a so-called ‘margin of error’.
Kasper Langmann, Co-founder of SpreadsheetoMost of the time, people use 95% as the confidence level. Meaning, out of 100 repeated experiments, the true mean is found in 95 of them.
However, a 95% confidence level is not a standard. You can choose your own confidence level, although, people commonly use 90% – 99% to well… instill confidence. 😊
After you calculate the confidence value, the confidence interval is presented with the average alongside the confidence value with a plus-minus sign (±) in between.
The ‘CONFIDENCE’ function syntax
Here’s the syntax for this function:
=CONFIDENCE(alpha,standard_dev,size)
It has three (3) required arguments:
- Alpha (the significance level which is calculated as 1 – confidence level; a 95% confidence level has a 0.05 significance level)
- Standard_dev (the standard deviation of the data set)
- Size (the population size)
Although the average is not one of the arguments, you have to calculate the average to get the confidence interval. The result from the ‘CONFIDENCE’ function is added to and subtracted from the average.
Kasper Langmann, Co-founder of SpreadsheetoIf the average is 100 and the confidence value is 10, that means the confidence interval is 100 ± 10 or 90 – 110.
If you don’t have the average or mean of your data set, you can use the Excel ‘AVERAGE’ function to find it.
Also, you have to calculate the standard deviation which shows how the individual data points are spread out from the mean.
In Excel, there are two functions you can use to calculate the standard deviation: STDEV.P and STDEVPA. Use the first one only — STDEV.P — since it ignores non-numeric data.
New Functions
Excel has released 2 new similar functions, the ‘CONFIDENCE.NORM’ and ‘CONFIDENCE.T’ functions with similar arguments.
The ‘CONFIDENCE’ function is still usable for backward compatibility. However, it’s recommended to learn the 2 new functions since Excel claimed these functions have improved accuracy.
How to use the function
Using the ‘CONFIDENCE’ function is easy and straightforward. All you have to do is supply the parameters with their appropriate cell references and/or values.
Let’s use the data set shown below:
Here, we’ll be solving for the confidence interval of the time it takes for a certain fast-food company to deliver your order.
Assuming you have the same order for all 10 instances, the delivery takes 55.4 minutes on average with a standard deviation of 8.499.
In addition, the fast-food company committed a 95% confidence value.
Using the ‘CONFIDENCE’ syntax, we’ll arrive at the following equation:
=CONFIDENCE(0.05,8.499,10) or =CONFIDENCE(E4,E6,E7)
- Alpha: 0.05 (the significance level which is calculated as 1 – confidence level; a 95% confidence level has a 0.05 significance level)
- Standard_dev: 8.499 (the standard deviation of the data set)
- Size: 10 (the population size)
From that, we get 5.27 (round up to 2 decimals) as the confidence value.
The confidence interval, which is added to and subtracted from the mean, is 50.13 – 60.67 or 55.4 ± 5.27.
Simple, right? 😊
Common Errors
As with most functions in Excel, there are common errors users may encounter when using the ‘CONFIDENCE’ function:
#NUM!
There are 3 possible reasons for this error to occur:
- ‘alpha’ is ≤ 0 or ≥ 1
- ‘standard_dev’ is ≤ 0
- ‘size’ is < 1
#VALUE!
This error happens if the supplied or referenced parameter is a non-numeric value. This is the reason why you should use STDEV.P when finding the standard deviation.
Wrapping things up…
The ‘CONFIDENCE’ function is a great Excel function to construct the confidence interval for a population mean.
The best thing about this function is that learning how to use it is quick and easy. Plus, the syntax is straightforward. 😊
Kasper Langmann, Co-founder of Spreadsheeto