Excel Techniques Used in this Article =AVERAGE(), =STDEV(), =COUNT(), =NORMSDIST(), =CONFIDENCE.NORM(), normal distribution, mean (average), standard deviation, sample size, significance level, upper and lower confidence intervals, z value, range name
Confidence intervals or limits can be prepared for almost any significance level you like. Typically a 5% confidence limit is prepared, as is a 90% and a 99% limit.
What a confidence limit does is it uses the behaviour of the Normal Distribution function and helps us to find, for example, the lower and upper value from a range of values within which the mean is probably going to be found. With a 5% significance level, we can say that we are confident that the mean value should lie somewhere in 95% of our data sample: that is, 47.5% either side of the mean. Look at the images below as we work through an example to explore these ideas.
First of all: there are just 31 data points and they could relate to anything! They occupy the range A3:B34, including column headings:
The Range F4:G5 shows us the standard deviation and count of the range: we will use these to find the confidence limits:
The z values, relate to the confidence limits we mentioned above: a z value of 1.645 will give us the appropriate values for the 5% significance level; 1.96 will give us the appropriate values for the 2.5% significance level and 3.295 will give us he values for the 0.5% significance level.
We apply the z values in the NORMSDIST(z) function in the cells G8:G10 for 5%, 2.5% and 0.5% respectively. Of course, we could have typed 0.9500 in cell G8 but we are trying to set up this worksheet as a template so we can use it for any significance level.
Significance Levels and the Confidence Limits
The significance level in the example shown here is 5% and in cell I12 you will find this formula =CONFIDENCE.NORM(alpha,standard deviation, size): where alpha is the significance level or 1 minus the value in G8 in this case, which is found by =NORMSDIST(F8), standard deviation is the value in cell G4 and is the result of =STDEV(data) where data is the name of the range B4:B31 and (sample) size is found in cell G5 and is the result of =COUNT(data). So, cell I12 contains =CONFIDENCE.NORM((1-G8),G$4,G$5). The value of 64.2433 is the value we need to use in the final calculation to find the upper and lower confidence limits.
To find the upper limit in this example, we add the result of the CONFIDENCE.NORM()function, 64.2433, to the mean value of 149.7419335 in cell G3 to give 213.9852 and we subtract that value from the mean to give us the lower limit of 149.7419335 – 64.2433 = 85.4987.
The graph we have prepared contains the raw data and the upper and lower confidence limits: UCL and LCL in green and red, respectively.
You might say that it doesn’t look as if 95% of all of the data are between these two limits but then you would be forgetting one thing: the method shows us the results of data based on a sample whilst the entire population would give us more accurate and reliable estimates of these values. The larger the sample, the better the results.
This page has demonstrated how to use the various techniques associated with confidence intervals or limits by using Excel. Whilst I have demonstrated only the 5% significance level, the spreadsheet file that accompanies this article contains templates for working with 2.5% and 0.5% significance levels too. Just go to the next section to find the link to this file.
Download The File
Click here confidence_intervals_with_excel to download this file. The file includes the worksheet we have discussed here as well as the work sheets for the 2.5% and 0.5% significance levels.
1st December 2013 … the first in advent so wherever you are, merry Christmas 2013!