Is there an asymetric Normal distribution with end points?

[Jennifer Murphy]

Is there a distribution like a Normal distribution, but finite, that has a settable min, max, and mean? For example, a distribution with a mean of 65 and no values below 0 or above 100? If it also has a settable std dev, great.

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

 

[spunky]

Seems like a truncated normal could do the trick?

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

 

[Jennifer Murphy]

Seems like a truncated normal could do the trick?

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

Wow. I thought I checked that out. I guess I kept searching for "skewed" 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.

 

[GretaGarbo]

Seems like a truncated normal could do the trick?

Yes.

Or maybe the Beta-distribution

Just rescale it so it goes from 0 to 100.

 

[hlsmith]

Yeah, I was also thinking about the beta distribution.

 

[Miner]

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.

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.

 

[hlsmith]

Excellent points @Miner

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.

 

[Jennifer Murphy]

Yes.

Or maybe the Beta-distribution

Just rescale it so it goes from 0 to 100.

Beta looks interesting. And unlike the truncated Gaussian, it goes to zero at the end points.

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

 

[Miner]

If I understand Q2 correctly, you should be able to adapt thisto the beta distribution.

Note: Set calculations in Excel to Manual. If it is set to Automatic, your entire spreadsheet will automatically generate new random numbers every time you select Enter.

 

[Jennifer Murphy]

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.

These are the primary reasons I think the Beta will be better for me than the truncated Gaussian.

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.

I am trying to calculate the proficiency of different people on different tasks and evaluate how their (average) skill improves with practice. I'd like an Excel function (like an inverse function) that will return a random number with a roughly normal shape and a known min, mean, and max, like 0, 65, 100, that I can use to compare various types of averages (simple, moving, weighted). Any of these (Normal, Beta, triangular) would probably do.

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

 

[Miner]

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

 

[GretaGarbo]

It is much more important that the distribution fits to the data than that the minimum or maximum are on the correct values. For example the length of females (and of males) can be well approximated by a normal distribution although the length does not go from minus infinity to plus infinity.

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.

 

[Dason]

It is much more important that the distribution fits to the data than that the minimum or maximum are on the correct values. For example the length of females (and of males) can be well approximated by a normal distribution although the length does not go from minus infinity to plus infinity.

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.

Very true. Also keep in mind that the beta distribution technically doesn't have support on 0 or 1. If those are possibly values then it might fail if you're fitting to data.

 

[Jennifer Murphy]

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

Wow! This is exactly the solution I was looking for. Thank you!!!

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.

 

[Jennifer Murphy]

It is much more important that the distribution fits to the data than that the minimum or maximum are on the correct values. For example the length of females (and of males) can be well approximated by a normal distribution although the length does not go from minus infinity to plus infinity.

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.

Yes, I understand. Most of my data is proficiency measures (percent correct or percent success), so it ranges from 0-1.