Forgot your password?

How to calculate 95 confidence intervals using Excel

By Edited May 19, 2016 0 0

Calculating the 95 percent confidence interval is very easy once you understand how to do it. 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. Using Excel you can quickly and easily calculate the confidence statistics you need. Here is an simple example of calculating the 95% confidence interval using Excel.

Things You Will Need

Computer with Excel installed

Step 1

The first step is gather the data you want to base the confidence calculation into an excel spread sheet. 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. In this case, there are 20 data points and the value of the count calculation is 20.

Step 2

step 2
Calculate the standard deviation of the data. The standard deviation is used to measure the variability of a data set and it is a key value of the confidence calculation. In Excel, this is done with the "STDEV" function. Select all of the data points and use the "STDEV" function to calculate the standard deviation for the whole population.

Step 3

95 confidence interval
Calculate the confidence interval. 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 we are calculating a confidence of 95%. In Excel, this is done by using the "CONFIDENCE" function. It requires the input of significant level which is 100% subtracted by the level of confidence we are interested in. In this case, the value is 5%. It also requires the standard deviation and count we had calculated in the previous two steps.

Step 4

confidence statistics
Calculate the mean or average value from the data. In Excel, this is done with the "AVERAGE" function. For this example, the average from all 20 data points is 76.2.

Step 5

calculate confidence intervals
Calculate the range for your confidence statistics. 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 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. Array

Tips & Warnings

In order to use some of the statistical functions in Excel, you may have to install the Analysis Tool Pack. The Analysis tool pack can be installed from the tools menu add ins.



Add a new comment - No HTML
You must be logged in and verified to post a comment. Please log in or sign up to comment.

Explore InfoBarrel

Auto Business & Money Entertainment Environment Health History Home & Garden InfoBarrel University Lifestyle Sports Technology Travel & Places
© Copyright 2008 - 2016 by Hinzie Media Inc. Terms of Service Privacy Policy XML Sitemap

Follow IB Technology