# How to generate a random number with normal distribution given confidence intervals?

I have broken down a project in to some list of tasks. For each task, I've worked with some experts to come up with 90% confidence intervals. e.g. I'm 90% sure task A will be more than L hours and 90% sure it will be less than U hours.

I'd like to build a monte carlo simulation to model the likely duration of the project.

In Excel, how do I generate a random number with normal distribution given these 90% confidence intervals? If this question is fundamentally misguided, is there a better way to achieve my intended outcome?

• For your particular problem, values of less than 0 would make no sense, but the normal distribution can take on any value in the real line. Nov 5, 2021 at 3:38
• Yes, I noticed this effect when I plotted the results. About 10%-15% of the results are less than 0. Fortunatly, I'm more interested in the higher sides of the cumulative probability distribution. "In 50% of the simulations, the project finished in X hours or less". Nov 5, 2021 at 13:05

In your case, the prior that you desire is fairly easy to specify. You're saying that, before actually starting the project, there is a 90% probability of it taking between L and U hours. What's more, you're also willing to accept that there's a normal distribution that can describe this range (i.e., assuming that there is a mean/median value that is most likely to occur and that increasingly divergent values are less probable). In this case, your prior is a normal distribution with mean of X and SD of a certain value based on your 90% prior. Here's an example of how this process works: say that my prior belief about the time taken to complete a project is described by a normal distribution with mean of 4 hours and standard deviation of 0.5 hours. The 90% confidence/credible interval can be obtained by examining what the critical values of the normal distribution corresponding to a two-tailed 0.10 (i.e., $$(1.00-0.90)/2 = 0.05$$). For a 90% interval, this is a value of 1.64, so the lower bound of the 90% interval would be $$4-(1.64*0.5) = 3.18$$ and the upper bound would be $$4+(1.64*0.5) = 4.82$$.
That example is obviously not exactly what you're looking for: you actually want the opposite where, given the interval boundaries, you describe the SD of the normal prior. This is simple algebra: $$lower~bound = mean-(1.64*X)$$ and $$upper~bound = mean+(1.64*X)$$. In this case, say that the point estimate for the time to complete the test is 5 hours, and your experts say that 90% of the time, the test will be finished between 2 and 8 hours. Your needed standard deviation would be $$5-(1.64*X) = 2$$ or about 1.83, so the normal distribution prior has mean of 5 and standard deviation of 1.83. If you wanted to do this from the 95% interval, then rather than 1.64 you would use 1.96, and so on.
If your experts didn't give you the "mean" of the normal distribution, then you can find the midpoint between your "confidence" intervals. So, if the experts say that the task will take between 3.5 and 8 hours, then the mean of the normal prior distribution would be $$(8 + 3.5)/2 = 5.75$$.