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.