Let me say first off that I'm consistently impressed with the quality of responses on this site. You folks have done a better job of explaining difficult concepts than most instructors or textbooks I've encountered.

On to my question/scenario.

My basic goal is to prove that confidence intervals actually work, and by work I mean that that a certain percentage of them, say 95%, actually include the population mean (assuming that we know the population mean in the first place).

Here's my set up.

A) I’ve generated a data set in Excel consisting of 20 samples with 64 observations each (i.e. n = 64) that represent prices.

B) Prices are bound between a minimum of \$25 and a maximum of \$100.

C) Below is the best Excel formula I found that can generate something approaching a normal distribution. If anyone has any thoughts on this, let me know as I’m not wholly convinced as to how/why it works. I only know that RandBetween produces a uniform distribution so it’s useless for these purposes.


D) I chose \$62.50 as the population mean simply because it’s the average of 25 and 100.

E) I chose \$12 as the standard deviation of population.

F) I chose 95% (i.e. z-score 1.96) as the confidence level.

Here are my 20 confidence intervals. The ones with the asterisks fail to contain the population mean of \$62.50.

S1  66.19   60.31
S2* 62.30   56.42
S3  66.46   60.58
S4* 70.38   64.50
S5  63.24   57.36
S6* 61.23   55.35
S7  63.95   58.07
S8  65.89   60.01
S9  67.86   61.98
S10 64.17   58.29
S11 65.59   59.71
S12 67.31   61.43
S13 67.61   61.73
S14 66.55   60.67
S15 66.03   60.15
S16 63.77   57.89
S17 64.63   58.75
S18 65.35   59.47
S19 66.19   60.31
S20 64.32   58.44

As you might have already guessed, here’s the issue: theoretically if 95% of these intervals contained the population mean, then only 1 interval out of 20 would have failed to include it. Instead there were 3. And this wasn’t an isolated incident. If I regenerate the prices repeatedly using F9, it only meets the 19 or greater threshold around 70% of the time with some iterations producing 20 out 20 intervals that contain the mean to as low as only 16 out of 20.

My question is why?

Here’s what I’ve eliminated as possible issues thus far.

a) I boosted the number of samples to 100 and sample size to 1281 and got the same result so I don’t believe it’s an issue of sample size. 128,100 data points would seem to be sufficient.

b) I performed 10,000 iterations and recorded them using a macro and still got a “success rate” of about 70% same as before.

c) I also replicated everything above using 90% and 99% confidence levels. 90% only resulted in 18 or more intervals containing the population mean 63% of the time. A 99% confidence level only managed this about 87% of the time.

d) I’ve graphed the data in the form of a histogram to visually inspect it for normality and I’ve found no visual correlation between skewness, spikes, etc. and whether more or fewer intervals fail to contain the population mean. Also, I tried a Normal Quantile Plot Test for Normality that I found online on the 128.1k data point example and I did find a very slight amount of leptokurtosis for anyone who knows what to make of that.

Here are possible issues that may be impacting the result in ways I don’t understand.

a) The distribution is “truncated.” I found very little that was intelligible for a lay person like myself that explained how to calculate confidence intervals for this type of distribution, or even if this method is appropriate for truncated data. I couldn’t even find agreement on whether the truncation disqualifies it as normal in the strict sense.

b) Excel’s Norminv, Normdist, and Rand functions. Is Excel simply incapable of generating random normally distributed data with the level of accuracy needed to make this example work? Note that I also tried Excel’s data analysis->random number generation tool and got virtually identical results as the Excel formula I included above.

Lastly, the only way I was able to consistently get 90% of the confidence intervals to contain the population mean was to use a 95.7% confidence level (i.e. z-score 2.025), 95% needed a CL of 99%, and 99% needed a CL of 99.5%.

Any thoughts on what’s going on here would be greatly appreciated. If anyone can figure this out, it’s you guys.


In case someone happens to see this thread, here are screen shots of the 2 main excel spreadsheets; one version with just the values, the other version with just the formulas. Random number generator values

