Where the formula in F7 is now: =NORM.Note: The second argument of the PERCENTILE function must be a decimal number between 0 and 1. So RANDBETWEEN(1,100) gives you your d100 a discrete uniform), what you want to do is make your d100 into a number between 0 and 1, by subtracting say 0.5 and dividing by 100 (more generally, you can subtract $\alpha$ and divide by $101-2\alpha$, for $\alpha$ between 0 and 1 - different choices will put you more or less far into the tail, but $\alpha=0.5$ should do okay. If you actually want the results to reflect using a d100 (i.e. is a random normal value with the required mean and standard deviation So for example if I put =NORM.INV(RAND(),B3,C3) into D3, and the values '58' and '5' into B3 and C3, I get: B C D If you just want to use a random uniform, in Excel you need: =norm.inv(rand(),mean,sd) So if you have such a function, you then supply it with a uniformly distributed random percentile (or rather, a quantile) to be turned into a normal quantile. (If they only supply one for the standard normal, it is easy to convert to any other normal by multiplying by the sd and adding the mean). Most packages used for statistical work will implement one where you can supply the mean and standard deviation.
(Your use of the terms minimum and maximum threw me off what you were trying to do)Īs a general solution that applies to any package, you need a function that converts a percentile to a normal that's known as the quantile function or the inverse cdf for the normal. One way to give something like a 'minimum' and 'maximum' if you still want to use a normal distribution, is to specify some extreme quantile as a notional minimum and maximum, which would correspond to some number of standard deviations from the mean.Īh, yes, if you give the mean and standard deviation (you don't have to specify mean-sd and mean+sd, though you could do that instead), it's easy. Normally you would specify a mean and standard deviation, but there's no limit to how many standard deviations an observation could be, they just become increasingly rare. The normal curve - what I assume you mean by a bell curve - doesn't actually have a minimum or maximum. In the case of the uniform, 1 standard deviation is $\sqrt$ (just under 30%) of the distance between minimum and maximum. Why would the minimum and maximum values be one standard deviation from the mean? That's not true of either the uniform ('flat') case, nor of the normal case. Now, if I assume those min/max values are 1 standard deviation, I've entered the min/max heights and weights for each race, and can easily calculate the mean. So I'd like to add some Bell Curve calculations, because why not. Given the criteria above, could someone please explain what Excel formula to use? Preferably using a built-in one. So, given a Mean, and ranges for 1SD, I'd like to enter a percentile (1d100) and arrive at a height.įor example, a 16% would be -1SD, 50% would be exactly the median, and 85% would be +1SD (following the 68–95–99.7 rule).
I spent a few hours poking around, but didn't find any answers, or at least I don't think I did. since I took a stats course, and I remember almost nothing.īell Curve and SD is about it.
(the average height listed for Human was pretty close to real life 1SD range for a human male) Now, if I assume those average min/max values are 1 standard deviation, I've got the ingredients of a formula. I've entered the average min/max heights and weights for each race, and can easily calculate the mean.
It's pretty slick, but the random height/weight calculator is.
#Calculate percentile from mean and standard deviation excel generator
I'm running a DnD campaign and made a Random NPC generator in Excel.
0 kommentar(er)
