Generate a normal distribution from mean, upper, and lower confidence intervals

[divangou]

I have a set of variables I would like to generate distributions for and draw random numbers from for a Monte Carlo Simulation. For each variable, I have the mean, Upper 95% CI, and lower 95% CI. The upper and lower CI's are almost never equal.

Any help on the most efficient way to do this in excel would be greatly appreciated.

Divangou

 

[shg]

I'm not sure this is correct, and am no statistician, but here goes.

The 95% confidence interval extends from 2.5% to 97.5% of the cumulative distribution, and =NORM.S.INV(97.5%) returns 1.96, which means the top of the confidence interval is 1.96 standard deviations above the mean. So the SD for your normal distribution is (CIhigh - mean)/1.96. And since the distrution is assumed normal (correct?), the lower end of the CI is redundant.

So to generate deviates from that distribution,

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]CI[/td][td]

95%​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Top end[/td][td]

97.50%​

[/td][td]B2: =B1+(1-B1)/2[/td][/tr]

[tr][td]

3​

[/td][td][/td][td]

1.96​

[/td][td]B3: =NORM.S.INV(B2)[/td][/tr]

[tr][td]

4​

[/td][td]mean[/td][td]

10​

[/td][td]B2: Input[/td][/tr]

[tr][td]

5​

[/td][td]CIhigh[/td][td]

15​

[/td][td]B5: Input[/td][/tr]

[tr][td]

6​

[/td][td]SD[/td][td]

2.551​

[/td][td]B6: =(CIhigh - mean) / B3[/td][/tr]

[tr][td]

7​

[/td][td]Deviates[/td][td]

11.27​

[/td][td]B7: =NORM.INV(RAND(), mean, SD)[/td][/tr]

[tr][td]

8​

[/td][td][/td][td]

9.94​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td]

7.20​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td]

11.38​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td][/td][td]

3.44​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td]

10.59​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td]

14.53​

[/td][td][/td][/tr]
[/table]

 

[shg]

EDIT: If the average of the top and bottom of the confidence interval <> the mean, then the distribution those three numbers describe is not normal.

 

[divangou]

Thanks shg, this is helpful. The problem is that the two Confidence Intervals are not equal, suggesting that the distribution is skewed. I'm no statistician either, so I may be getting my terminology wrong, but an internet search suggests you can have a skewed normal distribution (sounds like an oxymoron I know). The question is how to back-engineer the skewness based on the inputs I have mean and 2 CIs...I think. Here's a sub-sample of the data.

[TABLE="width: 245"]
<tbody>[TR]
[TD]Mean[/TD]
[TD]Low 95% CI[/TD]
[TD]High 95% CI[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]115.65[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]252.02[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]208.31[/TD]
[TD="align: right"]87.09[/TD]
[TD="align: right"]348.64[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]384.91[/TD]
[TD="align: right"]262.09[/TD]
[TD="align: right"]603.61[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]224.61[/TD]
[TD="align: right"]78.11[/TD]
[TD="align: right"]392.8[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]72.09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]202.77[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]215.68[/TD]
[TD="align: right"]77.25[/TD]
[TD="align: right"]363.99[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]145.65[/TD]
[TD="align: right"]14.62[/TD]
[TD="align: right"]277.76[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]192.74[/TD]
[TD="align: right"]85.3[/TD]
[TD="align: right"]347.23[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]188.14[/TD]
[TD="align: right"]59.29[/TD]
[TD="align: right"]315.74[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]107.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]228.55[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]110.78[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]242.27[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]183.73[/TD]
[TD="align: right"]70.87[/TD]
[TD="align: right"]336.32[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]127.85[/TD]
[TD="align: right"]1.55[/TD]
[TD="align: right"]286.09[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]127.54[/TD]
[TD="align: right"]24.44[/TD]
[TD="align: right"]265.96[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]56.3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]169.58[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]70.82[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]198.1[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

They are indeed asymmetric about the mean.

I have a routine that will generate skew-normal deviates, but I don't know ATM how to get the other parameters of the deviation from the bounds of the confidence interval.

 

[shg]

EDIT: ... but I don't know ATM how to get the other parameters of the distribution from the ...

 

[shg]

Where did the values you have come from?

 

[StephenCrump]

divangou

Perhaps echoing shg's question ...

You open by saying that you want to generate values for a set of variables using Monte Carlo simulation, which implies that you have parameters for a particular set of distributions.

But later you supply what looks like 16 sample results with N=100 (for one variable?) suggesting that the aim is actually to fit the population distribution? Presumably, if this is the case, you have additional information from the samples to help fit a curve, i.e. sample variances?

I also note that many of these have a confidence interval of 0, sugesting that the distribution is bounded at zero (i.e. my guess is that negative results aren't possible?)

What's the nature of the observations, and what makes you start by suggesting a normal distribution? There's a whole range of possible curves with different parameters, e.g. for skewness and kurtosis. A bounded distribution such as a Weibull may be appropriate .. I'm guessing this is an engineering application?

Where did the values you have come from?

 

[shg]

I also note that many of these have a confidence interval of 0, sugesting that the distribution is bounded at zero (i.e. my guess is that negative results aren't possible?)

Hmm -- exactly zero. Is this a discrete distribution (are the deviates all integers?).

 

[StephenCrump]

Sorry, I can't help making a slight distribution from the subject (no offence intended, shg)

parameters of the deviation

are the deviates all integers?