I'd like to be able code code it in an Excel function that will return random variables with those characteristics.

- Thread starter Jennifer Murphy
- Start date

I'd like to be able code code it in an Excel function that will return random variables with those characteristics.

https://en.wikipedia.org/wiki/Truncated_normal_distribution

https://en.wikipedia.org/wiki/Truncated_normal_distribution

That looks like exactly what I need. Now I have something to keep me busy for a day or two. Thank you very much.

Seems like a truncated normal could do the trick?

Or maybe the Beta-distribution

Just rescale it so it goes from 0 to 100.

There are some key differences between the truncated normal and the beta distributions. Truncated normal, as the name implies, simply chops the tails off the normal distribution. The tail area does not smoothly transition to near zero. The beta distribution does smoothly transition in the tail areas. Excel has functions for modeling the beta distribution, but not the truncated normal. You would have to model the normal distribution then exclude all values beyond 0 and 100.

If you are using this for Monte Carlo simulations, another frequently used option is the triangular distribution. It is used when you don't know the underlying distribution and will settle for an approximation.

Playing around with simulations is a great idea, which can also include incorporating sample sizes to see the impacts on variability between realizations. Below is an example I did last week visualizing Bayesian priors. Of note, ideally I wouldn't use a normal in intercept2, since I don't want it to go below zero. For me a gamma would be better, but the procedure won't allow it, given the sampler and conjugative requirements.

I was able to get some good plots using Excel's Beta.Dist function. Here's one:

But I have a couple of questions:

1. The Y value in the CDF (right) chart is the cumulative probability. But what is the Y value in the PDF (left) chart? It cannot be a probability, because it goes above 1. If I normalize the Y values by dividing them by their sum, that looks like a probability. Is it?

2. What do I need to do to get an Excel function that will return a random variable on [0,1] with the right probability to generate the chrta on the left if I call it 1,000 times?

Thanks

What are you using this distribution to model?

There are some key differences between the truncated normal and the beta distributions. Truncated normal, as the name implies, simply chops the tails off the normal distribution. The tail area does not smoothly transition to near zero. The beta distribution does smoothly transition in the tail areas. Excel has functions for modeling the beta distribution, but not the truncated normal. You would have to model the normal distribution then exclude all values beyond 0 and 100.

There are some key differences between the truncated normal and the beta distributions. Truncated normal, as the name implies, simply chops the tails off the normal distribution. The tail area does not smoothly transition to near zero. The beta distribution does smoothly transition in the tail areas. Excel has functions for modeling the beta distribution, but not the truncated normal. You would have to model the normal distribution then exclude all values beyond 0 and 100.

If you are using this for Monte Carlo simulations, another frequently used option is the triangular distribution. It is used when you don't know the underlying distribution and will settle for an approximation.

What my mediocre math and statistics skills are struggling with is how to get the inverse function. The Beta.Dist function will generate the points on the PDF and CDF curves and I can easily do one for the triangular distribution. But I can't see how to turn those into a function that will return a random number in any of those distributions.

Thanks

You can check if the data fits by doing a QQplot. If the data then will be on a straight line then it will fit.

You can check if the data fits by doing a QQplot. If the data then will be on a straight line then it will fit.

Try the BETA.INV (probability, alpha, beta, A, B) function. You can then use RAND() in place of the probability term.

I actually looked at Beta.Inv, but did not understand it. I was about to try it, but got distracted with other options.

I now have Excel VBA code that will do exactly what I need. I'll post it here as soon as I can run a few more tests to ensure that it is really working.

And since Excel also has a Norm.Inv function, I bet I can go back to the Normal distribution which has the advantage of an explicit mean.

Thank you for saving me hours of frustration.

You can check if the data fits by doing a QQplot. If the data then will be on a straight line then it will fit.