Keywords: confidence intervals, confidence levels, macro, function, excel, how to
Version: Excel 97 or later
In excel there are
no native function for calculating confidence interval (CI) a function called CONFIDENCE to calculate the confidence interval, but it works slightly different from the custom confidence function I present here.
Since I need to do this calculation on a regular basis i decided to simplify this work by writing a VB function that will automatically calculate the confidence interval in Excel for a given range of values and confidence level (CL) between 1-99%. It will also present the output in a format that is suitable to copy and paste directly into reports and result sheets. There are many excellent sites that cover the theory behind CI, so I won’t repeat it here.
What you need to know is that the function assumes that the standard deviation, σ, is unknown (which it is in most cases) and has to be estimated from the sample. In this case the CI isn’t calculated from the normal distribution but rather from the t-distribution, which is used in my function.
The function syntax is:
CONFID(range, confidence level)
where range contains your data and confidence level is the desired confidence level (!) expressed as a fraction (0.01-0.99). The resulting output will look like this:
Ex: 251.94 ± 4.03 (average ± CI)
The somewhat strange-looking output formatting was covered in a previous article. The following example screens will illustrate the usage:
You may freely use and modify the script and example as you see fit. However, if you find this tool useful, you can show your appreciation by submitting this article to your favourite social bookmarking site, putting a link to my site on your site or simply leave a comment.