Keywords: confidence intervals, confidence levels, macro, function, excel, how to
Version: Excel 97 or later
Download:
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.
Download:
Popularity: 34% [?]
Just wanted to comment and say that I really like your blog layout and the way you write too. It’s very refreshing to see a blogger like you.. keep it up
Hi,
You have errors in this,
your CLs are not the percentages they aspire to be.
See below for first two examples
r1 250.56 236.68
r2 258.81 234.76
r3 249.73 231.23
r4 253.50 238.37
r5 247.10 222.11
n 5.00 5.00
stdev 4.47 6.45
mean 251.94 232.63
0.1 3.29 4.75
0.05 3.92 5.66
0.01 5.15 7.43
C.L 90% 251.94 ± 3.29 232.63 ± 4.75
C.L 95% 3.29 ± 3.92 4.75 ± 5.66
C.L 99% 3.92 ± 5.15 5.66 ± 7.43
Dear
Congrats for your hardwork.But friends I want one help how can I create a formula to find the exact time intervals , the time taken by employee.
Example: The Lunch Break is for one hour assume 1:00 PM to 2:00 PM If employee comes by 2:15 PM How can I make it.
I’m not a statistician by any means, so correct me if I’m wrong, but there seems to be an error in your macro:
confint = stderr * Application.TInv((1 – conflvl), n)
should imo be
confint = stderr * Application.TInv((1 – conflvl), (n-1))
The reason for this is that TINV takes degrees of freedom as input and NOT sample size. For a sample of size n, the t distribution will have n-1 degrees of freedom.
Thank you so much for this – a friend needed help, and this little macro let me wrap up everything in 2 hours! I was already thinking I was in for a big haul…
That was real quick update…BTW may i know your name and what you are doing? I am Balaji P.G and a research scholar working towards my PhD in National University of Singapore
You are absolutely correct. I will update my post to reflect this.
Thanks!
hi,
I disagree with your comment that there is no native comment. There is a comment called as Confidence(alpha, Standard deviation, size) which calculates confidence interval.