A confidence interval is an estimate of a range of values that includes the true population value for a statistic, such as a mean. The level of confidence you choose will determine how reasonably certain the true population mean is included in the range. A confidence level of 95% means that you can be 95% certain, or wrong 5% of the time, that the sample being analyzed contains the population mean.
The most popular confidence levels are 99%, 95% and 90%. In statistics, these confidence levels correspond to what is called alpha. For example, a confidence level of 95% has an alpha of 5%, in other words, alpha is equal to 1 minus the confidence level.
There are three factors that contribute to the size of the confidence interval:
- The sample size – the larger the sample, the narrower the confidence interval
- The standard deviation of the sample data – higher variability in the sample data will increase the confidence interval
- The level of alpha for the calculation
The formula for calculating a confidence interval with a 95% confidence interval is:
Mean ± 1.96 * [standard deviation / square-root( sample size )]
The value 1.96, in the formula above, is a factor that reflects the 95% confidence level. Other confidence levels have different factors.
This process can be simplified by using the CONFIDENCE function in Excel:
Syntax: = CONFIDENCE ( alpha , standard deviation , sample size )
Let’s take a look at an example:
In the example below, ten participants were asked to complete a task and the time in seconds was recorded. Using the CONFIDENCE function in Excel, we can calculate the confidence interval of the sample data to estimate the range that contains the population mean.
In this example, alpha is set to 0.05 (a 95% confidence level), the standard deviation is calculated using the Excel STDEV function and the sample size is calculated using the Excel COUNT function. Embedding these other functions inside the CONFIDENCE function will make the model more flexible and robust; however, you can also type the numbers in directly or refer to a cell reference as well.
Adding a Confidence Interval to a Graph as Error Bars
Often times you may want to display your data in graphical form, including the confidence interval. The chart below shows the mean of the data set above and graphs the confidence interval 13.02 as an error bar.
Steps to add a confidence interval to a chart:
1) In Excel, click on the chart. This will activate the ‘Chart Tools’ menus at the top of Excel.
2) Select the ‘Layout’ tap and choose ‘Error Bars’ and then select ‘More Error Bar Options’
3) Choose ‘Custom’ radio button and click ‘Specify Value’. In this example the ‘Positive Error Value’ and the ‘Negative Error Value’ and equal and are the confidence interval calculated in the above example.