Random number generator formulas

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

  • 3
    $\begingroup$ Your problems begin with generating Normal variates incorrectly. Use 62.50 + 12*NORMSINV(RAND()). It also looks like you need to pay attention to the distinction between one- and two-sided confidence intervals. For the rest, TL;DR. $\endgroup$
    – whuber
    Commented Jun 10, 2013 at 21:52
  • $\begingroup$ Compared to your population standard deviation of 12, the width of your confidence intervals looks too narrow in many cases. We would expect the intervals to be approximately +/- 1.96*12 = 23 from the mean. So, roughly (39, 86). Your intervals are much narrower, which leads me to suspect you are calculating something wrong. (EDIT: forgot about your range restrictions, so that might make the intervals narrower than expected, but shouldn't be as narrow as what you found) $\endgroup$
    – Ellie
    Commented Jun 10, 2013 at 22:00
  • $\begingroup$ @whuber thanks for the quick response! I just sustituted in your formula and ran 100 iterations. Here were the results: at the 90% CL only 60 out of 100 had at least 18 intervals with the pop. mean. At 95% only 81/100. At 99% I got 98/100 which isn't bad. Thoughts? $\endgroup$
    – user26715
    Commented Jun 10, 2013 at 22:12
  • $\begingroup$ @Azula R. Thanks for the input. The standard deviation was a concern that I wasn't totally sure how to deal with. As far as choosing a value of 12, the logic came from trying out various std devs that would result in a data set that was fairly consistently bound between 25 and 100 give or take. In this case the max over whole data set was 97.65 and the min 25.44. $\endgroup$
    – user26715
    Commented Jun 10, 2013 at 22:21
  • 3
    $\begingroup$ @Azula The mistake is on your part: you forgot to divide by $\sqrt{n}$. Those intervals have approximately the correct widths. user26715: it's impossible to tell what you're doing wrong without seeing the code or at least the formulas you think you're using. Please note, though--in response to your title--that indeed these CIs are provably correct. All that we're doing here is establishing that your code is incorrect. That's not likely to be of interest to anyone else unless some specific statistical question emerges. $\endgroup$
    – whuber
    Commented Jun 10, 2013 at 22:22

2 Answers 2


It helps to distil your problem down to something simple and clear. When using Excel, this means:

  • Strip out unnecessary and duplicate material.

  • Use meaningful names for ranges and variables rather than cell references wherever possible.

  • Make examples small.

  • Draw pictures of the data.

To illustrate, let me share a spreadsheet I created long ago for exactly this purpose: to show, via simulation, how confidence intervals work. To start, here is the worksheet where the user sets parameter values and gives them meaningful names:

Parameters worksheet

The simulation takes place in 100 columns of another worksheet. Here is a small piece of it; the remaining columns look similar.

Simulation worksheet

How is it done? Let's look at the formulas:


From top to bottom, the first few rows:

  1. Count the number of simulated values in the column.

  2. Compute their standard deviation and then their standard error.

  3. Compute the t-value for the specified confidence alpha.

This stuff is of little interest, so it is shown in normal text. The interesting stuff is in red, but that should be self-explanatory from the formulas. (The strange formula for Out? will become apparent in the plot below.) The green values show how to generate normal variates with given mean Mu and standard deviation Sigma. This is done by inverting the cumulative distribution, as computed (for Normal distributions) by NORMSINV.

Finally, these 100 columns drive a graphic that shows all 100 confidence intervals relative to the specified mean Mu and also visually indicates (via the spikes at the bottom) which intervals fail to cover the mean. This is done with a little graphical trick: the value of Out? determines how high the spikes should be; a value of 3.5 extends them into the bottom of the plot, whereas a value of 0 keeps them outside the plot. (These values are plotted on an invisible left hand axis, not on the right hand axis.)


In this instance it is immediately apparent that two intervals failed to cover the mean. (The sixth and 33rd, it looks like.)

Because each interval has a $1-\alpha$ = $1-0.95$ = $5$% chance of not covering the mean in this example, the count of intervals out of $100$ follows a Binomial$(100, .05)$ distribution. This distribution gives relatively high probability to counts between $1$ (with $3.1$% chance of occurring) and $9$ (with $3.5$% chance of occurring); the chance that the count will be outside this range is only $3.4$%. By repeatedly pressing the "recalculation" key (F9 on Windows), you can monitor these counts. With a macro it's easy to accumulate these counts over many simulations, then draw a histogram and perhaps even conduct a Chi-square test to verify that they do indeed follow the expected Binomial distribution.

  • $\begingroup$ Thank you so much! I was completely missing the binomial distribution component. Below is a comparison of the original 100 iterations I recorded compared to the binomial distribution per Excel. I can now demonstrate that out of 20 confidence intervals, the highest probability belongs to scenarios where exactly 2 do not contain the population mean at the 90% confidence level. !enter image description here $\endgroup$
    – user26715
    Commented Jun 11, 2013 at 21:53

In any case, you have to simulate an infinite number of samples to get the result you want.

Or rely on probability theory.

The probably that the confidence interval covers the mean is 0.95. If you do $n$ CI's, the number that cover the true mean will follow a binomial distribution $(n,p)$, with $p=0.95$.

So nothing is certain.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.