How to calculate 95 confidence intervals using Excel

Here is an simple example of calculating the 95% confidence interval using Excel.

Things You Will NeedComputer with Excel installed

Step 1

The first step is gather the data you want to base the confidence calculation into an excel spread sheet. The minimum value for this range is the mean subtracted by the confidence interval and the maximum value is calculated by the mean added by the confidence interval. In Excel, this is done by using the “CONFIDENCE” function. For this example, the average from all 20 data points is 76.2.

Step 5

Calculate the range for your confidence statistics. It requires the input of significant level which is 100% subtracted by the level of confidence we are interested in. In Excel, this is done with the “STDEV” function. Using Excel you can quickly and easily calculate the confidence statistics you need.

Calculating the 95 percent confidence interval is very easy once you understand how to do it. Select all of the data points and use the “STDEV” function to calculate the standard deviation for the whole population.

Step 3

Calculate the confidence interval. In this case we are calculating a confidence of 95%. In Excel, this is done with the “AVERAGE” function. Confidence statistics is an estimation method used to predict if a subsequent sampling of data will fall within a given interval given a level of confidence. This is the interval from the mean that you would predict results of another sample based on a certain level of confidence. In this case, there are 20 data points and the value of the count calculation is 20.

Step 2

Calculate the standard deviation of the data. In this example, the data suggests that if the data is collected in the same way and the same factors are in play when we collect the follow data, we are 95% confident that the mean of the next data set should be between 70.6 to 81.7.

ArrayTips & WarningsIn order to use some of the statistical functions in Excel, you may have to install the Analysis Tool Pack. Put them all in a column and use the “COUNT” function to determine the number of data points you want to base you calculation on. It also requires the standard deviation and count we had calculated in the previous two steps.

Step 4

Calculate the mean or average value from the data. In this case, the value is 5%. The Analysis tool pack can be installed from the tools menu add ins.

. The standard deviation is used to measure the variability of a data set and it is a key value of the confidence calculation

Leave a Reply